IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

forming a select query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 61
Location: San Diego

PostPosted: Sun Jun 14, 2009 8:47 pm
Reply with quote

Guys,

I am breaking my head on finding a query which could fetch me rows in this fashion,

i have a table which has data like this, data are dates having their respective year id'S and later week id's
sample of the table data:
Code:
yr-week-id    date         version
Y98         1998-01-01    1
Y98         1998-01-02    1   
:
:
Y98         1998-12-31    1
Y99         1999-01-01    1
Y99         1999-01-02    1   
:
:
Y99         1999-12-31    1
:
:
Y03         2003-01-01    2
Y03         2003-01-02    2   
:
:
Y03         2003-12-31    2
:
:

W0501        2005-01-01    2
W0501        2005-01-02    2
:

W0501         2005-01-07    2
W0502         2005-01-08    2
W0502         2005-01-09    2
:

W0502         2005-01-14    2 
:
:
W0513       2005-03-26    2
:
W0513          2005-04-01    2
W0514        2005-04-02    2
:
W0514        2005-04-08    2
:
:
W026          2005-06-25    2
W026          2005-07-01    2


i need to consolidate this data/dates into form of quarters of an year ,Y98's will have 4 rows denoting 4 quarters of an year and W0501 to W0513 will be one row
the select query results need to be like:
Code:
start-yr-week-id    end-yr-week-id   start-date       end-date
Y98                  Y98        1998-01-01       1998-03-31
Y98                  Y98        1998-04-01       1998-06-30
Y98                  Y98        1998-07-01       1998-09-30
Y98                  Y98        1998-10-01       1998-12-31
:
Y03                 Y03        2003-01-01       2003-03-31
Y03                 Y03        2003-04-01       2003-06-30
Y03                 Y03        2003-07-01       2003-09-30
Y03                 Y03        2003-10-01       2003-12-31
:
W0501                 W0513       2005-01-01      2005-03-31
W0513                 W026      2005-04-01           2005-06-30

adding a twist to the tale, is if a version comes inside a quarter mostly in week id's, i need to split that quarter into the number of rows as there are number versions coming between .

all i can think of is a cobol program with arrays and all, to create this data..but i can see a query which can also do the same job.

any hint of how to form the query/queries will be appreciated

Thanks
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue Jun 16, 2009 5:21 pm
Reply with quote

Hi rakesh17684

Your are trying to break our heads also by not providing adequate information in the post.

Please provide..
what is the condition for considering date as start_date and end_date?
Also start-yr-week-id and end-yr-week-id ?
And what is the relevalance of version?

Regards
Raghu
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 17, 2009 7:45 am
Reply with quote

Hello,

If the rules of the requirement cannot be clearly described/documented, it will be most difficult to implement - whether sql or cobol is used. . .

Example output is a very good thing, but the rules must be clearly presented explaining how the sample output was chosen for the samnple input.
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 61
Location: San Diego

PostPosted: Wed Jun 17, 2009 9:43 am
Reply with quote

Raghu,

Quote:
Please provide..
what is the condition for considering date as start_date and end_date?


The start_date and end_date are like the start dates and end dates of quarters in a given calendar year ..its like jan 1 to mar 31, april 1 to jun 30th,jul 1 to sept 30 and oct 1 to dec 31.

Quote:
Also start-yr-week-id and end-yr-week-id ?

each of the dates have their corresponding id's picked up from the input data i provided. say jan1 of 1998 will have Y98, jan 1 of 2005 will have W0501, march 31 of 2005 will have W0513 and june 26 of 2005 will have W026. only difference is from 2005 the dates started having/ were grouped with week id's and before that with year id's

Code:
Y98         1998-12-31    1
Y99         1999-01-01    1
Y99         1999-01-02    1   
:
:
Y99         1999-12-31    1
:
:
Y03         2003-01-01    2
Y03         2003-01-02    2   
:
:
Y03         2003-12-31    2

W0501        2005-01-01    2
W0501        2005-01-02    2
:

W0501         2005-01-07    2
W0502         2005-01-08    2
W0502         2005-01-09    2
:

W0502         2005-01-14    2


hope i am clear this time. for the time being i am ignoring the relevance of version. can u make out an query with out that.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 17, 2009 12:48 pm
Reply with quote

1. The year - year/week-id column would be only display data.

2. is the date column a db2 date datatype or is it a decimal numeric column?

3. normally when things are grouped or consolidated, the purpose is to provide a count/total by group - you don't indicate such.

4. are there other columns that are to be selected? It seems as if you have attempted to define an ORDER BY problem as a subset of your data.

5. The quarter data/columns (start quarter, end quarter) can be generated from a CASE SELECT statement with an AS clause.

6. the sort would be on date.
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 61
Location: San Diego

PostPosted: Wed Jun 17, 2009 1:38 pm
Reply with quote

Just to add more clarity , i added some replies.. thanks for the suggestions

Quote:
1. The year - year/week-id column would be only display data.

year - year/week-id are id's of data tables we have, yes they are display data .
Quote:
2. is the date column a db2 date datatype or is it a decimal numeric column?

date column is of db2 date datatype .
Quote:
3. normally when things are grouped or consolidated, the purpose is to provide a count/total by group - you don't indicate such.

here the purpose is to consolidate these id's to a quarter id's finally.
Quote:
4. are there other columns that are to be selected? It seems as if you have attempted to define an ORDER BY problem as a subset of your data.

no other columns are present, if its the version u are talkin abt plz ignore it as of now.
Quote:
5. The quarter data/columns (start quarter, end quarter) can be generated from a CASE SELECT statement with an AS clause.

am pondering abt it now. still a learner of SQL. icon_neutral.gif
Quote:
6. the sort would be on date

yes/ok
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 17, 2009 2:25 pm
Reply with quote

Quote:
am pondering abt it now. still a learner of SQL


i would suggest: stop pondering, and start coding and learning.

this could easily be built in spufi.

If you need links to documents, the 'IBM MANUALS' button at the top of the page will provide them.

look for the CASE statement, there are examples in the documents.
look at both the SQL Reference and the SQL Guide for whichever vsn of db2 is running at your site.

first step is to build your SELECT statements so that each row contains the values from columns (and derived values - quarter start/end)
Code:

SELECT
YEAR-MONTH-ID
,datecolumn
,CASE
  WHEN MONTH(datecolumn) between 1 and 3
           THEN CHAR(YEAR(datecolumn)) || "01-01" AS QTR_START
   ...
END
,CASE
  WHEN MONTH(datecolumn) between 1 and 3
           THEN CHAR(YEAR(datecolumn)) || "03-31" AS QTR_END
   ...
END
,
...
FROM your-table
ORDER BY datecolumn
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 61
Location: San Diego

PostPosted: Fri Jun 19, 2009 10:18 am
Reply with quote

thanks dick ,

i came up with another simple query, which would satisfy the requirement.

Code:
select a.YEAR-MONTH-ID,b.YEAR-MONTH-ID,a.datecolumn,b.datecolumn from table1 a,table1 b  where
(char(day(a.datecolumn)) = '1' and  char(month(a.datecolumn)) = '1' and
char(day(b.datecolumn)) = '31' and  char(month(b.datecolumn)) = '3' and char(year(a.datecolumn)) = char(year(b.datecolumn)))or
(char(day(a.datecolumn)) = '1' and  char(month(a.datecolumn)) = '4' and
char(day(b.datecolumn)) = '30' and  char(month(b.datecolumn)) = '6' and char(year(a.datecolumn)) = char(year(b.datecolumn)))or
(char(day(a.datecolumn)) = '1' and  char(month(a.datecolumn)) = '7' and
char(day(b.datecolumn)) = '30' and  char(month(b.datecolumn)) = '9' and char(year(a.datecolumn)) = char(year(b.datecolumn)))or
(char(day(a.datecolumn)) = '1' and  char(month(a.datecolumn)) = '10' and
char(day(b.datecolumn)) = '31' and  char(month(b.datecolumn)) = '12' and char(year(a.datecolumn)) = char(year(b.datecolumn)) )
 with ur;
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts SELECT from data change table DB2 5
Search our Forums:

Back to Top