Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql 2008  data import problem

Posted on 2010-09-15
4
Medium Priority
?
229 Views
Last Modified: 2012-05-10
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

Open in new window

0
Comment
Question by:jmauel
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Priya Perumpilavil earned 2000 total points
ID: 33681757
please try this

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
      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

except

select ([Fiscal DateTime]
           ,[Restaurant Code]
           ,[Item Code]
           ,[PLU Code]
           ,[Description]
           ,[Qty]
           ,[Unit Price Inc_ VAT]) from [Supermacs Live].[dbo].[Supermac_s - Live$Outlet Sales]
0
 
LVL 9

Expert Comment

by:valkyrie_nc
ID: 33681778
Is there any way you could set up an SSIS package to do this?  You could use a Slowly Changing Dimension task in your dataflow to insert new and update (or ignore, depending on how you want to do it) existing data.

hth

valkyrie_nc
0
 
LVL 9

Expert Comment

by:valkyrie_nc
ID: 33681788
Although on review, I like PriyaPadman's solution. :)
0
 

Author Comment

by:jmauel
ID: 33683706
I am trying the code above but have removed the ORDER BY line. Hoever I am getting the following error

Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.


Any ideas.

Am i right in understanding it will compare the two databases and hust add whays new??


Thanks

Noreen
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question