February 13, 2014 at 10:36 am
Hi,
I'm looking to produce a dataset where we have one row for each dbpatid, the latest dbSchDate and the corresponding dbSchTypeCnt.
The table has multiple rows for dbpatid based on multiple dbSchDate's.
When I use this I get one row for each dbpatid
SELECT DISTINCT dbPatCnt, (SELECT MAX(dbSchDate)) AS dbSchDate
FROM dbo.SchDetail
GROUP BY dbPatCnt
but when I use this, I get multiple rows based on the dbSchTypeCn for every dbSchDate
SELECT DISTINCT dbPatCnt,dbSchTypeCnt,(SELECT MAX(dbSchDate)) AS dbSchDate
FROM dbo.SchDetail
GROUP BY dbPatCnt, dbSchTypeCnt
How do I code his so that it only returns the dbSchTypeCnt for the max(latest) dbSchDate?
thanks,
February 13, 2014 at 10:47 am
;WITH MaxDtSchDetail
AS
(
SELECT DISTINCT dbPatCnt, (SELECT MAX(dbSchDate)) AS MAXdbSchDate
FROM dbo.SchDetail
GROUP BY dbPatCnt
)
SELECT SD.dbPatCnt, SD.dbSchTypeCnt, SD.dbSchDate
FROM MaxDtSchDetail AS MD
JOIN dbo.SchDetail AS SD
ON SD.dbPatCnt = MD.dbPatCnt
AND SD.dbSchDate = MD.MAXdbSchDate
But please note, if you have multiple records with the same maximum dbSchDate for the same dbPatCnt, you're still going to have maltiple records returned for dbPatCnt
February 13, 2014 at 11:01 am
Try this:
select dbPatCnt, dbSchTypeCnt, dbSchDate
from dbo.SchDetail
where dbSchDate = (select MAX(dbSchDate) from SchDetail sd
where sd.dbSchTyhpeCnt = dbSchTypeCnt)
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 13, 2014 at 11:04 am
SELECT DISTINCT dbPatCnt,dbSchTypeCnt,(SELECT MAX(dbSchDate)) AS dbSchDate
FROM dbo.SchDetail
GROUP BY dbPatCnt, dbSchTypeCnt
How do I code his so that it only returns the dbSchTypeCnt for the max(latest) dbSchDate?
thanks,
Assuming you are on SQL Server 2005 or later.
Also assuming dbpatid you mention in your post is dbPatCnt in the query.
Then you can use ROW_NUMBER to achieve this:
;WITH CTESchDetail AS
(
SELECT dbPatCnt, dbSchTypeCnt, dbSchDate,
ROW_NUMBER() OVER (PARTITION BY dbPatCnt ORDER BY dbSchDate DESC) AS rn
FROM dbo.SchDetail
)
SELECT dbPatCnt, dbSchTypeCnt, dbSchDate
FROMCTESchDetail
WHERErn = 1
February 13, 2014 at 1:11 pm
when I run this:
select CustomerNumber, [year], TotalYears
from sfidata.dbo.tblCustomerHistory
where CustomerNumber in (1001, 1002, 1005)
order by CustomerNumber, [YEAR]
I get this:
CustomerNumberyearTotalYears
100119861
100119872
100119943
100219931
100219952
100219973
100519841
100519902
100519913
100520004
100520066
100520076
100520108
100520118 which shows you the data I'm working with.
When I run this:
select ch.CustomerNumber, ch.[Year], ch.TotalYears
from sfidata.dbo.tblCustomerHistory ch
where ch.CustomerNumber in (1001, 1002, 1005)
and ch.[Year] = (select MAX(ch2.[Year]) from sfidata.dbo.tblCustomerHistory ch2
where ch2.customernumber = ch.CustomerNumber)
I get this:
CustomerNumberYearTotalYears
100119943
100219973
100520118
Year is each year a customer has placed an order. So, by comparing the two data results, you can see that the second query pulled the MAX year, i.e. the latest year for customernumber 1001 is 1994.
Sorry about the spacing.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 14, 2014 at 12:21 am
Thanks for you're help guys. YB1D is the sql champion winner here! Eugene was also correct but as he mentioned we'd still get some multiple rows. Car, I've not tried yours as it looked like you were using hard coded values but thanks anyway.
February 14, 2014 at 3:08 am
mattech06 (2/14/2014)
Thanks for you're help guys. YB1D is the sql champion winner here! Eugene was also correct but as he mentioned we'd still get some multiple rows. Car, I've not tried yours as it looked like you were using hard coded values but thanks anyway.
The fact that using ROW_NUMBER() returns you one row, doesn't eleminate the posibilitiy that more than one row with differenet values in type column may exist for the same id with the same max date.
You can easely gurantee one row returned with GROUP BY query by selecting TOP 1 - it will give you exactly the same effect...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply