December 13, 2011 at 2:31 am
Hi,
This is my query which basically adds a column to a temp table I have created.
Select it.*
,(Select --Find the latest ITC Score before the incident took place
ITCScore
from Staging.ITCChecks ri
where ri.NameID = it.NameID
and ITCScoreDate = (select max(ri.ITCScoreDate)
from Staging.ITCChecks ri
where ri.NameID = it.NameID
and ri.ITCScoreDate <= it.IncidentDate
)
) as ITCScore
INTO #InitialTable_18
From #InitialTable_17 it
Select * from #InitialTable_18
Drop Table #InitialTable_18
Now basically in Staging.ITCChecks there are multiple rows per NameID which each have an ITCScore and ITCScoreDate.
The Subquery above chooses the maximum ITCScoreDate prior to IncidentDate and returns the ITCScore.
In some instances the ITCScore returned is NULL because it hasn't been updated on the table. If this is the case then I need a condition that will refer back to the PREVIOUS ITCScoreDate and then Select that ITCScore. Note if there is no previous ITCScore date ie there is only ONE row for this NameID then it must just return the null value.
Not sure if this can be done but any help would be appreciated.
Thanks a million!
December 13, 2011 at 2:42 am
First of all, hello and welcome to SSC!
Secondly, the good news: This can be done!! Not only that, but we can optimise your current code 😀
Now the bad news: Unfortunately, it seems that your readily consumable sample data and DDL scripts have fallen off your post. Or perhaps you were unaware of the benefits of providing them? When you have time, please read this article[/url] about the best way to provide us with the necessary scripts to allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks.
December 13, 2011 at 3:38 am
Hi Thanks for the feedback. I have created some sample data. I hope it is acceptable. If not please inform me, thanks.
DROP TABLE #ITCChecks
CREATE TABLE #ITCChecks (ITCScoreDate date, NameID INT, ITCScore INT)
INSERT INTO #ITCChecks VALUES ('2008-05-09',27279,null)
INSERT INTO #ITCChecks VALUES ('2008-04-09',27279,8)
INSERT INTO #ITCChecks VALUES ('2009-06-02',28265,6)
INSERT INTO #ITCChecks VALUES ('2010-11-02',31255,null)
INSERT INTO #ITCChecks VALUES ('2011-05-02',54165,3)
INSERT INTO #ITCChecks VALUES ('2009-03-22',54165,6)
select * from #ITCChecks
DROP TABLE #InitialTable_18
CREATE TABLE #InitialTable_18 (IncidentDate date, NameID INT)
INSERT INTO #InitialTable_18 VALUES ('2008-11-21',27279)
INSERT INTO #InitialTable_18 VALUES ('2010-07-09',28265)
INSERT INTO #InitialTable_18 VALUES ('2011-01-01',31255)
INSERT INTO #InitialTable_18 VALUES ('2011-11-01',54165)
select * from #InitialTable_18
DROP TABLE #Result
CREATE TABLE #Result (IncidentDate date, NameID INT,ITCScoreDate INT )
INSERT INTO #Result VALUES ('2008-11-21',27279,8)
INSERT INTO #Result VALUES ('2010-07-09',28265,6)
INSERT INTO #Result VALUES ('2011-01-01',31255,null)
INSERT INTO #Result VALUES ('2011-11-01',54165,3)
select * from #Result
December 13, 2011 at 4:03 am
Looks good.
How's this?
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks)
SELECT MAX(a.ITCScoreDate) AS ITCScoreDate, a.NameID, MAX(a.ITCScore) AS ITCScore
INTO #InitialTable_18
FROM CTE a
INNER JOIN CTE b ON a.NameID = b.NameID AND a.rn >= b.rn
GROUP BY a.NameID
Here's the whole code including the sample data -
BEGIN TRAN
CREATE TABLE #ITCChecks (ITCScoreDate date, NameID INT, ITCScore INT)
INSERT INTO #ITCChecks VALUES ('2008-05-09',27279,null)
INSERT INTO #ITCChecks VALUES ('2008-04-09',27279,8)
INSERT INTO #ITCChecks VALUES ('2009-06-02',28265,6)
INSERT INTO #ITCChecks VALUES ('2010-11-02',31255,null)
INSERT INTO #ITCChecks VALUES ('2011-05-02',54165,3)
INSERT INTO #ITCChecks VALUES ('2009-03-22',54165,6)
CREATE TABLE #InitialTable_17 (IncidentDate date, NameID INT)
INSERT INTO #InitialTable_17 VALUES ('2008-11-21',27279)
INSERT INTO #InitialTable_17 VALUES ('2010-07-09',28265)
INSERT INTO #InitialTable_17 VALUES ('2011-01-01',31255)
INSERT INTO #InitialTable_17 VALUES ('2011-11-01',54165)
CREATE TABLE #Result (IncidentDate date, NameID INT,ITCScoreDate INT )
INSERT INTO #Result VALUES ('2008-11-21',27279,8)
INSERT INTO #Result VALUES ('2010-07-09',28265,6)
INSERT INTO #Result VALUES ('2011-01-01',31255,null)
INSERT INTO #Result VALUES ('2011-11-01',54165,3)
select * from #Result
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks)
SELECT MAX(a.ITCScoreDate) AS ITCScoreDate, a.NameID, MAX(a.ITCScore) AS ITCScore
INTO #InitialTable_18
FROM CTE a
INNER JOIN CTE b ON a.NameID = b.NameID AND a.rn >= b.rn
GROUP BY a.NameID
SELECT * FROM #InitialTable_18
ROLLBACK
December 13, 2011 at 7:24 am
Thank you very much for your prompt response. I do however have a few questions. Is there a way to maintain all the columns in #InitialTable_18? There are over 40 columns in this table that need to be included in your InitialTable_18 output. I tried doing a join to the CTE but to no avail and I'm not sure if that would in fact be the best method.
Also could you please explain your code. I keen to learn in order to improve my skills:
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks)
I've never used a CTE before but what I'm not sure about is the ROW_NUMBER() part?
Also you select the Max ITCScoreDate and ITCScore
SELECT MAX(a.ITCScoreDate) AS ITCScoreDate, a.NameID, MAX(a.ITCScore) AS ITCScore
INTO #InitialTable_18
FROM CTE a
Why do you select the max ITCScore?
Finally on the join there is the condition AND a.rn >= b.rn, why was this included?
In my initial code I had a condition: ri.ITCScoreDate <= it.IncidentDate. Incident date is in #InitialTable_18 sample data i provided but not in your final result.
Sorry for all these questions which may be obvious but I'm relatively new to SQL
December 13, 2011 at 9:02 am
mic.con87 (12/13/2011)
Thank you very much for your prompt response. I do however have a few questions. Is there a way to maintain all the columns in #InitialTable_18? There are over 40 columns in this table that need to be included in your InitialTable_18 output. I tried doing a join to the CTE but to no avail and I'm not sure if that would in fact be the best method.
I'm guessing, because you haven't supplied DDL or mentioned what the relationship between the two tables is -
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks)
SELECT it.*, ITCScore
INTO #InitialTable_18
FROM #InitialTable_17 it
INNER JOIN (SELECT MAX(a.ITCScoreDate) AS ITCScoreDate, a.NameID, MAX(a.ITCScore) AS ITCScore
FROM CTE a
INNER JOIN CTE b ON a.NameID = b.NameID AND a.rn >= b.rn
GROUP BY a.NameID) cteJoin ON it.NameID = cteJoin.NameID
mic.con87 (12/13/2011)
Also could you please explain your code. I keen to learn in order to improve my skills:I've never used a CTE before but what I'm not sure about is the ROW_NUMBER() part?
A CTE is essentially the same as a subquery. I could've written it as I have below and it would've been functionally the same but the CTE is easier to read IMO.
SELECT MAX(a.ITCScoreDate) AS ITCScoreDate, a.NameID, MAX(a.ITCScore) AS ITCScore
FROM (SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks) a
INNER JOIN (SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks) b ON a.NameID = b.NameID AND a.rn >= b.rn
GROUP BY a.NameID
ROW_NUMBER is used to number your data based on the "ITCScoreDate", but have partitioned it by NameID. So for every unique NameID, we start at 1 for the latest date. This is used by a later part of the code.
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY ITCScoreDate DESC) AS rn
FROM #ITCChecks
mic.con87 (12/13/2011)
Also you select the Max ITCScoreDate and ITCScoreWhy do you select the max ITCScore?
Irritatingly, it's a bug which I'll fix at the end of this post.
mic.con87 (12/13/2011)
Finally on the join there is the condition AND a.rn >= b.rn, why was this included?In my initial code I had a condition: ri.ITCScoreDate <= it.IncidentDate. Incident date is in #InitialTable_18 sample data i provided but not in your final result.
Your join and mine are doing the same thing, but I don't need the separate table (assuming I understood your problem correctly).
OK, now to actually correct my code.
It doesn't need a CTE, although you could put it in one.
--Subquery version
SELECT ITCScoreDate, NameID, ITCScore
FROM (SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY CASE WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE() END DESC) AS rn
FROM #ITCChecks) workQuery
WHERE rn = 1
--CTE Version
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY CASE WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE() END DESC) AS rn
FROM #ITCChecks)
SELECT ITCScoreDate, NameID, ITCScore
FROM CTE
WHERE rn = 1
Then, my guess for adding in all of your other "columns"
--Subquery version
SELECT it.*, ITCScore
INTO #InitialTable_18
FROM #InitialTable_17 it
INNER JOIN (SELECT ITCScoreDate, NameID, ITCScore
FROM (SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY CASE WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE() END DESC) AS rn
FROM #ITCChecks) workQuery
WHERE rn = 1) subQueryJoin ON it.NameID = subQueryJoin.NameID
--CTE Version
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY CASE WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE() END DESC) AS rn
FROM #ITCChecks)
SELECT it.*, ITCScore
INTO #InitialTable_18
FROM #InitialTable_17 it
INNER JOIN (SELECT ITCScoreDate, NameID, ITCScore
FROM CTE
WHERE rn = 1) cteJoin ON it.NameID = cteJoin.NameID
Hope that clears everything up.
December 14, 2011 at 12:02 am
Thank you very much for your explanations and for providing both solutions. Your query in fact does something that I hadn't initially required but is very useful and I'll be making use of it. It basically looks at the ITCScoreDates that are less than the IncidentDate and if the corresponding ITCScore(s) is NULL then it will look for the next closest ITCScore regardless of whether the ITCScoreDate is GREATER than IncidentDate. I had initially required the query to return NULL if there was no ITCScore present before IncidentDate.
Once again thanks for your assistance!
December 14, 2011 at 1:52 am
mic.con87 (12/14/2011)
Thank you very much for your explanations and for providing both solutions. Your query in fact does something that I hadn't initially required but is very useful and I'll be making use of it. It basically looks at the ITCScoreDates that are less than the IncidentDate and if the corresponding ITCScore(s) is NULL then it will look for the next closest ITCScore regardless of whether the ITCScoreDate is GREATER than IncidentDate. I had initially required the query to return NULL if there was no ITCScore present before IncidentDate.Once again thanks for your assistance!
I thought that was part of your original requirements in your first post 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply