Calendar

[mandilicious]

Hi Champs

I am having a problem in creating calendar using below Query .In my dashboard date looks like above picture  so I need to separate day,month and year from above table? below query run smoothly .

SELECT distinct a.*, B.TRNS
FROM DBA.PRC_FACT A,
(
SELECT distinct PL_ID,
                CASE WHEN TRNS_CODE in (554,555,834,835,525,516,528,529) AND BULK_IND = 0 THEN 'CSD'
                     WHEN TRNS_CODE in (54,535,534,855,525,526,528,529) AND BULK_IND = 1 THEN 'CSDB'
                     WHEN TRNS_CODE in (101,152,155,156) AND BULK_IND = 0 THEN 'CQL'
                     WHEN TRNS_CODE in (450,632,934,936,938,940,942,944,946) AND BULK_IND = 0 THEN 'CIU'
                     WHEN TRNS_CODE in (741,722,776,776,703,794,711,71,711,722) AND BULK_IND = 0 THEN 'CBR'
                     WHEN TRNS_CODE in (721,742,777,78,703,794,111,112,721,712) AND BULK_IND = 1 THEN 'CSLK'
                     WHEN TRNS_CODE in (700,778,70,770,858,859) AND BULK_IND = 0 THEN 'CQDPADT'
                     WHEN TRNS_CODE in (702,73,779,780,795,796,711,714,713,724,127,728) AND BULK_IND = 0 THEN 'CQC'
                     ELSE 'N/A'
                END AS 'CHARGE_CODE',
                convert(int,sum(TRNS)) as 'TRNS'
FROM DBA.FACT_TRANSACTIONS f
GROUP BY PL_ID, CHARGE_CODE
)B
WHERE a.PROD_NAME = 'DA'
AND a.UCN = b.PL_ID
AND a.CHARGE_CODE = b.CHARGE_CODE

Thanking you in advance.

[Shubham_Deshmukh]

What exactly you want, tell me sample output

[mandilicious]

Hi

Thanks for quick respond: I want to  separate day,month and year. Cause when I pull this into dashboard it look like above. I need to have month(1) and Day(1) separate, and Year(2019) separate to each other.I hope I make sense.

If  can have day 1 in one list box and Year as well.

Thanks

[Shubham_Deshmukh]

See, still m not getting your requirement. Write you expected output.
If you want month, day and year from any date then you can get it from
=day(date)
=Month(date)
=Year(date)
And you want to show this in list box?

[Gysbert_Wassenaar · Accepted solution]

Try putting this line directly above your sql statement:

LOAD *, Year(PERIOD) as YEAR, Month(PERIOD) as MONTH, Day(PERIOD) as DAY;

[mandilicious]

Hi Gysbert

Thank you very much it worked perfect.

[mandilicious]

Hi Gysbert

Perfect it is  working perfect Thanks

[mandilicious]

Thanks for you assistant!