Selecting Unique record from a table

Question

Post reply

Selecting Unique record from a table

rajborntodare

Hall of Fame

Points: 3576
More actions
February 10, 2014 at 2:42 pm

#283539

Hi I have a table (Billing Table) having multiple records for
people having one record per person per each month.
how to get a list of the guys having record just for one month (say feb)
and doesn't have any other months.
please give an advise..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DataGuy SSCrazy Points: 2029 More actions · Answer 1

declare @StartDt DATE

declare @EndDtDATE

SET @StartDt = '2/1/2014'

SET @EndDt = '2/28/2014'

SELECT DISTINCT YOURCUSTOMERID

,YOURCUSTOMERNAME

FROMYOURTABLE

WHEREYOURDATECOLUMN BETWEEN @StartDt AND @EndDt

Lowell SSC Guru Points: 323518 More actions · Answer 2

rajborntodare (2/10/2014)
Hi I have a table (Billing Table) having multiple records for
people having one record per person per each month.
how to get a list of the guys having record just for one month (say feb)
and doesn't have any other months.
please give an advise..

i think you also need to add a test for the other months with NOT EXISTS:

-- Created By: Lowell Izaguirre

-- Create Date: |CurrentDate|

-- Description:

-- Modified By: Lowell Izaguirre

-- Modified Date: |CurrentDate|

-- Description:

declare @StartDt DATE

declare @EndDtDATE

SET @StartDt = '2/1/2014'

SET @EndDt = '2/28/2014'

SELECT DISTINCT T1.YOURCUSTOMERID

,T1.YOURCUSTOMERNAME

FROMYOURTABLE T1

WHERET1.YOURDATECOLUMN BETWEEN @StartDt AND @EndDt

AND NOT EXISTS (SELECT 1

FROM YOURTABLE T2

WHERE T1.YOURCUSTOMERID = T2.YOURCUSTOMERID

AND (T2.YOURDATECOLUMN < @StartDt OR T2.YOURDATECOLUMN > @EndDt )

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajborntodare Hall of Fame Points: 3576 More actions · Answer 3

Appreciate your help

But their is another problem

where there can be also a customer who is having bill in both jan, feb may be all months

but I want them who have only in jan

DataGuy SSCrazy Points: 2029 More actions · Answer 4

Thats where the NOT EXISTS comes into play which Lowell posted. If any of your customers have records outside the range they will be elminated from the results.