Tips & Tricks - How get all days of range even no values present

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))

This make a dataset start from date to another date that give in Galaxy too as sql params

(@DateFrom , @DateTo)

Same we can do if you want months of the range date with mm parameterΒ

dbo.GXGETITEMSUMDATEWRHSSTTP(GXITEMCOMPANYPROP.GXITEMID,’743D7942-4CCB-474B-B140-06011F6795CC’ ,cte.dte,’Bal_A’,

GXWAREHOUSE.GXID,’585BA65A-3BF4-426B-A7B7-B7F0C007BF52′)Β

‘743D7942-4CCB-474B-B140-06011F6795CC’ is guidΒ Company ID

‘585BA65A-3BF4-426B-A7B7-B7F0C007BF52’ is guid from tableΒ GXSTOCKTYPEΒ

Bal_A is code of stock variable Balance AΒ

In where clause check if stock item has balance at the start of date range or has transaction in range of dates

Another importan point is see how we can get filters variables in ours report in document properties

Look carefull how define filter variables and filter owners in Custom Report Design

Cloud ticket to import report isΒ C5-B4-9A-63-B2-64-E4-05

Note : Run on version Galaxy 11Β

Happy reporting !!

Tips & Tricks – How get all days of range even no values present

Αφήστε μια απάντηση Ακύρωση απάντησης