Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting Unique record from a table Expand / Collapse
Author
Message
Posted Monday, February 10, 2014 2:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
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..

Post #1539958
Posted Monday, February 10, 2014 2:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 3:42 PM
Points: 76, Visits: 196
declare @StartDt DATE
declare @EndDt DATE
SET @StartDt = '2/1/2014'
SET @EndDt = '2/28/2014'

SELECT DISTINCT YOURCUSTOMERID
,YOURCUSTOMERNAME

FROM YOURTABLE
WHERE YOURDATECOLUMN BETWEEN @StartDt AND @EndDt

Post #1539960
Posted Monday, February 10, 2014 2:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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 @EndDt DATE
SET @StartDt = '2/1/2014'
SET @EndDt = '2/28/2014'

SELECT DISTINCT T1.YOURCUSTOMERID
,T1.YOURCUSTOMERNAME

FROM YOURTABLE T1
WHERE T1.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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539963
Posted Monday, February 10, 2014 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
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

Post #1539983
Posted Monday, February 10, 2014 5:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 3:42 PM
Points: 76, Visits: 196
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.
Post #1539989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse