-2

I set the job schedule on daily basis. When I check the records on the next day, the records were not moved completely on someday. I don't know why.

Here is my query

INSERT INTO [HQWebMatajer].[dbo].[F_ItemDailySalesParent]
(
[ItemID]
  ,[StoreID]
  ,[ItemLookupCode]
  ,[DepartmentID]
  ,[CategoryID]
  ,[SupplierID]
  ,[Time]
  ,[Qty]
  ,[ExtendedPrice]
  ,[ExtendedCost]
)
SELECT 
[ItemID]
  ,[StoreID]
  ,[ItemLookupCode]
  ,[DepartmentID]
  ,[CategoryID]
  ,[SupplierID]
  ,[Time]
  ,[Qty]
  ,[ExtendedPrice]
  ,[ExtendedCost]
FROM 
    [HQMatajer].[dbo].[JC_ItemDailySalesParent]
where time=convert(Date,getdate()-1)

Total records found on [JC_ItemDailySalesParent] = 21027 and total records on [F_ItemDailySalesParent] = 18741 on 06-March-2017

If you think i might have missed some column or something else!.. Then I will execute the same query by changing where condition to where time=convert(Date,getdate()). Then it's executed the complete record without missing.

Note: Both tables are job schedule. [JC_ItemDailySalesParent] table will run at 2 am. F_ItemDailySalesParent will run at early morning 6 o ck.

Don't ask me why two tables with same record. That's for different purpose.

Thanks,

Community
  • 1
  • 1
Liam neesan
  • 2,282
  • 6
  • 33
  • 72
  • 1
    I would get away from using `getdate()-1` and explicitly use `DATEADD()` function. You need to provide some sample data and expected output. Check the `SYSDATETIME()` to see how it compares to your local time. Check to make sure records actually exist for the condition in your `WHERE` clause. This is pretty straight forward. – S3S Mar 23 '17 at 13:04
  • Are you using MySQL or MS SQL Server? Don't tag products not involved. – jarlh Mar 23 '17 at 13:09
  • @scsimon There is no chance to change the system date. because both jobs and tables are located in same server – Liam neesan Mar 23 '17 at 13:09
  • @jarlh done.... – Liam neesan Mar 23 '17 at 13:11

1 Answers1

0

Always try to have a unique identifier in the table. Assuming combination of ItemID, StoreID, ItemLookupCode, DepartmentID, CategoryID, SupplierID, Time are unique:

INSERT INTO HQWebMatajer.dbo.F_ItemDailySalesParent
( ItemID
, StoreID
, ItemLookupCode
, DepartmentID
, CategoryID
, SupplierID
, Time
, Qty
, ExtendedPrice
, ExtendedCost
)
SELECT ItemID
  , StoreID
  , ItemLookupCode
  , DepartmentID
  , CategoryID
  , SupplierID
  , Time
  , Qty
  , ExtendedPrice
  , ExtendedCost
FROM HQMatajer.dbo.JC_ItemDailySalesParent src
LEFT JOIN  HQWebMatajer.dbo.F_ItemDailySalesParent tgt
    ON tgt.ItemID = src.ItemID
    AND tgt.StoreID = src.StoreID
    AND tgt.ItemLookupCode = src.ItemLookupCode
    AND tgt.DepartmentID = src.DepartmentID
    AND tgt.CategoryID = src.CategoryID
    AND tgt.SupplierID = src.SupplierID
    AND tgt.Time = src.Time
WHERE tgt.ItemID IS NULL;
Wendy
  • 640
  • 1
  • 4
  • 8