Outputting missing dates - SQL Server Forums

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

Outputting missing dates

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-24 : 14:40:46

I would like to select from a database those stores who did not have any activity(no one did not shop) for 5 days or more in the last 6 months. e.g KFC didn't have any customers Monday,Tuesday, Wednesday,Thursday and Friday.

So the below code is for those customers who did not shop in the last 6 mths (Dec 2008 - 31st May 2009), for 5 days or more. I also want to show the days that were missing. The database that I am using do not store the days that no activity happened, so there would be a gap in the data eg KFC had activity on the 1st June but the next transaction happened on the 15th June.

I would like the output from the query to show the follow
KFC 5-06-2009 15-06-2009

So the below information would make it on the list because they had 5 or more consecutive days of no shopping(activity) and the period that the store did not have activity is displayed.

Any Ideas


select distinct s.storeid,transactiondate
from Stores s
inner join (
	select distinct storeid,transactiondate
		,(select top 1 transactiondate 
		  from transaction 
		  where storeid=t.storeid and transactiondate < t.transactiondate 
			and (transactiondate >= '20081201' and transactiondate < '20090601')
		  order by transactiondate desc) as prevdate
	from transaction t
	where (transactiondate >= '20081201' and transactiondate < '20090601')
      )r on r.subsidiaryid=s.subsidiaryid
where (transactiondate >= '20081201' and transactiondate < '20090601')
      and r.transactiondate >= dateadd(dd,datediff(dd,0,prevdate)+5,0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:45:57

do you have a calendar table created on db?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources