SQL Query

dr46014 · Post by **dr46014** » Mon Feb 19, 2007 5:57 pm

Hi all...
I am having a Time_Dim table in my database where there are 3 columns..Fiscal_Date,Year_Week,Year_Month.For each date in fiscal_date field there is Year_Month and Year_Week.I mean for 365 days in a year the Time_Dim is populated with 365 entries.Suppose for fiscal date 20070101 the table has Year_Month as 200701 and Year_Week as 200701

Fiscal_Date Year_Month Yearweek
20070201 200702 200706
20070202 200702 200706
20070203 200702 200706
20070204 200702 200707

like this..
can uplease suggest me a SQL query which would find out the current month's first and rest other week codes..For example the query should return the results as below
Current_Month:200702
First weekcode:200706
Rest week codes:200707,200708,200709,200710
I need this as becoz for the first week of every month i need to make an outer join between two tables TAB1 containing Year_Month and Tab2 containing Year_Week and populate everything into Tab3.So the records which are not present inTab1 has null entry in the fields taken from Tab2.So for the next week means second week onwards we go on inserting or updating Tab3 depending on a key Cust_ID.So i need to have the query so that i can provide the condition there..
If anyone has some alternative approach can suggest me
Regards,
Deepak

ray.wurlod · Post by **ray.wurlod** » Mon Feb 19, 2007 7:08 pm

It would be ugly SQL at best.
Easier to select all the rows into a DataStage job and effect a vertical pivot.
Search the forum for vertical pivot techniques; in this case the approach using stage variables is probably easiest.

kumar_s · Post by **kumar_s** » Mon Feb 19, 2007 7:30 pm

Select Yearweek from TIME_DIM where Year_Month = to_char(SYSDATE,'YYYYMM')
Will give you all the Yearweek for the current month. And finding the min will give the first week of the month.
But Iam not sure, how could you do a outer join based on Year_Month on tab1 and Year_Week in tab2.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 19, 2007 8:21 pm

And the comma-delimited list in the final column?

dr46014 · Post by **dr46014** » Mon Feb 19, 2007 9:44 pm

ok...i got the soultion to some extent..let me try this sql...i am also thinking of using rank so that i could be able to do further processing