June 15, 2009 at 6:19 am
I have a table with 2 date columns. cbTransEffectiveDate and cbExpirationDate.
Both of these columns are stored as integer i.e. 20080101
For each row in this table, I need to create 1 row for every month between cbTransEffectiveDate and cbExpirationDate.
For example
Row 1:
cbTransEffectiveDate =20080131
cbTransEffectiveDate =20080501
I need to output 5 rows
20080101
20080201
20080301
20080401
20080501
I am currently joining to a 'Time Dimension' like so.
FROM
T_SM_PREMIUM_ONSET_OFFSET cph INNER JOIN
datawarehouse.dbo.T_DM_Time t ON
substring(cast(cph.cbTransEffectiveDate as char),1,6)+'01' <= t.DM_Time_Id AND
cph.cbExpirationDate >= t.DM_Time_Id
WHERE
t.The_Day = 1 --only want the 1st day of every month in the above date range
My execution plan contains a nested loop taking up 66% of execution time.
From my experience so far SSIS is not so great for comparative joins((>=) or (<=))
Any clever ideas??
I guess I could create 12 columns in my table. 1 for each month.
Update the columns with the months in between my date values and then unpivot...but that kind of sucks.
June 17, 2009 at 3:33 pm
I would assume that applying functions to the "cph.cbTransEffectiveDate" columns is causing the performance issues. Can you describe in more detail what you are trying to do? Are you just trying to group by the month and/or get a sum for the month but display the date as the first of the month?
I'm just guessing, but maybe this will help:SELECT (t.YearNumber * 1000) + (t.MonthNumber * 100) + 1 AS NewDate
FROM
T_SM_PREMIUM_ONSET_OFFSET cph
INNER JOIN
datawarehouse.dbo.T_DM_Time t
ON cph.cbTransEffectiveDate = t.DM_Time_Id
WHERE
--t.The_Day = 1
cph.cbTransEffectiveDate <= cph.cbExpirationDate
GROUP BY
t.YearNumber,
t.MonthNumber
June 18, 2009 at 4:55 am
for each row in the table
I need to take that 1 row and turn it into 1 row for every month between the effective date and expiration date.
June 18, 2009 at 9:39 am
Perhaps some sample data an expected output would help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 18, 2009 at 10:06 am
I guess I wasn't looking for help with performance tuning on the query. I was looking for any advice on a general approach.
Imagine I have a table with only 2 columns
begin_date (int)
end_date (int)
the 1st row looks like the following: format of int is yyyymmdd
20010322, 20010710
I would like to output the following:
20010301, 20010710
20010401, 20010710,
20010501, 20010710,
20010601, 20010710,
20010701, 20010710
One row for every month between my begin and end dates.
I am looking for general theory here. I am capable of tightening up code. I'm just trying to decide if I should be using a different approach.
Currently I am joining to a 'time' table like so:
JOIN
T_DM_Time t ON
substring(cast(begin_date as char),1,6)+'01'
--I am simply converting the value to be the 1st of the month
= DM_Time_Id
TIME_ID contains integers values formatted like yyyymmdd
1 row for each month
20010101
20010201
20010301
...and so on
so you see my join would return the resultset I am looking for here.
I am just curious if anyone knows of a better way to go about this?
Sorry for not being clear on what kind of advice I was looking for.
I can understand your confusion/frustration...and I see why nobody is touching this post.
Hopefully I am more clear now.
June 18, 2009 at 12:53 pm
Yeah, I think the approach is fine. I was just messing around with some junk so I thought I'd share my code (obviously the Date table I am using is a bit strange):DECLARE @Table TABLE (begin_date INT, end_date INT)
INSERT @Table
SELECT 20010322, 20010710
UNION ALL SELECT 20070405, 20070913
-- Fastest
SELECT
TD.DateNumber, T.end_date
FROM
@Table AS T
CROSS APPLY
(
SELECT DateNumber
FROM TimeDim AS TD
WHERE TD.DateNumber T.begin_date
AND TD.DayOfMonth = 1
UNION
SELECT MAX(DateNumber)
FROM TimeDim AS TD
WHERE DateNumber = YEAR(CAST(CAST(T.begin_date AS VARCHAR(8)) AS DATETIME))
AND TD.MonthOfYear >= MONTH(CAST(CAST(T.begin_date AS VARCHAR(8)) AS DATETIME))
AND TD.DateNumber <= T.end_date
AND TD.DayOfMonth = 1
-- Using CROSS APPLY
SELECT
TD.DateNumber, T.end_date
FROM
@Table AS T
CROSS APPLY
(
SELECT DateNumber
FROM TimeDim AS TD
WHERE DateNumber = YEAR(CAST(CAST(T.begin_date AS VARCHAR(8)) AS DATETIME))
AND TD.MonthOfYear >= MONTH(CAST(CAST(T.begin_date AS VARCHAR(8)) AS DATETIME))
AND TD.DayOfMonth = 1
) AS TD
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply