查询报表：代码案例-日期代码

今天日期的代码：
GetDate()

例句：查询到日日期到今天的
AND T0.[DocDueDate]<=GetDate()

条件里面如果用等于的话，需要转换下日期格式

CONVERT(varchar(100), GETDATE(), 102)

应用例句

SELECT T0.[ItemCode], T0.[Dscription], T0.[U_Spec], T0.[U_SaleCode], T0.[U_pwhsnum], (SELECT T10.[OnHand] FROM OITW T10 WHERE T10.itemcode=t0.itemcode and t10.whscode='11')as'原材料仓库存', T0.[U_pwhsnum],

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-6, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前7天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-5, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前6天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-4, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前5天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-3, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前4天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-2, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前3天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE()-1, 102) AND  T7.itemcode=T0.itemcode AND  Ts7.[DocStatus] ='O')as'前2天销量',

(SELECT T7.[Quantity] FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] = CONVERT(varchar(100), GETDATE(), 102)  AND  T7.itemcode=T0.itemcode AND Ts7.[CANCELED] ='N' AND  Ts7.[DocStatus] ='O')as'前1天销量',

(SELECT T0.[U_pwhsnum]/AVG(T7.[Quantity]) FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] =(getdate()-7 )  AND  T7.itemcode=T0.itemcode AND  Ts7.[CANCELED] ='N' AND  Ts7.[DocStatus] ='O')as'可销售天数',

(SELECT COUNT(T7.[Quantity]) FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] =(getdate()-7 )  AND  T7.itemcode=T0.itemcode AND  Ts7.[CANCELED] ='N' AND  Ts7.[DocStatus] ='O')as'近7天非0销售天数',

(SELECT AVG(T7.[Quantity]) FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] =(getdate()-7 )  AND  T7.itemcode=T0.itemcode AND  Ts7.[CANCELED] ='N' AND  Ts7.[DocStatus] ='O')as'近7天平均日销量',

(SELECT SUM(T7.[Quantity]) FROM QUT1 T7 INNER JOIN OQUT Ts7 ON T7.[DocEntry] = Ts7.[DocEntry] WHERE  Ts7.[TaxDate] =(getdate()-7 )  AND  T7.itemcode=T0.itemcode AND  Ts7.[CANCELED] ='N' AND  Ts7.[DocStatus] ='O')as'近7天总销量' FROM QUT1 T0  INNER JOIN OQUT T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[DocStatus] ='O' AND  T1.[CANCELED] ='N' AND  T1.[TaxDate] = CONVERT(varchar(100), GETDATE(), 102)

五六零

海报 链接