Find Gaps in date by dealer

[EBOUGHEY]

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. I'm not quite sure which way is going to be the most efficient and hoped someone here can help.

I've started with the below command to pull the initial data.

select distinct dateclosed
from GAP_ANALYSIS a
where dealerstd in (select dealernum from DLR_PARTICIPANT b
where a.rectype <> 'D'
and a.dateclosed >= '2005-04-01'
and a.dateclosed <= '2005-06-01')
and DATEPART(dw,dateclosed )not IN (1)
order by dateclosed


Thanks for any help. I'm still searching through previous posts as well.

Elena

 

[EBOUGHEY]

I've also created a stored procedure from one of the threads I've seen. Does this make any sense and would it accomplish a portion of my goal?

Create Proc uspMissingdates
@start datetime ='2005-04-01', @end datetime ='2005-06-01'
As

Select @end=cast(cast(@end as datetime)-1 As varchar(8))

Select Missing=cast(a.dateclosed As datetime)+1
From gap_analysis a
Left Join gap_analysis b
On cast(a.dateclosed As datetime)+1=cast(b.dateclosed AS datetime)
where a.dateclosed Between @start And @end
Go

 

[amrita418]

I see some errors in your query but before those are addressed, can you provide the table structure, sample data for both the tables (gap_analysis and dlr_participant) and what other filters you need on gap_analysis table.

Regards,
AA

 

[EBOUGHEY]

The structure for the tables are quite large and the below are the only ones we are working with... I hope this is what you need. I know I have major errors in my code. I am just starting on it and trying to come up with something on a short time scale. Thanks for your assistance.

Gap_Analysis

dealerstd varchar (10)
dateclosed datetime(8)


Dlr_Participant

dealernum varchar(20)


Dealer # Date Closed
10001748 2005-04-20 00:00:00.000
10001824 2005-04-20 00:00:00.000
10001827 2005-04-20 00:00:00.000
10001868 2005-04-20 00:00:00.000
10001748 2005-04-21 00:00:00.000
10001824 2005-04-21 00:00:00.000
10001827 2005-04-21 00:00:00.000
10001828 2005-04-21 00:00:00.000
10001868 2005-04-21 00:00:00.000
10001869 2005-04-21 00:00:00.000
10001939 2005-04-21 00:00:00.000
10002105 2005-04-21 00:00:00.000


I am a little closer with the below code, but obviously it is not pulling out the dates that are missing. Just giving you as much as I can...

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

 

[amrita418]

Here is the basic query that should get you started. You should be adding additional filters to get the final desired output.

insert into #gap_analysis values ('10001748',    '2005-04-20 00:00:00.000')
insert into #gap_analysis values ('10001824',    '2005-04-20 00:00:00.000')
insert into #gap_analysis values ('10001827',   '2005-04-20 00:00:00.000')
insert into #gap_analysis values ('10001868',    '2005-04-20 00:00:00.000')
insert into #gap_analysis values ('10001748',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001824',   '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001827',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001828',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001868',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001869',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10001939',    '2005-04-21 00:00:00.000')
insert into #gap_analysis values ('10002105',    '2005-04-21 00:00:00.000')

insert into #dlr_participant values ('10001748')
insert into #dlr_participant values ('10001824')
insert into #dlr_participant values ('10001827')
insert into #dlr_participant values ('10001868')
insert into #dlr_participant values ('10001828')
insert into #dlr_participant values ('10001869')
insert into #dlr_participant values ('10001939')
insert into #dlr_participant values ('10002105')

select 		o.DealerNum, o.DateClosed 
from		(select 	b.dealernum 'DealerNum', 
				dateclosed 'DateClosed'
		 from 		#dlr_participant b,
				(select 	distinct 
						dateclosed 
		 		 from 		#gap_analysis 
		 		 where 		dateclosed between '2005-04-20' and '2005-04-21') c )o
		left outer join 
		#gap_analysis a
		on (o.DealerNum = a.DealerStd 
      		    and o.DateClosed = a.DateClosed)
where 		a.DealerStd is null

Regards,
AA

 

[EBOUGHEY]

The code you have above still brings up all the records... What I am trying to achieve is a list of dealer numbers and the date that the feed was missed (which consequently would not be in the file since it wasn't pulled).

So if we looked at the sample data I put out here you notice that we received data on the 21st for dealers '10001869', '10001939' and '10002105' but did not receive anything on the 20th.

End Result:

10001869 2005-04-20 00:00:00.000
10001939 2005-04-20 00:00:00.000
10002105 2005-04-20 00:00:00.000

I think there will have to be a temp file created or a way for it to relate back to a missing date.

Thanks for taking so much time on this!

Elena

 

[amrita418]

Please double check the sample data and the query I posted.

The output has the 3 rows you expected and additionally '10001828' dealer because this dealer also doesnot have an entry for 4/20.

Regards,
AA

 

[EBOUGHEY]

I stand corrected and totally embarrassed...

I'm testing it right now on 33 million records. Had to tweak it obviously, but the core code was right there.

Thanks a bunch!

Elena