February 13, 2009 at 6:32 am
Hi Guys,
First time poster long time reader.
I'm trying to come up with a solution on amalgamating/splitting dates from two tables in SQL. I've been thinking about this for a while and my minds gone blank!
I've attached examples of what i'm trying to accomplish.
If you need more details please let me know (I didn't want to type out an essay).
Many thanks
Craig
February 13, 2009 at 7:23 am
This should be doable by joining table dbo.tblSupplierSeasons with a Tally table or a Calendar table to produce a resultset where each row corresponds to a single day, then joining table dbo.tblOurSeasonMarkUps with a Tally table or a Calendar table to produce another resultset where each row corresponds to a single day, then joining the two derived resultsets on the date column, then grouping this resultset to find the start and end of each date range and calculating the cost * markup, etc.
February 13, 2009 at 6:32 pm
The query below produces a resultset that matches the data in tblExpectedResults provided by the OP. It requires a Tally table containing consecutive integers starting at 0 and containing at least as many records as the maximum difference in days between the SeasonStart and SeasonEnd datetime columns in either of the source tables.
The two CTEs generate resultsets from the source data, where each record represents a single day. The column named CN in each CTE calculates the difference between two ROW_NUMBER() function evaluations in order to assign a unique number to each row of a contiguous sequence of days that have the same set of values in the source tables. Check out the following article for more information about this clever trick:
http://www.sqlmag.com/Article/ArticleID/93606/sql_server_93606.html.
I would not expect there to be any days missing in the date range represented by the source data in the tblOurSeasonMarkUps table compared to the tblSupplierSeasons table. However, if this is the case, then markup data will not be defined for these dates, but due to the LEFT JOIN these dates will still be represented in the final resultset if they are included in a date range specified in the tblSupplierSeasons table data.
The grouping in the final SELECT statement amalgamates the days into date ranges that have contiguous common values. The contiguity of the date ranges is enforced by including the two CN columns in the GROUP BY clause.
;WITH cteSS AS (
SELECT
CN = ROW_NUMBER() OVER (ORDER BY S.SeasonStart, TS.N)
- ROW_NUMBER() OVER (PARTITION BY S.Code, S.Cost ORDER BY S.SeasonStart, TS.N),
[Date] = DATEADD(day, TS.N, S.SeasonStart),
S.Code,
S.Cost
FROM dbo.tblSupplierSeasons S
INNER JOIN dbo.Tally TS
ON (TS.N >= 0 AND TS.N <= DATEDIFF(day, S.SeasonStart, S.SeasonEnd))
),
cteMU AS (
SELECT
CN = ROW_NUMBER() OVER (ORDER BY M.SeasonStart, TM.N)
- ROW_NUMBER() OVER (PARTITION BY M.Markup, M.SeasonDescription ORDER BY M.SeasonStart, TM.N),
[Date] = DATEADD(day, TM.N, M.SeasonStart),
M.SeasonDescription,
M.Markup
FROM dbo.tblOurSeasonMarkUps M
INNER JOIN dbo.Tally TM
ON (TM.N >= 0 AND TM.N <= DATEDIFF(day, M.SeasonStart, M.SeasonEnd))
)
SELECT
[Code] = SS.Code,
[SeasonStart] = MIN(SS.[Date]),
[SeasonEnd] = MAX(SS.[Date]),
[SellingPrice] = SS.Cost * (1.0 + 0.01 * COALESCE(MU.Markup, 0.0)),
[MarkUpApplied] = MU.Markup,
[SeasonDescription] = MU.SeasonDescription
FROM cteSS SS
LEFT OUTER JOIN cteMU MU ON (SS.[Date] = MU.[Date])
GROUP BY SS.Code, SS.Cost * (1.0 + 0.01 * COALESCE(MU.Markup, 0.0)), MU.Markup, MU.SeasonDescription, SS.CN, MU.CN
ORDER BY [Code], [SeasonStart]
February 17, 2009 at 10:59 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply