sql 2008 data import problem

I am having issues with a daily import routine i have set up.
The idea is that our locations send their sales data back every day to our Head Office. This is dumped in a sql database. I then extract some of this data and import into another sql database which also houses our ERP solution. Our Erp package then processes the data and post sales invoices , stock used etc.
The problem occurs when there is a communication issue with any of the stores and we dont their data for a few days. My sql code just inserts yesterdays sales, which is fine if all the sales are in. In reality I have to run many small insert scripts during the week to insert data from stores that dont poll their data when they should.
I am trying to think of a more practical way to manage this. The first database gets updated automatically when a store sends their data, the second database doesn't. Instead of running my scruipt for yesterdays data, would it be possible to write a script to insert sales for any of the last 7 days if data has not been inserted already. This would allow for data coming in late (within a weelk anyway) Is this even possible?

Thanks for Reading

Noreen

INSERT INTO [Supermacs Live].[dbo].[Supermac_s - Live$Outlet Sales]
           ([Fiscal DateTime]
           ,[Restaurant Code]
           ,[Item Code]
           ,[PLU Code]
           ,[Description]
           ,[Qty]
           ,[Unit Price Inc_ VAT])
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, FiscalDate))), 
           [Maitred].dbo.MDInvoice.RestaurantCode,
           [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
           ISNULL([Maitred].dbo.MDInvoiceDetail.CodePLU,0), 
           [Maitred].dbo.MDInvoiceDetail.Description, 
           SUM([Maitred].dbo.MDInvoiceDetail.Quantity) AS Qty, 
           ISNULL([Maitred].dbo.MDInvoiceDetail.UnitPrice,0)
FROM       [Maitred].dbo.MDInvoice INNER JOIN
                      [Maitred].dbo.MDInvoiceDetail ON [Maitred].dbo.MDInvoice.InvoiceId = [Maitred].dbo.MDInvoiceDetail.InvoiceId
WHERE FiscalDate >= DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP)-1,0)
   AND FiscalDate < DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP) +1,0) AND
      RestaurantCode in (0041,0042,0032,1019,0003,1026,0027,0045,0029,
0022,0005,0031,0012,0036,0039,0016,1057,0025,
0002,0037,0008,0006,0034,0007,1044,0040,0004,1053,1077,1093,1089,1064)         
GROUP BY [Maitred].dbo.MDInvoiceDetail.DescriptionMDID,
[Maitred].dbo.MDInvoice.FiscalDate, 
[Maitred].dbo.MDInvoice.RestaurantCode, 
[Maitred].dbo.MDInvoiceDetail.CodePLU, 
[Maitred].dbo.MDInvoiceDetail.Description, 
[Maitred].dbo.MDInvoiceDetail.UnitPrice

ORDER BY [Maitred].dbo.MDInvoice.RestaurantCode, [Maitred].dbo.MDInvoice.FiscalDate, [Maitred].dbo.MDInvoiceDetail.CodePLU

GO

Select allOpen in new window