quote: Originally posted by bandi
SELECT PatientName, ApptNumber, ApptTimestampFROM (SELECT PatientName, ApptNumber, ApptTimestamp ,Row_numbeR() OVER(PARTITION BY PatientName, MONTH(ApptTimestamp) ORDER BY ApptTimestamp) Seq FROM PatientTable )tWHERE t.Seq=1 --Chandu
Just taking month alone will cause an issue if date range falls above an year.see this illustrationdeclare @test table([PatientName] varchar(100), [ApptNumber] int,[ApptTimestamp] datetime)insert @test([PatientName], [ApptNumber],[ApptTimestamp])values ('John Smith',18374832,40913.34375),('John Smith',19837289,40915.6041666667),('John Smith',19982776,40954.375),('John Smith',20091092,40966.65625),('John Smith',20100939,40967.3090277778),('John Smith',20110938,40973.7013888889)insert @test([PatientName], [ApptNumber],[ApptTimestamp])values('John Smith',20129193,'20130101 13:10:24'),('John Smith',20121123,'20130111 16:10:23'),('John Smith',20130056,'20130213 14:25:34')SELECT * FROM @testSELECT 'Bandi''s solution' SELECT PatientName, ApptNumber, ApptTimestampFROM (SELECT PatientName, ApptNumber, ApptTimestamp ,Row_numbeR() OVER(PARTITION BY PatientName, MONTH(ApptTimestamp) ORDER BY ApptTimestamp) Seq FROM @test )tWHERE t.Seq=1SELECT 'VIsakh Solution'SELECT PatientName, ApptNumber, ApptTimestampFROM (SELECT PatientName, ApptNumber, ApptTimestamp ,Row_numbeR() OVER(PARTITION BY PatientName, DATEADD(mm,DATEDIFF(mm,0,ApptTimestamp),0) ORDER BY ApptTimestamp) Seq FROM @test )tWHERE t.Seq=1output----------------------------------------inserted data----------------------------------------PatientName ApptNumber ApptTimestamp---------------------------------------------------------------------------------------------------- ----------- -----------------------John Smith 18374832 2012-01-07 08:15:00.000John Smith 19837289 2012-01-09 14:30:00.000John Smith 19982776 2012-02-17 09:00:00.000John Smith 20091092 2012-02-29 15:45:00.000John Smith 20100939 2012-03-01 07:25:00.000John Smith 20110938 2012-03-07 16:50:00.000John Smith 20129193 2013-01-01 13:10:24.000John Smith 20121123 2013-01-11 16:10:23.000John Smith 20130056 2013-02-13 14:25:34.000----------------Bandi's solution----------------PatientName ApptNumber ApptTimestamp---------------------------------------------------------------------------------------------------- ----------- -----------------------John Smith 18374832 2012-01-07 08:15:00.000John Smith 19982776 2012-02-17 09:00:00.000John Smith 20100939 2012-03-01 07:25:00.000---------------VIsakh Solution---------------PatientName ApptNumber ApptTimestamp---------------------------------------------------------------------------------------------------- ----------- -----------------------John Smith 18374832 2012-01-07 08:15:00.000John Smith 19982776 2012-02-17 09:00:00.000John Smith 20100939 2012-03-01 07:25:00.000John Smith 20129193 2013-01-01 13:10:24.000John Smith 20130056 2013-02-13 14:25:34.000 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |