SERVER月报表语句解决方法

2012-03-08

SERVER月报表语句我有类似这么一张表(销售表)idnamecreateDate(datetime)sale(int)1A2011-11-11 12:10:102

SERVER月报表语句
我有类似这么一张表(销售表)
id name createDate(datetime) sale(int)
1 A 2011-11-11 12:10:10 2
2 A 2011-11-12 13:10:10 4
3 A 2011-11-14 13:10:10 4
4 B 2011-11-14 13:10:10 4
5 B 2011-11-18 13:10:10 4
.................................

还有一张人员表
id name
1 A
2 B
3 C

月报要求格式
createDate name saleTotal
2011-11-01 A 100
2011-11-01 B 23
2011-11-02 A 100
2011-11-02 B 100
2011-11-03 A 100
2011-11-03 B 0
..........................
2011-11-30 A 100
2011-11-30 B 100

不管某天是否有销售记录，都必须有统计信息，如A,B在11-6日都未销售出产品，
也要输出如下：
2011-11-06 A 0
2011-11-06 B 0

要根据选择的日期从1号输出到当月最后一天，最后一天可能是30,31,29,28

怎么写啊，我想了好久都没搞定，
这个是写报表最基本的要求好像。。

[解决办法]

SQL code

declare @startdate datetime,@enddate datetimeset @startdate=dateadd(mm,datediff(mm,0,getdate()),0)set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))select convert(varchar(10),dateadd(day,number,@startdate),120) as colfrom    master..spt_values where     datediff(day,dateadd(day,number,@startdate), @enddate)>=0    and number>=0     and type='p'/*col----------2011-11-012011-11-022011-11-032011-11-042011-11-052011-11-062011-11-072011-11-082011-11-092011-11-102011-11-112011-11-122011-11-132011-11-142011-11-152011-11-162011-11-172011-11-182011-11-192011-11-202011-11-212011-11-222011-11-232011-11-242011-11-252011-11-262011-11-272011-11-282011-11-292011-11-30(30 行受影响)*/
[解决办法]
declare @startdate datetime,@enddate datetime
set @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

select convert(varchar(10),dateadd(day,number,@startdate),120) as col
from
   master..spt_values  
where  
   datediff(day,dateadd(day,number,@startdate), @enddate)>=0
   and number>=0  
   and type='p'

[解决办法]
SQL codeuse Tempdbgo--> -->  if not object_id(N'Tempdb..#salesOrder') is null    drop table #salesOrderGoCreate table #salesOrder([id] int,[name] nvarchar(1),[createDate] Datetime,[sale] int)Insert #salesOrderselect 1,N'A','2011-11-11 12:10:10',2 union allselect 2,N'A','2011-11-12 13:10:10',4 union allselect 3,N'A','2011-11-14 13:10:10',4 union allselect 4,N'B','2011-11-14 13:10:10',4 union allselect 5,N'B','2011-11-18 13:10:10',4Goif not object_id(N'Tempdb..#Staff') is null    drop table #StaffGoCreate table #Staff([id] int,[name] nvarchar(1))Insert #Staffselect 1,N'A' union allselect 2,N'B' union allselect 3,N'C'GoDECLARE @dt1 DATETIME,@dt2 DATETIMESET @dt1='2011-11-01'SET @dt2='2011-11-30';WITH dtAS(SELECT @dt1 AS dtUNION ALLSELECT dt+1 FROM dt WHERE dt<@dt2)Select a.[name],CONVERT(VARCHAR(10),b.dt,120) AS dt,ISNULL(SUM(c.sale),0) AS salefrom #Staff AS aCROSS JOIN dt AS b LEFT JOIN #salesOrder AS c ON a.[name]=c.[name] AND DATEDIFF(d,b.dt,c.[createDate])=0GROUP BY a.[name],CONVERT(VARCHAR(10),b.dt,120)ORDER BY 1,2
[解决办法]
SQL codedeclare @startdate datetime,@enddate datetimeset @startdate=dateadd(mm,datediff(mm,0,getdate()),0)set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0));with f as(select  isnull(a.name,b.name) as name,CONVERT(varchar(10),[createDate],120) as createDate,SUM(sale) as saletotalfrom  a full join b on  a.name=b.name group by  a.name,b.name,CONVERT(varchar(10),[createDate],120))select   a.name,b.createDate,isnull(a.saletotal,0) as saletotalfrom(select    convert(varchar(10),dateadd(day,number,@startdate),120) as createDatefrom    master..spt_values where     datediff(day,dateadd(day,number,@startdate), @enddate)>=0    and number>=0     and type='p')bleft join  f aon  a.createDate=b.createDate

热点排行

SQL Server

SERVER月报表语句解决方法