May 3, 2012 at 5:37 am
Hi, I am fairly new to SQL scripting and everything has been self taught so far so please excuse if what I am asking is basic.
I have a table with columns as follows (only some columns shown)
PolicyTransactionID, Policy Number, SessionID, PolicyStatus
Example Data could be:
PolicyTransactionID PolicyNumber SessionID PolicyStatus
1 1234 1 10
2 1234 5 10
3 1234 9 10
4 1234 10 3
5 5678 89 10
I want to display all the records where the policy status is 10 but only on the last sessionid. I have been doing it step by step, I started with getting the MAX(sessionid) to display policy number eg.
select fpt.policynumber, max(fpt.sessionidentifier)
from factpolicytransaction fpt
where fpt.policynumber = '1234'
group by fpt.policynumber
This works fine, and gives me 1234, 10. but I need to show the PolicyTransactionID field too, but when I add that field into the select statement then I get all the records (assuming that is because the policytransactionid field is different and SQL says I have to Group it if I'm selecting it)
select fpt.policynumber, max(fpt.sessionidentifier), fpt.policytransactionid
from factpolicytransaction fpt
where fpt.policynumber = '1234'
group by fpt.policynumber, fpt.policytransaction
Then I need to only display the records which have a PolicyStatus of 10
So at the end of it, I only want to see the following record and I don't want to put in a policy number, that was only used for my testing, so it would look at all policies, get the latest sessionid row, look at policystatus and if 10 then include that record
PolicyTransactionID PolicyNumber SessionID PolicyStatus
5 5678 89 10
I have struggled for a couple of days and messed around trying to learn and figure it out by myself, but I don't have much hair left so thought it was about time I asked for help.
I hope I have explained it well enough.
Thanks in anticipation
May 3, 2012 at 5:46 am
something like this?
declare @table table (policytransactionid int, policynumber int, sessionid int, policystatus int)
insert into @table VALUES (1,1234,1,10), (2,1234,5,10),(3,1234,9,10),(4,1234,10,3),(5,5678,89,10)
;with cte as
(
select
ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY PolicyTransactionID DESC) AS RowNum,
policytransactionid,
policynumber,
sessionid,
policystatus
from
@table
where
policystatus = 10
)
select * from cte where RowNum = 1
May 3, 2012 at 5:51 am
Beaten to it.
I think I had a similar problem yesterday, I'm still working on it now as a matter of fact.
Try something along these lines, you may need to play about with it but it's there or there abouts I think:
select
fpt.policynumber,
max(fpt.sessionidentifier),
policytransactionID
from
(SELECT
A.PolicyNumber,
A.sessionidentifier,
A.policytransactionid, RANK() OVER(PARTITION BY PolicyNumber ORDER BY sessionidentifier DESC) AS RowNum
FROMfpt
WHEREfpt.policynumber = '1234'
GROUP BY
fpt.policynumber,
policytransactionID
) AS A
WHERE RowNum = 1
May 3, 2012 at 6:25 am
Hi Anthony
Thanks for the code but it is still not giving what I need 🙁
Running your code, it is still returning a record for policynumber 1234, but the latest sessionid for that record is number 10 (not 9) and has a policystatus of 3 which therefore shouldn't be returned.
RowNumpolicytransactionidpolicynumbersessionidpolicystatus
131234910
1556788910
Thanks
Julie
May 3, 2012 at 6:34 am
do you only ever want the row for the largest sessionid and its corresponding transactionid where policystatus = 10 irrespective of the policy number?
or do you want the largest sessionid corresponding transactionid where policystatus = 10 but is dependent on the policy number.
if you only want the row based on the biggest sessionid you want this
declare @table table (policytransactionid int, policynumber int, sessionid int, policystatus int)
insert into @table VALUES (1,1234,1,10), (2,1234,5,10),(3,1234,9,10),(4,1234,10,3),(5,5678,89,10)
;with cte as
(
select
ROW_NUMBER() OVER (ORDER BY SessionID DESC) AS RowNum,
policytransactionid,
policynumber,
sessionid,
policystatus
from
@table
where
policystatus = 10
)
select * from cte where RowNum = 1
May 3, 2012 at 6:37 am
Just some small changes to Anthony's code.
You probably need this
declare @table table (policytransactionid int, policynumber int, sessionid int, policystatus int)
insert into @table VALUES (1,1234,1,10), (2,1234,5,10),(3,1234,9,10),(4,1234,10,3),(5,5678,89,10)
;with cte as
(
select
ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY sessionid DESC) AS RowNum,
policytransactionid,
policynumber,
sessionid,
policystatus
from
@table
)
select * from cte where RowNum = 1 and policystatus = 10
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 3, 2012 at 6:49 am
Thank you so much everyone - the little tweak that Kingston did works like a charm.
Maybe I won't take so long to ask for help next time - just need to get my head around what the code does now and learn from it.
Very much appreciated everyone
Julie 😀
May 3, 2012 at 6:52 am
glad its working for you.
also please take a look at this url http://www.sqlservercentral.com/articles/Best+Practices/61537/ it will be a good starting point on how to post the information that we need to create a mock environment to help us diagnose your issue quicker, extreamly useful as it helps us get you the answer that bit quicker.
May 3, 2012 at 6:59 am
Just read it - thanks for this and will do so in the future - sorry! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply