View previous topic :: View next topic
|
Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006 Posts: 61 Location: San Diego
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
rakesh17684
New User
Joined: 08 Oct 2006 Posts: 61 Location: San Diego
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
rakesh17684
New User
Joined: 08 Oct 2006 Posts: 61 Location: San Diego
|
|
|
|
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.
Quote: |
6. the sort would be on date |
yes/ok |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
rakesh17684
New User
Joined: 08 Oct 2006 Posts: 61 Location: San Diego
|
|
|
|
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 |
|
|
|