December 7, 2011 at 12:43 am
Hi,
I had a query running in Oracle which I'm now trying to run in SQL Server:
The oracle Query is:
SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < TRUNC (cc.DiscoverDate)
AND rl.LossType < 1000
AND rl.timestamp < TRUNC (cc.DiscoverDate)
AND cc.QuestionClassID IN (20,25)
AND TRUNC (cc.DiscoverDate) - rl.YearOfIncident < 1095
Group by cc.ClaCaseID
First issue I dealt with was the TRUNC (cc.DiscoverDate) and replaced this with DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
Next I tried this code in SQL Server which doesn't work
SELECTMIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType< 1000
AND rl.RecordCreated< DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)) - rl.YearOfIncident < 1095
Group by cc.ClaCaseID
Any Help would be greatly appreciated.
December 7, 2011 at 12:50 am
try below code
SELECT MIN (cc.ClaCaseID),DENSE_RANK() over(ORDER BY rl.YearOfIncident) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType < 1000
AND rl.RecordCreated < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)) - rl.YearOfIncident < 1095
Group by cc.ClaCaseID
December 7, 2011 at 2:20 am
I received a date error so I fixed this error, it was the subtracting of two dates.
I then received the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'Staging.Losses.YearOfIncident' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So added rl.YearOfIncident to the group by clause. However this made me realize that your fix is not what I require.
The reason I used the dense rank was because there where multiple ClaCaseID in the Staging.Losses table and I only needed to return the latest one once the YearOfIncident had been ranked according to the criteria.
This query was joined to another table which had unique ClaCaseID and then a NVL2 was performed ie ISNULL function was performed returning 'Y' if there was a value and 'N' if there was not.
So seem like I'm back to the drawing board....
SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType< 1000
AND rl.RecordCreated< DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND datediff(day, DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)),rl.YearOfIncident) < 1095
Group by cc.ClaCaseID
December 7, 2011 at 2:33 am
mic.con87 (12/7/2011)
So seem like I'm back to the drawing board....
SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType< 1000
AND rl.RecordCreated< DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND datediff(day, DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)),rl.YearOfIncident) < 1095
Group by cc.ClaCaseID
Try this:
SELECT MIN (ClaCaseID)
FROM (
SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC) AS latest_cla_case_no
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType< 1000
AND rl.RecordCreated< DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND datediff(day, DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)),rl.YearOfIncident) < 1095
Group by cc.ClaCaseID) tmp
WHERE RID = 1
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2011 at 3:03 am
I tried your code and still no luck:
Error message I receive is:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ClaCaseID'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'
December 7, 2011 at 3:23 am
mic.con87 (12/7/2011)
I tried your code and still no luck:Error message I receive is:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ClaCaseID'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'
I went a bit too fast.
SELECT MIN (ClaCaseID)
FROM (
SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC) FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType < 1000
AND rl.RecordCreated < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND datediff(day, DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)),rl.YearOfIncident) < 1095) tmp
WHERE RID = 1
Although you should be able to debug code like that yourself. The AS error was easy to find, I accidentally assigned two aliased to the DENSE_RANK.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2011 at 3:34 am
The code works but it doesn't return the individual ClaCaseID's, rather a single value. I tried to add a group by clause but that didn't help, neither did adding a partition by clause before the order by clause. Any suggestions?
December 7, 2011 at 3:36 am
For the date truncation, I prefer one of these two conversions:
declare @date datetime = getdate() ;
select @date as [ThisIsNow]
, convert(date, @date) [asDate]
, convert(datetime, (convert(date, @date))) as [asDateTime] ;
I'm not familiar with Oracle, but is this what you're looking for?
select min(cc.ClaCaseID) over (order by rl.YearOfIncident ) as latest_cla_case_no
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 7, 2011 at 3:49 am
mic.con87 (12/7/2011)
The code works but it doesn't return the individual ClaCaseID's, rather a single value. I tried to add a group by clause but that didn't help, neither did adding a partition by clause before the order by clause. Any suggestions?
Ah dammit. I thought you were using KEEP FIRST, that's why I added the WHERE RID = 1 clause so that you get only one result. If you delete that WHERE clause and add the original GROUP BY in the inner query, you should get your result.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2011 at 5:04 am
I'm sorry to be such a pain, I tried this:
SELECT MIN (ClaCaseID)
FROM (
SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC)
FROM Staging.ClaCases cc
,Staging.Losses rl
WHERE cc.NameID = rl.NameID
AND rl.YearOfIncident < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND rl.LossType < 1000
AND rl.RecordCreated < DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))
AND cc.QuestionClassID IN (20,25)
AND datediff(day, DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)),rl.YearOfIncident) < 1095
Group by cc.ClaCaseID, rl.YearOfIncident) tmp
and it still returns a single result, in fact the first ClaCaseID that is listed in the table. I've never come across the RID, maybe that has something to do with it? I did try take this out but then the tmp alias is invalid.
December 7, 2011 at 5:36 am
Ah yes, you are still using the MIN aggregate, so you will of course get only one result.
I should drink more coffee.
I just noticed you are using in fact "MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident)" in your Oracle query.
Shouldn't that return only one result, as LAST specifies that you only want the last item of the sequence?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2011 at 6:38 am
I'm not sure what the expected result is, maybe you can provide an example?
In any case, following your remarks, I think that, instead of the order by, a partition by is needed in my example. I've also added a row_number() in case you need that:
select rl.YearOfIncident
, min(cc.ClaCaseID) over (partition by rl.YearOfIncident) as latest_cla_case_no
, row_number() over (partition by rl.YearOfIncident, order by cc.ClaCaseID) as [row_no]
from Staging.ClaCases cc
, Staging.Losses rl
where cc.NameID = rl.NameID
and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and rl.LossType < 1000
and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and cc.QuestionClassID in (20, 25)
and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095
Does that help?
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 7, 2011 at 7:46 am
Basically the LAST looks for the latest YearOfIncident once it has been ranked and keeps that record. In Oracle you need to keep the min. The query may look at several ClaCaseID each with different YearOfIncident's. It will then rank them according to YearOfIncident and keep the latest.
For example:
YearOfIncident ClaCaseID
2007-11-01 1665
2007-12-01 1666
2005-02-01 1666
2005-02-01 1667
2007-12-01 1667
Should become:
YearOfIncident ClaCaseID
2007-11-01 1665
2007-12-01 1666
2007-12-01 1667
I actually received help on an oracle forum for this query.
Here is the original link, maybe that will halep you to understand my problem and perhaps even suggest a better method.
https://forums.oracle.com/forums/thread.jspa?messageID=9954205�
December 7, 2011 at 7:57 am
Your example seems to be rather simple and can be solved like this:
select max(rl.YearOfIncident) as YearOfIncident
, cc.ClaCaseID
from Staging.ClaCases cc
, Staging.Losses rl
where cc.NameID = rl.NameID
and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and rl.LossType < 1000
and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and cc.QuestionClassID in (20, 25)
and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095
group by cc.ClaCaseID
order by cc.ClaCaseID
Or am I missing something?
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 8, 2011 at 1:03 am
Thank-you for your responses everyone. The last post made me realize that perhaps I am over complicating this. I am still curious to see how my original question could be made to work in SQL Server. I'm not sure if it is the optimal way to perform what I require. If you look at my original post on an oracle forum https://forums.oracle.com/forums/thread.jspa?messageID=9954205�
You will see that I pretty much used the solution that Erwin Dockx suggested. I had performance issues when joining onto another table and that is why I was seeking a better method.
select z.ClaCaseID from (
select max(rl.YearOfIncident) as YearOfIncident
, cc.ClaCaseID
from Staging.ClaCases cc
, Staging.Losses rl
where cc.NameID = rl.NameID
and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and rl.LossType < 1000
and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and cc.QuestionClassID in (20, 25)
and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095
group by cc.ClaCaseID
)z
which ultimately would become
,CASE WHEN (select z.ClaCaseID from (
select max(rl.YearOfIncident) as YearOfIncident
, cc.ClaCaseID
from Staging.ClaCases cc
, Staging.Losses rl
where cc.NameID = rl.NameID
and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and rl.LossType < 1000
and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and cc.QuestionClassID in (20, 25)
and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095
group by cc.ClaCaseID
)z
where z.ClaCaseID = ccx.ClaCaseID)IS NOT NULL THEN 'Y' ELSE 'N')
Thanks again for your contributions!!!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply