December 14, 2011 at 2:33 am
Hi folks,
I need some help with a query, here is some testdata
--available stores
CREATE TABLE #stores
(
storenrINT NOT NULL,
storetypeVARCHAR(1) NOT NULL,
PRIMARY KEY (storenr)
)
INSERT INTO #stores
(storenr, storetype)
SELECT1,'A'
UNION
SELECT2,'B'
CREATE TABLE #visits
(
visitIDINT NOT NULL,
storenrINT NOT NULL,
visitdateSMALLDATETIME NOT NULL,
PRIMARYKEY (visitID)
)
INSERT INTO #visits
(visitID,storenr, visitdate)
SELECT1,1,'20111201'
UNION
SELECT2,1,'20111205'
UNION
SELECT3,1,'20111208'
UNION
SELECT4,2,'20111209'
--table is filled by a trigger on the stores table
--to track the changes
CREATE TABLE #storechanges
(
storenrINT NOT NULL,
changedateSMALLDATETIME NOT NULL,
prev_storetypeVARCHAR(1) NOT NULL,
new_storetypeVARCHAR(1) NOT NULL
PRIMARY KEY (storenr,changedate)
)
INSERT INTO #storechanges
(
storenr,
changedate,
prev_storetype,
new_storetype
)
SELECT1,'20111204','A','B'
UNION
SELECT1,'20111207','B','A'
DROP TABLE #storechanges
DROP TABLE #visits
DROP TABLE #stores
What I need is a query which maps a visit to the storetype which was assigned to the store at the specific date.
So I expect the following result:
visitID storenr storetype
1 1 'A' --visit was before the first change to this store
2 1 'B' --lies between the two changes
3 1 'A' --lies after the last change, reflects current status in the storetable
4 2 'B' --store was not changed until now
I tried to split it into parts and UNION it all together, what worked for visits in unchanged stores and visits before/after the first/last change. But I have issues with the ones that are between.
I hope you can help me.
Thanks in advance,
Steffen
December 14, 2011 at 5:04 am
If I understand your requirement correctly:
SELECT
V.visitID, V.storenr, COALESCE(SC.prev_storetype, S.storetype) storetype
FROM
#visits V
JOIN
#stores S ON S.storenr = V.storenr
LEFT JOIN
#storechanges SC ON SC.storenr = S.storenr
AND SC.changedate = (SELECT MIN(changedate) FROM #storechanges WHERE changedate > V.visitdate)
December 14, 2011 at 5:11 am
Or better:
SELECT
V.visitID, V.storenr, COALESCE(OA.prev_storetype, S.storetype) storetype
FROM
#visits V
JOIN
#stores S ON S.storenr = V.storenr
OUTER APPLY
(
SELECT TOP 1
storenr, changedate, prev_storetype
FROM
#storechanges SC
WHERE
SC.storenr = S.storenr AND SC.changedate > V.visitdate
ORDER BY
SC.changedate
) OA
December 14, 2011 at 11:20 am
Hi Peter.
I'm on vacation the next two days but I'll try it on monday.
Your solution looks great and simple and I ask myself why this didn't come to my mind. 🙂
What is the benefit of the OUTER APPLY? To eliminate the additional SELECT and GROUP BY on the storechanges?
In your first post a criteria is missing I believe, it should read:
(SELECT MIN(changedate) FROM #storechanges WHERE changedate > V.visitdate AND storenr = V.storenr)
Thank you very much.
December 14, 2011 at 12:00 pm
shadow_2 (12/14/2011)
What is the benefit of the OUTER APPLY? To eliminate the additional SELECT and GROUP BY on the storechanges?
Look at the actual execution plans. The first one contains a Clustered Index Scan, a Clustered Index Seek, another Clustered Index Scan (changed into an Clustered Index Seek by your correction below) with an Aggregate and one more Index Seek. The latter one contains only a Clustered Index Scan and two Clustered Index Seeks. Of cause using the queries might produce different plans on real production data, but I'm pretty confident that the OUTER APPLY query will outperform the first query.
In your first post a criteria is missing I believe, it should read:
(SELECT MIN(changedate) FROM #storechanges WHERE changedate > V.visitdate AND storenr = V.storenr)
You are absolutely right.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply