Solved

sql 2008  data import problem

Posted on 2010-09-15
4
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Priya Perumpilavil earned 500 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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