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
SQL Query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: