April 6, 2012 at 6:13 pm
I need to find the distinct records having both the minimum & maximum date (lastD) for each MName, but not sure how to write query to do so. An example of a few records are below. The records I need from the query are the bolded 2. Thanks in advance for any help and Happy Easter:
idD lastD lastS eDt MName50412 2011-05-12 00:00:00 12 2012-03-30 00:00:00 Jon
50412 2011-05-17 00:00:00 80 2012-03-30 00:00:00 Jon
50412 2012-01-11 00:00:00 80 2012-03-30 00:00:00 Jon
50412 2011-04-16 00:00:00 30 2012-03-30 00:00:00 Jon
April 6, 2012 at 6:24 pm
This?
; WITH CTE AS(
SELECT * , RN1 = ROW_NUMBER() OVER (ORDER BY lastD DESC)
, RN2 = ROW_NUMBER() OVER (ORDER BY lastD )
FROM
(
SELECT 50412 ,'2011-05-12 00:00:00' ,12 ,'2012-03-30 00:00:00' ,'Jon'
UNION ALL SELECT 50412 ,'2011-05-17 00:00:00' ,80 ,'2012-03-30 00:00:00' ,'Jon'
UNION ALL SELECT 50412 ,'2012-01-11 00:00:00' ,80 ,'2012-03-30 00:00:00' ,'Jon'
UNION ALL SELECT 50412 ,'2011-04-16 00:00:00' ,30 ,'2012-03-30 00:00:00' ,'Jon'
) Temp ( idD, lastD, lastS, eDt, MName)
)
SELECT *
FROM CTE
WHERE RN1 =1 OR RN2 = 1
April 7, 2012 at 6:25 am
Thank you so much for your reply. I can't test your query now, but I don't think it will work because lastD is not unique? idD is not unique either. There is another id field I need to pull, which is the NameId, but I did not add it to the records above in my first post 🙁 I thought about using over() to uniquely identify these records, but it's not one field that makes them unique. What makes these records unique, is the combination of NameId, idD (this is a type id for each NameId), lastD (and lastS). Could I maybe take what your suggesting a bit further and somehow combine the fields to make the over unique in some way? Maybe something like RN1 = ROW NUMBER() OVER(PARTITION BY idD AND NameId ORDER BY lastD DESC), RN2 = ROW NUMBER() OVER(PARTITION BY idD AND NameId ORDER BY lastD)?
April 7, 2012 at 9:17 am
abecker 5532 (4/7/2012)
Thank you so much for your reply. I can't test your query now, but I don't think it will work because lastD is not unique? idD is not unique either. There is another id field I need to pull, which is the NameId, but I did not add it to the records above in my first post 🙁 I thought about using over() to uniquely identify these records, but it's not one field that makes them unique. What makes these records unique, is the combination of NameId, idD (this is a type id for each NameId), lastD (and lastS). Could I maybe take what your suggesting a bit further and somehow combine the fields to make the over unique in some way? Maybe something like RN1 = ROW NUMBER() OVER(PARTITION BY idD AND NameId ORDER BY lastD DESC), RN2 = ROW NUMBER() OVER(PARTITION BY idD AND NameId ORDER BY lastD)?
if you post your complete DDL (CREATE TABLE statements...) and some sample data with more than one id we can get the row_number tested. and your row number suggestions are good we just can test them with out more data.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 7, 2012 at 3:39 pm
Apologies for not posting more thoroughly! The db is third party and I'm not looking at it, but I believe this is very close. The bolded/colored records are an example of those I would need. Again, thanks in advance for all your help:
Fields:
NameId varchar(50),
idD int,
lastD smalldatetime,
lastS int,
eDt smalldatetime,
MName varchar(75)
920-125-1212 50412 2011-05-12 00:00:00 12 2012-03-30 00:00:00 Jon
920-125-1212 50100 2012-02-19 00:00:00 30 2012-03-30 00:00:00 Jon
920-125-1212 50412 2011-05-17 00:00:00 80 2012-03-30 00:00:00 Jon
920-125-1212 50100 2011-03-30 00:00:00 90 2012-03-30 00:00:00 Jon
920-125-1212 50100 2011-05-17 00:00:00 90 2012-03-30 00:00:00 Jon
920-125-1212 50412 2012-01-11 00:00:00 80 2012-03-30 00:00:00 Jon
920-125-1212 50412 2011-04-16 00:00:00 30 2012-03-30 00:00:00 Jon
931-127-1578 50100 2011-04-01 00:00:00 30 2012-03-30 00:00:00 Mary
931-127-1578 50133 2011-03-01 00:00:00 30 2012-03-30 00:00:00 Mary
931-127-1578 50100 2012-02-19 00:00:00 15 2012-03-30 00:00:00 Mary
931-127-1578 50133 2011-08-01 00:00:00 60 2012-03-30 00:00:00 Mary
931-127-1578 50133 2012-01-23 00:00:00 90 2012-03-30 00:00:00 Mary
April 9, 2012 at 1:07 am
I still don't clearly understand what you mean by
"I need to find the distinct records having both the minimum & maximum date (lastD) for each MName"
From your sample data, max(lastd) = 2012-02-19 00:00:00, min(lastd) = 2011-03-01 00:00:00 and both max & min for edt is 2012-03-30 00:00:00.
When you say "distinct records having both the minimum & maximum date (lastD)", do you mean that the condition which is used for comparison is min and max of lastd? The sample data has two date fields. Which one do you want to compare with the min and max values?
Please be a little more clear.
Vinu Vijayan
April 9, 2012 at 6:13 am
My description was not clear: "I need to find the distinct records having both the minimum & maximum date (lastD) for each MName" - Apologies again.
I need to find the records having both the min & max date (lastD NOT eDt) for each idD (type) AND MName. ...Each MName can have multiple idD for which min and max lastD has to be found.
April 9, 2012 at 6:47 am
I hope this is what you're looking for.
This will give you result for all MNames:
Select idD, MName, MIN(lastD) as MinDate, MAX(lastD) as MaxDate From Ex
Group By MName, idD
Order By MName
If you want records only for Jon, then this is the code:
Select idD, MName, MIN(lastD) as MinDate, MAX(lastD) as MaxDate From Ex
Group By MName, idD
Order By MName Having MName IN('JON')
If you want records only for Jon, then this is the code:
Select idD, MName, MIN(lastD) as MinDate, MAX(lastD) as MaxDate From Ex
Group By MName, idD
Order By MName Having MName IN('Mary')
Vinu Vijayan
April 9, 2012 at 8:12 am
Thanks for your reply - I used what you suggested, then found the other fields I was looking for (especially needed lastS) based off this sub query. Looks like I was trying to group too much initially! I appeciate everyone's time & assistance. God Bless
April 9, 2012 at 10:25 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply