MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

remnent from SQL Challenge Question 1

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
satya_reddy
Beginner


Joined: 21 Sep 2004
Posts: 16
Topics: 3
PostPosted: Thu Sep 30, 2004 7:27 am    Post subject: remnent from SQL Challenge Question 1 Reply with quote
Hi all,
Before I start, thanks to Kolusu, for the good SQL Challenge Question 1. I used it in multiple examples. One visualization is getting complicated. I do not know, If I can get the output the desired way.

Query:
Code:

SELECT DISTINCT C.ID                                                     
  ,SUM(CASE WHEN MN = 1 THEN C.CNT ELSE 0 END) AS JAN                     
  ,SUM(CASE WHEN MN = 2 THEN C.CNT ELSE 0 END) AS FEB                     
  ,SUM(CASE WHEN MN = 3 THEN C.CNT ELSE 0 END) AS MAR                     
  ,SUM(CASE WHEN MN = 4 THEN C.CNT ELSE 0 END) AS APR                     
  ,SUM(CASE WHEN MN = 5 THEN C.CNT ELSE 0 END) AS MAY                     
  ,SUM(CASE WHEN MN = 6 THEN C.CNT ELSE 0 END) AS JUN                     
  ,SUM(CASE WHEN MN = 7 THEN C.CNT ELSE 0 END) AS JUL                     
  ,SUM(CASE WHEN MN = 8 THEN C.CNT ELSE 0 END) AS AUG                     
  ,SUM(CASE WHEN MN = 9 THEN C.CNT ELSE 0 END) AS SEP                     
  ,SUM(CASE WHEN MN = 10 THEN C.CNT ELSE 0 END) AS OCT                   
  ,SUM(CASE WHEN MN = 11 THEN C.CNT ELSE 0 END) AS NOV                   
  ,SUM(CASE WHEN MN = 12 THEN C.CNT ELSE 0 END) AS DEC                   
  FROM (SELECT ITSSADLP_ID_RACF         AS ID                             
              ,ITSSADLP_REG_TAB         AS REG                           
              ,COUNT (ITSSADLP_ID_RACF) AS CNT                           
              ,YEAR  (ITSSADLP_STAT_TS) AS YR                             
              ,MONTH (ITSSADLP_STAT_TS) AS MN                             
          FROM SCITST.ITSSADLP                                           
         GROUP BY ITSSADLP_ID_RACF, ITSSADLP_REG_TAB, ITSSADLP_STAT_TS ) C
GROUP BY C.ID                                                             
ORDER BY C.ID                                                             

Output I got is as,
Code:

ID               JAN          FEB          MAR          APR          MAY
-------  -----------  -----------  -----------  -----------  -----------
D027059            0            0            0           11           11
D840069            0            0            0            0            1
D840083            0            0            0            0           30
D840103            0            0            0            0            4
D840136            0            0            0            0           28
D840202            0            0            0            0            0
D840263            0            0            0            0            0
D840267            0            0            0            0            4
D840458            0            0            0            0            0
D840508            0            0            0            0            0
D840581            0            0            0            0            0
D840582            0            0            0            0            0
D840615            0            0            0            0            0


     JUN          JUL          AUG          SEP          OCT          NOV
--------  -----------  -----------  -----------  -----------  -----------
       4            0            0            0            0            0
       0            0            0            0            0            0
       0           92            2            0            0            0
       0            0            0            0            0            0
       1            0            8           40            0            0
       0            0            1            0            0            0
       0            0            0           40            0            0
       0            2           58           31            0            0
       0           68            0            0            0            0
       0            0          103            0            0            0
       4            1            0            0            0            0
       0           13            5            0            0            0
       0            0            0            6            0            0

        DEC
-----------
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0

The Desired out I needed was
for the last 12 months. i.e from 09/29/04 to 10/01/2004. The output counts are ok. Output should start monthwise from 2004-sept, 2004-Aug,.. so on till 2003-Sep. Is it possibl to get. The SQL should be able to automatedly alighn the counts with current month in first Column, and Oct 2003 in last column.

Question 2: If I have to calculate the start date from 1 year from today, but avoiding the current month, i.e Date between 09/29/2004 thre 10/01/2004. or 10/16/2004 thru 11/01/2004. Is it thru Cobol manupulation or SQL has some logic to provide date ranges for last months in year.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Sep 30, 2004 8:09 am    Post subject: Reply with quote
Satya reddy,

Quote:

Question 2: If I have to calculate the start date from 1 year from today, but avoiding the current month, i.e Date between 09/29/2004 thre 10/01/2004. or 10/16/2004 thru 11/01/2004. Is it thru Cobol manupulation or SQL has some logic to provide date ranges for last months in year.


Satya your dates are all messed up. I am confused with your date range. I am taking you wanted to get
Code:

If I have to calculate the start date from 1 year from today, but avoiding the current month



The following sql will give you the date range

Code:

SELECT                                             
     DATE(LAST_DAY(CURRENT DATE) + 1 DAY)           
    ,DATE(LAST_DAY(CURRENT DATE) + 1 DAY + 1 YEAR) 
FROM SYSIBM.SYSDUMMY1                               
;                                                   


The output is :
Code:

2004-10-01  2005-10-01


As for your first question, I am still trying to understand your requirement.

Let us say your table has 2 columns ID & date and here is a sample data
Code:

ID          DATE   
====     ==========
KEY1     2003-10-10
KEY1     2003-10-11
KEY1     2003-10-12
KEY1     2004-01-01
KEY1     2004-02-01
KEY1     2004-09-28

KEY2     2003-11-10
KEY2     2003-12-11
KEY2     2003-01-12
KEY2     2004-04-01
KEY2     2003-05-01



Now you want it as

Code:

ID       Mon1   Mon2  Mon3  Mon4   Mon5  Mon6  Mon7   Mon8  Mon9  Mon10   Mon11  Mon12

Key1     1       0     0     0      0     0    1       1      0    0       0      3
   
Key2     0       0     0     1      1     0    0       1      1    1       0      0


Is this how you wanted ?

Key1 has 3 records in oct of 2003. so mon 12 will have 3
key1 has 1 record in Jan 2004, so mon 8 will have 1
key1 has 1 record in feb 2004, so mon 7 will have 1
key1 has 1 record in sep 2004, so mon 1 will have 1

simlarly for key2..

Let me know if this what you wanted. If it is different then please try to show me an example of the data as I have shown.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
satya_reddy
Beginner


Joined: 21 Sep 2004
Posts: 16
Topics: 3
PostPosted: Thu Sep 30, 2004 11:59 pm    Post subject: Reply with quote
Thanks Kolusu. I wanted the data for the last 12 months, beginning from the current month. The query, I am trying to test is as below.

SELECT C.ID
,SUM(CASE WHEN MN = (CURRENT MONTH) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 1 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 2 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 3 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 4 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 5 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 6 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 7 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 8 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 9 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 10 MON) THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = (CUR MON - 11 MON) THEN C.CNT ELSE 0 END)
FROM (SELECT ITSSADLP_ID_RACF AS ID
,ITSSADLP_REG_TAB AS REG
,COUNT (ITSSADLP_ID_RACF) AS CNT
,YEAR (ITSSADLP_STAT_TS) AS YR
,MONTH (ITSSADLP_STAT_TS) AS MN
FROM SCITST.ITSSADLP
GROUP BY ITSSADLP_ID_RACF, ITSSADLP_REG_TAB, ITSSADLP_STAT_TS ) C
GROUP BY C.ID
ORDER BY C.ID

This is same as what you specified, but months go back, as 2004/SEPT, 2004/AUG, 2004/JUL, 2004/JUN, 2004/MAY, 2004/APR, 2004/MAR, 2004/FEB, 2004/JAN, 2003/DEC, 2003/NOV, 2003/OCT. I shall also put it per your example.
Back to top
View user's profile Send private message
satya_reddy
Beginner


Joined: 21 Sep 2004
Posts: 16
Topics: 3
PostPosted: Fri Oct 01, 2004 12:16 am    Post subject: Reply with quote
ID Mon1 Mon2 Mon3 Mon4 Mon5 Mon6 Mon7 Mon8 Mon9 Mon10 Mon11 Mon12

Key1 1 0 0 0 0 0 1 1 0 0 0 3

Key2 0 0 0 1 1 0 0 1 1 1 0 0

Mon 1 is 2004/OCT
Mon 2 is 2004/SEPT
Mon 3 is 2004/AUG
Mon 4 is 2004/JUL
Mon 5 is 2004/JUN
Mon 6 is 2004/MAY
Mon 7 is 2004/APR
Mon 8 is 2004/MAR
Mon 9 is 2004/FEB
Mon 10 is 2004/JAN
Mon 11 is 2003/DEC
Mon 12 is 2003/NOV

I am able to get the output, if I hardcode month, but if I want to let SQL identify the months itself, I am ending up in errors.
For Ex. ,SUM(CASE WHEN MN = (CURRENT MONTH) THEN C.CNT ELSE 0 END) or ,SUM(CASE WHEN MN = (CURRENT MONTH - 1 month) THEN C.CNT ELSE 0 END). SQL is not accepting the syntax MN = (CURRENT MONTH), but it allows MN = 1. Can you help in correcting my syntax.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Oct 01, 2004 9:01 am    Post subject: Reply with quote
Satya_reddy,

I am not sure how efficient the following query is but here is sql. I assumed that your table has 2 columns.
Code:

ID
ORD_DATE

Code:

SELECT ID                                                               
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 0 MONTH) THEN 1 ELSE 0 END) AS MON01 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 2 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 1 MONTH) THEN 1 ELSE 0 END) AS MON02 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 3 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 2 MONTH) THEN 1 ELSE 0 END) AS MON03 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 4 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 3 MONTH) THEN 1 ELSE 0 END) AS MON04 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 5 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 4 MONTH) THEN 1 ELSE 0 END) AS MON05 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 6 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 5 MONTH) THEN 1 ELSE 0 END) AS MON06 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 7 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 6 MONTH) THEN 1 ELSE 0 END) AS MON07 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 8 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 7 MONTH) THEN 1 ELSE 0 END) AS MON08 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 9 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 8 MONTH) THEN 1 ELSE 0 END) AS MON09 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 10 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 9 MONTH) THEN 1 ELSE 0 END) AS MON10 
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 11 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 10 MONTH) THEN 1 ELSE 0 END) AS MON11
      ,SUM(CASE WHEN ORD_DATE BETWEEN                                   
           DATE(LAST_DAY(CURRENT DATE - 12 MONTH) + 1 DAY)  AND         
           LAST_DAY(CURRENT DATE- 11 MONTH) THEN 1 ELSE 0 END) AS MON12
  FROM TABLE         
 GROUP BY ID       
 ;



The logic behind this is to generate the date range. Hopefully the following query will give you an idea

Code:

SELECT DATE(LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY) AS M01_START_DATE
      ,LAST_DAY(CURRENT DATE- 0 MONTH) AS M01_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 2 MONTH) + 1 DAY) AS M01_START_DATE   
      ,LAST_DAY(CURRENT DATE- 1 MONTH) AS M02_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 3 MONTH) + 1 DAY) AS M03_START_DATE   
      ,LAST_DAY(CURRENT DATE- 2 MONTH) AS M03_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 4 MONTH) + 1 DAY) AS M04_START_DATE   
      ,LAST_DAY(CURRENT DATE- 3 MONTH) AS M04_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 5 MONTH) + 1 DAY) AS M05_START_DATE   
      ,LAST_DAY(CURRENT DATE- 4 MONTH) AS M05_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 6 MONTH) + 1 DAY) AS M06_START_DATE   
      ,LAST_DAY(CURRENT DATE- 5 MONTH) AS M06_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 7 MONTH) + 1 DAY) AS M07_START_DATE   
      ,LAST_DAY(CURRENT DATE- 6 MONTH) AS M07_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 8 MONTH) + 1 DAY) AS M08_START_DATE   
      ,LAST_DAY(CURRENT DATE- 7 MONTH) AS M08_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 9 MONTH) + 1 DAY) AS M09_START_DATE   
      ,LAST_DAY(CURRENT DATE- 8 MONTH) AS M09_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 10 MONTH) + 1 DAY) AS M10_START_DATE   
      ,LAST_DAY(CURRENT DATE- 9 MONTH) AS M10_END_DATE                     
      ,DATE(LAST_DAY(CURRENT DATE - 11 MONTH) + 1 DAY) AS M11_START_DATE   
      ,LAST_DAY(CURRENT DATE- 10 MONTH) AS M11_END_DATE                   
      ,DATE(LAST_DAY(CURRENT DATE - 12 MONTH) + 1 DAY) AS M12_START_DATE   
      ,LAST_DAY(CURRENT DATE- 11 MONTH) AS M12_END_DATE                   
  FROM SYSIBM.SYSDUMMY1                                               
  ;                                                                   


Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
satya_reddy
Beginner


Joined: 21 Sep 2004
Posts: 16
Topics: 3
PostPosted: Mon Nov 29, 2004 9:11 pm    Post subject: Reply with quote
Thanks Kosulu for your help as it really helped me. I am back from a long vacation. The production run was also good.

I used the above query to populate host variables and used in the following query, to get perfect results.
Back to top
View user's profile Send private message
satya_reddy
Beginner


Joined: 21 Sep 2004
Posts: 16
Topics: 3
PostPosted: Mon Nov 29, 2004 9:16 pm    Post subject: Reply with quote
SELECT C.ADLPPLNCD
,SUM(CASE WHEN MN = :MONTH-1 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-2 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-3 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-4 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-5 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-6 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-7 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-8 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-9 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-10 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-11 THEN C.CNT ELSE 0 END)
,SUM(CASE WHEN MN = :MONTH-12 THEN C.CNT ELSE 0 END)
FROM (SELECT ADLP_LOC_PLAN_CD AS ADLPPLNCD
, COUNT(ADLP_LOC_PLAN_CD) AS CNT
, YEAR(ADLP_LAST_UPDT) AS YR
, MONTH(ADLP_LAST_UPDT) AS MN
, DATE(ADLP_LAST_UPDT) AS DT
FROM ADLPCRHS
GROUP BY ADLP_LOC_PLAN_CD
, ADLP_LAST_UPDT) C
WHERE DATE(C.DT) BETWEEN
:START-DATE AND :END-DATE
GROUP BY C.ADLPPLNCD
ORDER BY C.ADLPPLNCD
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group