Compare data to the same time last year?

Question

Post reply

Compare data to the same time last year?

craighenderson

SSCommitted

Points: 1574
More actions
January 12, 2010 at 2:43 am

#144085

I have been using this SQL to get data for yesterday
select SUM(transactionamount) from itransaction where outletid = 1395 and transactiontypeid = 1001 and createdon >= cast(convert(varchar(8),getdate()-1,1) as datetime)
how do I get the same data for this time last year? (same day but a year past?)
Any help really appreciated
Thanks
Craig Henderson

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

--Today

SELECT cast(convert(varchar(8),getdate(),1) as datetime)

--Yesterday

SELECT cast(convert(varchar(8),getdate()-1,1) as datetime)

--LAST YEAR

SELECT cast(convert(varchar(8),(dateadd(year,-1,getdate()-1)),1) as datetime)

--Today

SELECT DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)

--Yesterday

SELECT DATEADD(day,DATEDIFF(day, 0, GETDATE()),-1)

--LAST YEAR

SELECT DATEADD(YEAR,-1,(DATEADD(day,DATEDIFF(day, 0, GETDATE()),-1)))

craighenderson SSCommitted Points: 1574 More actions · Answer 2

Thank you.

My sql is obviously running for a date range that is equal to or greater than the specified date, so when I run the script for 1 year ago, I get all the data since then - how should I change tge SQL to run just for that date.

removing the '>' returns NULL, presumably because its looking for dd/mm/yyyy hh.mm.ss

how do I change it to only look for dd/mm/yyyy?

Thanks

Craig H

arun.sas SSChampion Points: 11831 More actions · Answer 3

--Today

SELECT convert(varchar(15),getdate(),103)

--Yesterday

SELECT convert(varchar(15),getdate()-1,103)

--LAST YEAR

SELECT convert(varchar(15),((dateadd(year,-1,getdate()-1))),103)

Jeffrey Williams SSC Guru Points: 90298 More actions · Answer 4

If you want the data from the day one year ago, calculate that day first:

DECLARE @startDate datetime;

SET @startDate = dateadd(year, -1, dateadd(day, datediff(day, 0, getdate()), 0));

Now, you can perform you select as:

SELECT SUM(transactionamount)

FROM dbo.itransaction

WHERE outletid = 1395

AND transactiontypeid = 1001

AND createdon >= @startDate

AND createdon < dateadd(day, 1, @startDate);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

craighenderson SSCommitted Points: 1574 More actions · Answer 5

Thanks for this

If I run this

DECLARE @startDate datetime;

DECLARE @enddate datetime;

SET @startDate = dateadd(year, -8, dateadd(day, datediff(day, 0, getdate()), 0));

SET @endDate = dateadd(year, -1, dateadd(day, datediff(day, 0, getdate()), 0));

SELECT SUM(transactionamount)

FROM dbo.itransaction

WHERE outletid = 1182

AND transactiontypeid = 1002

AND createdon >= @startdate

AND createdon < @enddate;

Will it show me data for a week long period from one year ago?

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Craig, you can check the values of the variables:

DECLARE @startDate datetime;

DECLARE @enddate datetime;

SET @startDate = dateadd(year, -8, dateadd(day, datediff(day, 0, getdate()), 0));

SET @endDate = dateadd(year, -1, dateadd(day, datediff(day, 0, getdate()), 0));

SELECT @startDate, @endDate

Did you intend to select a date 8 years ago? Have a look at DATEADD in BOL.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

craighenderson SSCommitted Points: 1574 More actions · Answer 7

Doh!

No I wanted a date 1 year and 8 days ago...

learning all the time, just not quick enough eh 🙂

This is what I was meant to do...

DECLARE @startDate datetime;

DECLARE @enddate datetime;

SET @startDate = dateadd(year, -1, dateadd(day, datediff(day, 8, getdate()), 0));

SET @endDate = dateadd(year, -1, dateadd(day, datediff(day, 1, getdate()), 0));

SELECT SUM(transactionamount)

FROM dbo.itransaction

WHERE outletid = 1182

AND transactiontypeid = 1002

AND createdon >= @startdate

AND createdon < @enddate;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

craighenderson (1/14/2010)
Doh!
No I wanted a date 1 year and 8 days ago...
learning all the time, just not quick enough eh 🙂

You're doing just fine, Craig.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden