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|
-- Modified By: Lowell Izaguirre
-- Modified Date: |CurrentDate|
declare @StartDt DATE
declare @EndDt DATE
SET @StartDt = '2/1/2014'
SET @EndDt = '2/28/2014'
SELECT DISTINCT T1.YOURCUSTOMERID
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 )
--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!