topic Re: Calendar in App Development

Calendar

mandilicious — Fri, 02 Apr 2021 07:51:02 GMT

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.

Re: Calendar

Shubham_Deshmukh — Wed, 06 Mar 2019 09:30:02 GMT

What exactly you want, tell me sample output

Re: Calendar

mandilicious — Wed, 06 Mar 2019 09:38:11 GMT

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

Re: Calendar

Shubham_Deshmukh — Wed, 06 Mar 2019 09:46:07 GMT

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?

Re: Calendar

Gysbert_Wassenaar — Wed, 06 Mar 2019 11:09:19 GMT

Try putting this line directly above your sql statement:

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

Re: Calendar

mandilicious — Wed, 06 Mar 2019 11:30:32 GMT

Hi Gysbert

Thank you very much it worked perfect.

Re: Calendar

mandilicious — Wed, 06 Mar 2019 11:33:29 GMT

Hi Gysbert

Perfect it is working perfect Thanks

Re: Calendar

mandilicious — Wed, 06 Mar 2019 11:42:34 GMT

Thanks for you assistant!