In this post we learn how we can get all days of range even no values present in these days
For example we get for a range of days balance of stocks even no transaction made in some of days in range
Β
Sql that return values for above sample is below
Β
with cte as
(
select dte = CONVERT(datetime,SUBSTRING(CAST(‘@DateFrom’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateFrom’ AS VARCHAR)) – 1),103)
union all
select DATEADD(dd,1,dte) from cte where dte < CONVERT(datetime,SUBSTRING(CAST(‘@DateTo’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateTo’ AS VARCHAR)) – 1),103))
select CONVERT(nvarchar,cte.dte,103), GXITEM.GXCODE ITEMCODE,GXITEM.GXDESCRIPTION,GXWAREHOUSE.GXCODE WAREHOUSECODE ,
dbo.GXGETITEMSUMDATEWRHSSTTP(GXITEMCOMPANYPROP.GXITEMID,’743D7942-4CCB-474B-B140-06011F6795CC’ ,cte.dte,’Bal_A’,
GXWAREHOUSE.GXID,’585BA65A-3BF4-426B-A7B7-B7F0C007BF52′) BALANCE
FROM cte,GXWAREHOUSE,
GXITEMCOMPANYPROP inner join GXITEM ON GXITEMCOMPANYPROP.GXITEMID=GXITEM.GXID
WHEREΒ
(
GXITEMCOMPANYPROP.GXITEMID in (select GXITEMACCUM.GXITEMID FROM GXITEMACCUM WHEREΒ
GXITEMACCUM.GXDATE>=CONVERT(datetime,SUBSTRING(CAST(‘@DateFrom’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateFrom’ AS VARCHAR)) – 1),103) AND GXITEMACCUM.GXDATE<=CONVERT(datetime,SUBSTRING(CAST(‘@DateTo’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateTo’ AS VARCHAR)) – 1),103)
AND GXITEMACCUM.GXWRHSID=GXWAREHOUSE.GXID )
or
dbo.GXGETITEMSUMDATEWRHSSTTP(GXITEMCOMPANYPROP.GXITEMID,’743D7942-4CCB-474B-B140-06011F6795CC’ ,CONVERT(datetime,SUBSTRING(CAST(‘@DateFrom’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateFrom’ AS VARCHAR)) – 1),103),’Bal_A’,
GXWAREHOUSE.GXID,’585BA65A-3BF4-426B-A7B7-B7F0C007BF52′)<>0)
@where
ORDER BY GXITEM.GXCODE,CTE.dte
Let’s see some part of sql command
Β
Β
with cte as
(
select dte = CONVERT(datetime,SUBSTRING(CAST(‘@DateFrom’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateFrom’ AS VARCHAR)) – 1),103)
union all
select DATEADD(dd,1,dte) from cte where dte < CONVERT(datetime,SUBSTRING(CAST(‘@DateTo’ AS VARCHAR),1,CHARINDEX(‘ ‘, CAST(‘@DateTo’ AS VARCHAR)) – 1),103))