All Forums Database
bmcclatchey 8 posts Joined 02/16
16 Feb 2016
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);
 

dnoeth 4628 posts Joined 11/04
17 Feb 2016

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;

 

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

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

dnoeth 4628 posts Joined 11/04
17 Feb 2016

Seems you run a pre-TD14 version.
Replace it with TO_CHAR(mon, 'Month') with TRIM(DATE (FORMAT 'mmmm'))

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

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?

dnoeth 4628 posts Joined 11/04
17 Feb 2016

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

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

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

dnoeth 4628 posts Joined 11/04
17 Feb 2016

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

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

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

dnoeth 4628 posts Joined 11/04
17 Feb 2016

Arrrrgh,
I should read before I post:
TRIM(mon (FORMAT 'mmmm')),
 
Finally...

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

I really hate to post this, but I'm getting invalid format string.  Just curious, even if it's formatted correctly, will that first line TRIM(mon (FORMAT 'mmmm')), still select the current month and not the reporting month?  Sorry to keep coming back with problems.  I wish I was more self sufficient here.  THANKS! 

dnoeth 4628 posts Joined 11/04
17 Feb 2016

Maybe I should test before I write :-)
Of course mon is a number, no date format.

SELECT
   mon,
   TM_Loc_cd,
   shpmnts
FROM
 (
   SELECT
      EXTRACT(YEAR FROM isrt_tmstp) AS yr,
      EXTRACT(MONTH FROM isrt_tmstp) AS mth,
      TRIM((mth (FORMAT 'mmmm'))) 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,4
 ) AS dt
ORDER BY yr,mth;

 

Dieter

bmcclatchey 8 posts Joined 02/16
17 Feb 2016

Thanks for hanging in there with me!  
Now, I'm getting this error: GROUP BY and WITH BY clauses may not contain aggregate functions.  
By the way, I changed "mth" to "mon" on the last line.

dnoeth 4628 posts Joined 11/04
17 Feb 2016

Finally I double checked logic & syntax :-)

SELECT
   -- maybe add the year to the FORMAT 'yyyyBmmmmm'
   -- or simply keep the date as-is, then you don't need the outer Select
   TRIM(ym (FORMAT 'mmmm')), 
   TM_Loc_cd,
   shpmnts
FROM
 (
   SELECT
      -- first day of month, in TD14+ TRUNC(isrt_tmstp)
      CAST(isrt_tmstp AS DATE) - (EXTRACT(DAY FROM isrt_tmstp) -1) AS ym,
      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 ym, TM_Loc_cd
 ) AS dt
ORDER BY ym, TM_Loc_cd

 

Dieter

bmcclatchey 8 posts Joined 02/16
18 Feb 2016

Perfect!  Thank you so much for your help!  You are a lifesaver!  I really appreciate all of your effort!  

satyam12 2 posts Joined 03/16
04 Mar 2016

HI,
Need help to with SQL to solve below.
 
My table contains tow columns Member number and elig date (which is always beginning of the month)
Member #    Elig_dt                           Expected Result (no. of months continuously eligible)
=============                         =============
M1             2015/01/01                         1
M1             2015/02/01                         2                
M1             2015/03/01                         3
M1            2015/12/01                          1
M2            2015/01/01                          1
M2            2015/02/01                          2
M2           2015/12/01                           1
I need to count no. of months  a member is eligibile without interruption. if interruption the counter should be reset to 1
 
Any help from any one is greatly appreciated.
 
Thx
 
 
 

dnoeth 4628 posts Joined 11/04
05 Mar 2016

What's your Teradata release?
Since 14.10 there's LAST_VALUE:

select Member, Elig_dt,
                     -- find the previous gap's row number
   rn - last_value(case when flag = 1 then rn -1 end ignore nulls)
        over (partition by Member
              order by Elig_dt
              rows unbounded preceding)
from
 (
   select Member, Elig_dt,
      row_number()                -- row number without reset
      over (partition by Member
            order by Elig_dt) as rn,
      case when min(Elig_dt)      -- check for gap
                over (partition by Member
                      order by Elig_dt
                      rows between 1 preceding and 1 preceding)
                >= add_months(Elig_dt, -1)
           then 0                 -- no gap
           else 1                 -- gap
      end as flag
   from tab
 ) as dt

 

Dieter

venkat555 1 post Joined 03/16
17 Mar 2016
SEL MEMBER,ELIG_DT,SEQ FROM 
 ( 
 SEL MEMBER,ELIG_DT, EXTRACT( MONTH FROM ELIG_DT ) MN,
ROW_NUMBER () OVER ( PARTITION BY MEMBER ORDER BY ELIG_DT  ) RN ,
( CASE WHEN MN <> RN THEN 1 ELSE RN END ) SEQ
FROM CNT_TEST 
) A 

The above will be the simpler code
 
 

satyam12 2 posts Joined 03/16
06 Apr 2016

Thanks so much for your help. I have one more hurdle in my data.
If the member is appearing twice in a give month (elig_dt) then  i should be able to assign same value for that month like below.
MEMBER   ELIG_DT   EXPECTED RESULT
M1            2016/01/01    1
M1            2016/02/01    2
M1            2016/02/01    2
M1            2016/03/01    3
 
 

dnoeth 4628 posts Joined 11/04
06 Apr 2016

Switching to DENSE_RANK instead of ROW_NUMBER should work.

Dieter

You must sign in to leave a comment.