Report showing gaps in date per dealer

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

SQL Server 2000 Forums

Transact-SQL (2000)

Report showing gaps in date per dealer

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-07 : 05:36:08

Elena writes "Hi all,

Have a nasty little problem here. I need to generate a report that shows where our missing feeds are. There are 163 dealers and each sends us a daily feed. The report should tell us the dealer number and the dates where there is a gap(except Sunday and perhaps a couple of holidays). I'm not quite sure which way is going to be the most efficient and hoped someone here can help.

End Result?

Dealer #     Missing Feed Date
170777           05/16/2005
18033            05/16/2005
1273             04/22/2005

Obviously the below is returning all of the dates it seems, but I'm still working on it. Any help would be greatly appreciated.

SELECT distinct a.dateclosed
FROM gap_analysis a
LEFT JOIN gap_analysis b ON b.dateclosed = a.dateclosed + 1
where a.dealerstd in (select c.dealernum from DLR_PARTICIPANT c
where a.rectype <> 'D'
and a.dateclosed >= '2005-04-01'
and a.dateclosed <= '2005-06-01')
and DATEPART(dw,a.dateclosed )not IN (1)
order by a.dateclosed

Thanks in advance,

Elena Boughey"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-07 : 05:37:21

You might want to take a look here:

http://www.sqlteam.com/item.asp?ItemID=3332

Also read the Comments section at the bottom of the page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources