Help with SQL for reporting month totals

Database

Help with SQL for reporting month totals

Please forgive me if this is a basic question, but I have found myself responsible for creating SQL reports which is not my forte.  Any help will be greatly appreciated!

I have current SQL as follows for another report.  I have been asked to provide monthly totals in the format below.  Can anyone please provide the correct SQL to do this?  I have played around with it for the last day, but everything I have tried doesn't give me the exact result I'm looking for.  Thank you in advance!

November:  ABCD:  300

                  EFGH: 275

                  IJKL:   469

December:  ABCD:  220

                  EFGH: 145

                  IJKL:   590

etc.        

select COUNT(mtr_nbr) AS shpmnts, CAST(isrt_tmstp AS DATE),

CASE mtr_nbr 

   WHEN '123456' THEN 'ABCD'

   WHEN '234567' THEN 'EFGH'

   WHEN '345678' THEN 'IJKL'

END as TM_Loc_cd

  from DB.table

where isrt_tmstp BETWEEN '2016-02-08 00:00:00' AND '2016-02-14 23:59:00' and

mtr_nbr in (

123456,

234567,

345678)

group by TM_Loc_cd, CAST(isrt_tmstp AS DATE)

order by TM_Loc_cd, CAST(isrt_tmstp AS DATE);

 

Tags (4)
19 REPLIES
N/A

Re: Help with SQL for reporting month totals

Get the year/month using EXTRACT

SELECT
TO_CHAR(mon, 'Month'),
TM_Loc_cd,
shpmnts
FROM
(
SELECT
EXTRACT(YEAR FROM isrt_tmstp) AS yr,
EXTRACT(MONTH FROM DATE) AS mon,
CASE mtr_nbr
WHEN '123456' THEN 'ABCD'
WHEN '234567' THEN 'EFGH'
WHEN '345678' THEN 'IJKL'
END AS TM_Loc_cd,
COUNT(mtr_nbr) AS shpmnts
FROM DB.TABLE
WHERE isrt_tmstp >= TIMESTAMP '2015-11-01 00:00:00' -- novemvber 2015
AND isrt_tmstp < TIMESTAMP '2016-03-01 00:00:00' -- to february 2016
AND mtr_nbr IN (123456,234567,345678)
GROUP BY 1,2,3
) AS dt
ORDER BY yr,1,2;

If you don't want one row per mtr_nbr you can pivot using conditional aggregation:

SELECT
TO_CHAR(mon, 'Month'),
TM_Loc_cd,
shpmnts
FROM
(
SELECT
EXTRACT(YEAR FROM isrt_tmstp) AS yr,
EXTRACT(MONTH FROM DATE) AS mon,
SUM(CASE mtr_nbr WHEN '123456' THEN 1 ELSE 0 end) AS "ABCD",
SUM(CASE mtr_nbr WHEN '234567' THEN 1 ELSE 0 end) AS "EFGH",
SUM(CASE mtr_nbr WHEN '345678' THEN 1 ELSE 0 end) AS "IJKL",
COUNT(mtr_nbr) AS shpmnts
FROM DB.TABLE
WHERE isrt_tmstp >= TIMESTAMP '2015-11-01 00:00:00' -- novemvber 2015
AND isrt_tmstp < TIMESTAMP '2016-03-01 00:00:00' -- to february 2016
AND mtr_nbr IN (123456,234567,345678)
GROUP BY 1,2
) AS dt
ORDER BY yr,1;

Re: Help with SQL for reporting month totals

Thank you very much for your reply!  I'm getting an error on TO_CHAR.  It says it's invalid.  Any suggestions?

N/A

Re: Help with SQL for reporting month totals

Seems you run a pre-TD14 version.

Replace it with TO_CHAR(mon, 'Month') with TRIM(DATE (FORMAT 'mmmm'))

Re: Help with SQL for reporting month totals

Thanks again!  We're getting close.  When I ran the first solution with the TRIM modification, I'm getting results only for February.  I would like to see multiple months listed, with each showing totals by location.  I'm sorry to keep asking questions on the same topic.  Do you have any ideas?

N/A

Re: Help with SQL for reporting month totals

Look at your WHERE-condition, in the query you posted it was limiting to rows from february, my version uses multiple months.

Re: Help with SQL for reporting month totals

Thanks again!  See my SQL and output below.  The WHERE statement is from November-January, but it's still only pulling Feb.  I'm positive I have data for the other months.  I really appreciate your help!  

SELECT

   TRIM(DATE (FORMAT 'mmmm')),

   TM_Loc_cd,

   shpmnts

FROM

 (

   SELECT

      EXTRACT(YEAR FROM isrt_tmstp) AS yr,

      EXTRACT(MONTH FROM DATE) AS mon,

      CASE mtr_nbr 

      WHEN '6996239' THEN  'ADSKI'

      WHEN '6996155' THEN  'TRLKO'

     WHEN '6996137' THEN  'DALKC'

      END AS TM_Loc_cd,

      COUNT(mtr_nbr) AS shpmnts

   FROM DB.table  -- changed this for posting to this forum

   WHERE isrt_tmstp >= TIMESTAMP '2015-11-01 00:00:00' -- november 2015

     AND isrt_tmstp <  TIMESTAMP '2016-03-01 00:00:00' -- to february 2016

     AND mtr_nbr IN (6996239,6996155,6996137)

   GROUP BY 1,2,3

 ) AS dt

ORDER BY yr,1;

 

Output:

February,ADSKI,1

February,TRLKO,34

February,DALKC,2

N/A

Re: Help with SQL for reporting month totals

Too much cut & paste...

It's the EXTRACT(MONTH FROM DATE) AS mon, of course this should be EXTRACT(MONTH FROM isrt_tmstp) AS mon, instead.

DATE = CURRENT_DATE = February 2016 :-)

Re: Help with SQL for reporting month totals

Thanks again.  There is one last problem and then I won't bother you anymore.  The SELECT TRIM(DATE (FORMAT 'mmmm')), is selecting the current date, not the reporting date, which is why everything is showing in February.  Any idea how to fix this one last problem so it will show the reporting month and not the current month?

THANKS!!!!!  

N/A

Re: Help with SQL for reporting month totals

Arrrrgh,

I should read before I post:

TRIM(mon (FORMAT 'mmmm')),

Finally...