SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 DATETIME -1 ENTRIES
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Yak Posting Veteran

60 Posts

Posted - 02/27/2014 :  20:31:01  Show Profile  Reply with Quote
i would like to update my stored procedure, which is ran daily, to only enter current day and 1 day prior. The tsql is doing joins on 4 tables i would like to get the previous days entries along with the current day it is being loaded on a column labled last_update_date.

i have something like this last_update_date >= dateadd(day,datediff(day,1,GETDATE()),0)


however it is still pulling all the historical days which i dont want, because it will create duplicate rows. if the day already exist it should skip over it and only enter current day -1(previous).

khtan
In (Som, Ni, Yak)

Singapore
17606 Posts

Posted - 02/27/2014 :  20:49:56  Show Profile  Reply with Quote
last_update_date >= dateadd(day,datediff(day,1,GETDATE()),0)

that condition will only give you records where last_update_date that is greater than yesterday's date.
quote:
however it is still pulling all the historical days which i dont want

what are the last_update_date for those rows ?


KH
Time is always against us

Go to Top of Page

Blessed1978
Yak Posting Veteran

60 Posts

Posted - 02/28/2014 :  07:06:52  Show Profile  Reply with Quote
the last update date is the last day the record was updated so if it is currently 2014-02-28 00:00:00:000 it should look back to 2014-02-27 00:00:00:000 and 2014-02-28 00:00:00:000
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17606 Posts

Posted - 02/28/2014 :  08:02:01  Show Profile  Reply with Quote
so isn't it giving you what you wanted ?

If not, post your sample data, full query and expected result


KH
Time is always against us

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 02/28/2014 :  09:10:16  Show Profile  Reply with Quote
USE Tempdb;
GO

SET NOCOUNT ON;

IF OBJECT_ID ('#CustomerA', 'U') IS NOT NULL
DROP TABLE #CustomerA;
SELECT 1 as ID ,
CAST('28/02/2014' as DATE) as TranDate
INTO #CustomerA;

INSERT INTO #CustomerA
SELECT 2 as ID ,
CAST('27/02/2014' as DATE) as TranDate;

INSERT INTO #CustomerA
SELECT 3 as ID ,
CAST('26/02/2014' as DATE) as TranDate;
GO


SELECT *
FROM #CustomerA as A
WHERE TranDate >= CAST((GETDATE() -1) as DATE)


quote:
Originally posted by khtan

so isn't it giving you what you wanted ?

If not, post your sample data, full query and expected result


KH
Time is always against us



Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/28/2014 :  11:05:59  Show Profile  Reply with Quote
What do you want for output?

If you can describe in words what you want to happen it makes it, potentially, easier to understand than code that doesn't work/give the proper results.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000