November 24, 2011 at 8:45 am
Good day,
I have a table with a timestamp for each record, I have a need to select the records based on three scenarios:
1. Since Last Year (since 365 days before current day)
2. Since Year to End (starts from Jan 1 of current year)
3. Since Fiscal Year (starts from Nov 1 of current year)
My where clause will need to include this selection criteria. I guess I need to use "case " in it.
Can someone tell me how to write this query?
Many thanks. ;-);-);-)
November 24, 2011 at 12:59 pm
Maybe one reason for not getting an answer yet is the holiday in the U.S. ....
Another reason might be that "timestamp" refers to a "unique binary number within a database" and not a datetime data type....
Assuming the column is of datetime data type you could use
-- 1. Since Last Year (since 365 days before current day)
SELECT DATEADD(yy,-1,getdate())
--2. Since Year to End (starts from Jan 1 of current year)
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
--3. Since Fiscal Year (starts from Nov 1 of current year)
SELECT DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
November 24, 2011 at 1:13 pm
Thank you but I don't think that answered the question:
say the table has two columns: ID, LastUpdatedOn
It has records like:
42009-02-13 17:43:33.110
122009-03-02 11:54:19.590
142010-10-13 18:13:46.470
162011-02-11 10:43:45.503
172010-09-29 14:18:12.720
182010-05-22 18:00:20.680
192009-04-16 16:02:07.540
202008-11-12 12:39:07.343
212009-10-13 12:05:02.267
222010-09-14 15:53:04.980
232009-10-20 14:04:29.890
242010-05-24 11:42:27.977
252010-12-16 16:35:53.277
.......
I need the query be able to pull out data based on the three different parameters, so it must be something like:
select id, lastmodifiedon from table
where function(lastmodifiedon ) = case when @timeline = 1 then ....else when @timeline = 2 then ..... else ..... end
or something like that.
Right?
Thank you again for the help.
November 24, 2011 at 1:28 pm
For a test I wrote this query and hoping to get all records within the last year (<=365 days), but it gives me more than that:
select LastUpdatedOn from infosys
where datediff(dd, LastUpdatedOn, DATEADD(dd,-365,getdate()) ) <= 365
Records returned:
2010-10-13 18:13:46.470
2011-02-11 10:43:45.503
2010-09-29 14:18:12.720
2010-05-22 18:00:20.680
2010-09-14 15:53:04.980
2010-05-24 11:42:27.977
2010-12-16 16:35:53.277
2010-05-30 08:48:10.237
2010-11-12 11:09:04.610
2010-10-12 10:34:45.603
2010-02-02 16:25:05.027
2010-10-12 11:13:52.483
2010-04-12 10:08:50.133
....
November 24, 2011 at 1:32 pm
You didn't mention anything about the parameter @timeline, just three scenarios...
Based on more than 500 points on your side I would have expected you know how to provide ready to use sample data...
Anyway. How about this?
SET @param =
CASE
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
SELECT * FROM @tbl
WHERE dat >= @param
November 24, 2011 at 1:44 pm
I just figured out these queries to pull out data:
--Since last year
select LastUpdatedOn from infosys where datediff(dd, LastUpdatedOn, getdate() ) <= 365
--Since year to end
SELECT LastUpdatedOn From Infosys where LastUpdatedOn > DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
--Since fiscal year
SELECT LastUpdatedOn From Infosys where LastUpdatedOn > DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
How do I consolidate the three queries to one so a parameter can be passed to it? The following query fails. Thanks.
declare @timeline int
select sysid, lastupdatedon from infosys i
where (case when @timeline = 1 then datediff(dd, LastUpdatedOn, getdate()) < 365 else
when @timeline = 2 then LastUpdatedOn > DATEADD(yy,DATEDIFF(yy,0,getdate()),0) else
when @timeline = 3 then LastUpdatedOn > DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
end)
November 24, 2011 at 1:47 pm
LutzM (11/24/2011)
You didn't mention anything about the parameter @timeline, just three scenarios...Based on more than 500 points on your side I would have expected you know how to provide ready to use sample data...
Anyway. How about this?
SET @param =CASE
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
SELECT * FROM @tbl
WHERE dat >= @param
Thank you, this is the final version:
declare @timeline int
set @timeline = 3
declare @param datetime
SET @param =
CASE
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
SELECT lastupdatedon FROM infosys
WHERE lastupdatedon >= @param
November 24, 2011 at 1:48 pm
This may or may not be correct.
What does it mean, "since last year"?
As of this moment, GETDATE() will return 11/24/2011 12:45 and some seconds.
Do you want data from 11/24/2010 12:45 onwards, or for the entire day from 11/24/2010 12:00 AM?
For business reports it is usually the latter.
November 24, 2011 at 2:12 pm
Revenant (11/24/2011)
This may or may not be correct.What does it mean, "since last year"?
As of this moment, GETDATE() will return 11/24/2011 12:45 and some seconds.
Do you want data from 11/24/2010 12:45 onwards, or for the entire day from 11/24/2010 12:00 AM?
For business reports it is usually the latter.
Also, "365 days ago" do not always refer to the same day of the previous year.
So, the DATEADD(yy,-1) method is not the equivalent either.
But the less precise the requirement is, the more vague is the answer... 😉
November 24, 2011 at 2:24 pm
Thanks for all the help and concern about the preciseness about the date/time. The project doesn't require that precise at this time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply