Need help to find missing date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to find missing date

Author

Topic

aditya_kulkarni15
Starting Member

2 Posts

Posted - 2010-09-29 : 09:57:57

Hi,
I need a help to find a missing date for the scenario as explain below.
I have a table which contains all the date coming in selected date range, I have another table which has a oppid and date with some other data. Not for some oppid few dates are missing ( for below example date 27 is missing). Can someone give a solution to select this missing date oppid wise without using any loop.

Scenario is as follows

CREATE TABLE #Temp
(
date datetime,
OppId int,
Data1 varchar(100),
Data2 varchar(100)
)

CREATE TABLE #Date
(
AllDate DATETIME
)

INSERT INTO #Date VALUES ( '2010-09-29')
INSERT INTO #Date VALUES ( '2010-09-28')
INSERT INTO #Date VALUES ( '2010-09-27')
INSERT INTO #Date VALUES ( '2010-09-26')
INSERT INTO #Date VALUES ( '2010-09-25')
INSERT INTO #Date VALUES ( '2010-09-24')
INSERT INTO #Date VALUES ( '2010-09-23')
INSERT INTO #Date VALUES ( '2010-09-22')
INSERT INTO #Date VALUES ( '2010-09-21')
INSERT INTO #Date VALUES ( '2010-09-20')
INSERT INTO #Date VALUES ( '2010-09-19')

INSERT INTO #Temp VALUES ('2010-09-29',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-28',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-27',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-26',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-25',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-24',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-23',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-22',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-21',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-20',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-19',100, 'aditya','Kul')
INSERT INTO #Temp VALUES ('2010-09-29',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-28',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-26',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-25',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-24',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-23',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-22',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-21',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-20',101, 'aditya1','Kul1')
INSERT INTO #Temp VALUES ('2010-09-19',101, 'aditya1','Kul1')

Expect a reply to my Query.

Thank you
Aditya

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-30 : 05:38:03

Try this -


SELECT A.OppID, AllDate 'Missing Date' FROM 
(
SELECT OppID, ALLDate FROM #Date
CROSS APPLY
( SELECT Distinct OppID FROM #Temp )A
) A 
LEFT JOIN #Temp T ON A.OppID = T.OppID AND A.AllDate = T.Date
WHERE T.OppId IS NULL