--=================省内件分区营业表===============declare @code char(4) --定义区号 select @code='2001'declare @begdate datetime --定义起止日期 declare @enddate datetime select @begdate='2000-1-1' select @enddate='2004-1-1'declare @date datetime select @date=@begdateselect convert(char(2),month(@date))+'.'+convert(char(2),day(@date)) '日期', sum(case when substring(workerno,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2) then weight else 0 end) '收件重量', sum(case when (substring(workerno,1,4)=@code) and paywise=1 then fmoney else 0 end) '寄付现金', sum(case when (substring(workerno,1,4)=@code) and paywise=2 then fmoney else 0 end) '寄付月结', sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4) then fmoney else 0 end) '到付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计', sum(case when substring(sworker,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4) then weight else 0 end) '收件重量', sum(case when (substring(sworker,1,4)=@code) and paywise=3 then fmoney else 0 end) '寄付现金', sum(case when (substring(sworker,1,4)=@code) and paywise=4 then fmoney else 0 end) '收付月结', sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2) then fmoney else 0 end) '寄付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计', sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额', sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额', sum(fmoney) '营业额'from fastorder where (EXISTS (select @date=@date+1)) and @date<=@enddate and data=@date
你的意思可能是: select date 日期 from tablename where date between '2002-1-1' and '2002-4-1'
如果用 between 可能会中间有间断的日期,我现是需要从一号到月份的最后一个日期,是间不能断谢谢!
--=================省内件分区营业表===============declare @code char(4) --定义区号 select @code='2001'declare @begdate datetime --定义起止日期 declare @enddate datetime select @begdate='2000-1-1' select @enddate='2004-1-1' select convert(char(2),month(data))+'.'+convert(char(2),day(data)) '日期', sum(case when substring(workerno,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2) then weight else 0 end) '收件重量', sum(case when (substring(workerno,1,4)=@code) and paywise=1 then fmoney else 0 end) '寄付现金', sum(case when (substring(workerno,1,4)=@code) and paywise=2 then fmoney else 0 end) '寄付月结', sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4) then fmoney else 0 end) '到付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计', sum(case when substring(sworker,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4) then weight else 0 end) '收件重量', sum(case when (substring(sworker,1,4)=@code) and paywise=3 then fmoney else 0 end) '寄付现金', sum(case when (substring(sworker,1,4)=@code) and paywise=4 then fmoney else 0 end) '收付月结', sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2) then fmoney else 0 end) '寄付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计', sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额', sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额', sum(fmoney) '营业额'from fastorder where data>=@begindate and data<=@enddate group by convert(char(2),month(data))+'.'+convert(char(2),day(data))
呵呵,要成为 存储过程高手 首先要成为 sql语句高手
set nocount on declare @code char(4) --定义区号 select @code='2001'declare @begdate datetime --定义起止日期 declare @enddate datetime select @begdate='2000-1-1' select @enddate='2004-1-1'declare @date datetime select @date=@begdatecreate table #temp (日期 datetime,件份数 int,收件重量 int,寄付现金 int,寄付月结 int, 到付金额 int,发件小计 int,收件份数 int,收件重量 int,寄付现金 int,收付月结 int,寄付金额 int, 收件小计 int,现金总额 int,月结总额 int,营业额 int)while @date<=@enddatebegininsert into #temp select convert(char(2),month(@date))+'.'+convert(char(2),day(@date)) '日期', sum(case when substring(workerno,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2) then weight else 0 end) '收件重量', sum(case when (substring(workerno,1,4)=@code) and paywise=1 then fmoney else 0 end) '寄付现金', sum(case when (substring(workerno,1,4)=@code) and paywise=2 then fmoney else 0 end) '寄付月结', sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4) then fmoney else 0 end) '到付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计',
sum(case when substring(sworker,1,4)=@code then 1 else 0 end) '收件份数', sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4) then weight else 0 end) '收件重量', sum(case when (substring(sworker,1,4)=@code) and paywise=3 then fmoney else 0 end) '寄付现金', sum(case when (substring(sworker,1,4)=@code) and paywise=4 then fmoney else 0 end) '收付月结', sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2) then fmoney else 0 end) '寄付金额', sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计', sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额', sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额', sum(fmoney) '营业额'
from fastorder where data=@dateset @date=@date+1endselect * from #tempdrop table #tempset nocount off
你可以使用一个中间表(T).日期号(DAYNO),记录是从1到28,29,30或31,当然你需要根据月份来确定。Create T(DayNO int) declare @Year int,@Month int,@D int,@END_DAY Select @End_Day = case when @Month in(1,3,5,7,8,10,12) then 31 when @Month in(4,6,9,11) then 30 when @Month = 2 and ((@Year%100<>0 And @Year%4 = 0) or @Year%400 = 0) then 29 else 28 endSelect @D = 1 while @D <= @End_Day begin Insert T values(@D) Select @D = @D+1 endSelect DayNo,A.* From T Left Join Tabel A on T.DayNo = Day(A.日期) Where 日期 between @FirstDay And @LastDay
select @code='2001'declare @begdate datetime --定义起止日期
declare @enddate datetime
select @begdate='2000-1-1'
select @enddate='2004-1-1'declare @date datetime
select @date=@begdateselect convert(char(2),month(@date))+'.'+convert(char(2),day(@date)) '日期',
sum(case when substring(workerno,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2)
then weight else 0 end) '收件重量',
sum(case when (substring(workerno,1,4)=@code) and paywise=1
then fmoney else 0 end) '寄付现金',
sum(case when (substring(workerno,1,4)=@code) and paywise=2
then fmoney else 0 end) '寄付月结',
sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4)
then fmoney else 0 end) '到付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计', sum(case when substring(sworker,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4)
then weight else 0 end) '收件重量',
sum(case when (substring(sworker,1,4)=@code) and paywise=3
then fmoney else 0 end) '寄付现金',
sum(case when (substring(sworker,1,4)=@code) and paywise=4
then fmoney else 0 end) '收付月结',
sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2)
then fmoney else 0 end) '寄付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计',
sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额',
sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额',
sum(fmoney) '营业额'from fastorder where (EXISTS (select @date=@date+1)) and @date<=@enddate and data=@date
日期 字段一 ........
1 dddd cccc dddd dadfad
2 asdf adfadf adsf adsfadsf
3 ......
4 .....
5 ......
6......
...
..
31.......
谢谢!
select date 日期 from tablename where date between '2002-1-1' and '2002-4-1'
select @code='2001'declare @begdate datetime --定义起止日期
declare @enddate datetime
select @begdate='2000-1-1'
select @enddate='2004-1-1'
select convert(char(2),month(data))+'.'+convert(char(2),day(data)) '日期',
sum(case when substring(workerno,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2)
then weight else 0 end) '收件重量',
sum(case when (substring(workerno,1,4)=@code) and paywise=1
then fmoney else 0 end) '寄付现金',
sum(case when (substring(workerno,1,4)=@code) and paywise=2
then fmoney else 0 end) '寄付月结',
sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4)
then fmoney else 0 end) '到付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计', sum(case when substring(sworker,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4)
then weight else 0 end) '收件重量',
sum(case when (substring(sworker,1,4)=@code) and paywise=3
then fmoney else 0 end) '寄付现金',
sum(case when (substring(sworker,1,4)=@code) and paywise=4
then fmoney else 0 end) '收付月结',
sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2)
then fmoney else 0 end) '寄付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计',
sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额',
sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额',
sum(fmoney) '营业额'from fastorder where data>=@begindate and data<=@enddate
group by convert(char(2),month(data))+'.'+convert(char(2),day(data))
declare @code char(4) --定义区号
select @code='2001'declare @begdate datetime --定义起止日期
declare @enddate datetime
select @begdate='2000-1-1'
select @enddate='2004-1-1'declare @date datetime
select @date=@begdatecreate table #temp (日期 datetime,件份数 int,收件重量 int,寄付现金 int,寄付月结 int,
到付金额 int,发件小计 int,收件份数 int,收件重量 int,寄付现金 int,收付月结 int,寄付金额 int,
收件小计 int,现金总额 int,月结总额 int,营业额 int)while @date<=@enddatebegininsert into #temp select convert(char(2),month(@date))+'.'+convert(char(2),day(@date)) '日期',
sum(case when substring(workerno,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(workerno,1,4)=@code) and (paywise=1 or paywise=2)
then weight else 0 end) '收件重量',
sum(case when (substring(workerno,1,4)=@code) and paywise=1
then fmoney else 0 end) '寄付现金',
sum(case when (substring(workerno,1,4)=@code) and paywise=2
then fmoney else 0 end) '寄付月结',
sum(case when (substring(workerno,1,4)=@code) and (paywise=3 or paywise=4)
then fmoney else 0 end) '到付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '发件小计',
sum(case when substring(sworker,1,4)=@code
then 1 else 0 end) '收件份数',
sum(case when (substring(sworker,1,4)=@code) and (paywise=3 or paywise=4)
then weight else 0 end) '收件重量',
sum(case when (substring(sworker,1,4)=@code) and paywise=3
then fmoney else 0 end) '寄付现金',
sum(case when (substring(sworker,1,4)=@code) and paywise=4
then fmoney else 0 end) '收付月结',
sum(case when (substring(sworker,1,4)=@code) and (paywise=1 or paywise=2)
then fmoney else 0 end) '寄付金额',
sum(case when substring(workerno,1,4)=@code then fmoney else 0 end) '收件小计',
sum(case when paywise=1 or paywise=3 then fmoney else 0 end) '现金总额',
sum(case when paywise=2 or paywise=4 then fmoney else 0 end) '月结总额',
sum(fmoney) '营业额'
from fastorder where data=@dateset @date=@date+1endselect * from #tempdrop table #tempset nocount off
declare @Year int,@Month int,@D int,@END_DAY
Select @End_Day = case when @Month in(1,3,5,7,8,10,12) then 31
when @Month in(4,6,9,11) then 30
when @Month = 2 and
((@Year%100<>0 And @Year%4 = 0) or @Year%400 = 0)
then 29
else 28
endSelect @D = 1
while @D <= @End_Day
begin
Insert T values(@D)
Select @D = @D+1
endSelect DayNo,A.* From T Left Join Tabel A on T.DayNo = Day(A.日期)
Where 日期 between @FirstDay And @LastDay