April 25, 2012 at 1:22 am
Hi, Let say we have given some dates in our table.
the structure is as below
CREATE TABLE [dbo].[datetest](
[test_date] [datetime] NULL
)
insert into datetest
select '2012-04-25'
union
select '2011-12-31'
union
select '2011-12-27'
union
select '2011-12-23'
union
select '2011-12-23'
union
select '2011-12-20'
Now here the case is we have the latest date 25-APR-2012. I want the latest previous week dates from the max date. In this case the max date is 25-APR-2012. so the latest previous week dates are 31-DEC-2011 and 27-DEC-2011. I have written the code like
select Distinct CONVERT(VARCHAR(10),test_date,126) AS test_date
from datetest
where datepart(ww, test_date) in(select top 1 datepart(ww, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)
AND datepart(YYYY, test_date) in(select top 1 datepart(yyyy, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)
But I am not getting any output. Any idea please.
Thanks
April 25, 2012 at 2:36 am
rajn.knit07 (4/25/2012)
Hi, Let say we have given some dates in our table.the structure is as below
CREATE TABLE [dbo].[datetest](
[test_date] [datetime] NULL
)
insert into datetest
select '2012-04-25'
union
select '2011-12-31'
union
select '2011-12-27'
union
select '2011-12-23'
union
select '2011-12-23'
union
select '2011-12-20'
Now here the case is we have the latest date 25-APR-2012. I want the latest previous week dates from the max date. In this case the max date is 25-APR-2012. so the latest previous week dates are 31-DEC-2011 and 27-DEC-2011. I have written the code like
select Distinct CONVERT(VARCHAR(10),test_date,126) AS test_date
from datetest
where datepart(ww, test_date) in(select top 1 datepart(ww, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)
AND datepart(YYYY, test_date) in(select top 1 datepart(yyyy, test_date)as weeknumber
from datetest
where datepart(ww, test_date)<= datepart(ww,'2012-01-02')
order by datepart(yyyy, test_date) desc,
datepart(ww, test_date) desc)
But I am not getting any output. Any idea please.
Thanks
Your query as written will return the latest test date in the same week and the same year, not the two previous tests. The reason that no rows are returned is that no test has been carried out in 2012 WK 1 (week containing 2nd Jan 2012).
As a simple typo (I presume) the date 2011-12-23 has been entered twice. Using UNION this will distinct the set to only leave one row with that date. Either enter a different datetime or use UNION ALL.
As a starter the code below will return the two previous tests given a single test date.
with OrderedList as (
select *, row_number() over (order by test_date desc) as RN
)
select ol1.test_date, ol2.test_date as PreviousDate, ol3.test_date as PreviousPreviousDate
from OrderedList as ol1
left join OrderedList as ol2
on ol1.RN + 1 = ol2.RN
left join OrderedList as ol3
on ol1.RN + 2 = ol3.RN
where ol1.test_date = '2012-04-25'
And in SQL 2012
with MatchedList as (
select *,
lag(test_date,1,NULL) over (order by test_date) as Previous1,
lag(test_date,2,NULL) over (order by test_date) as Previous2
)
select *
from MatchedList
where test_date = '2012-04-25'
Fitz
April 25, 2012 at 2:40 am
April 25, 2012 at 2:47 am
vinu512 (4/25/2012)
Would this work?
SELECT TOP 1 test_date
FROM (
SELECT DISTINCT TOP 2 test_date
FROM datetest
ORDER BY test_date DESC) a
ORDER BY test_date
This would return the second last test date. The OP wanted the two previous test dates for a given test date.
Fitz
April 25, 2012 at 2:55 am
This was removed by the editor as SPAM
April 25, 2012 at 3:03 am
I thought the OP wants the previous date for the "Latest" test date. The latest test date not only in this case but in all cases will be the top 1 when ordered as DESC. So the next one would be calculated from my query.
I could have misunderstood what the OP wrote.
April 25, 2012 at 3:09 am
vinu512 (4/25/2012)
I thought the OP wants the previous date for the "Latest" test date. The latest test date not only in this case but in all cases will be the top 1 when ordered as DESC. So the next one would be calculated from my query.I could have misunderstood what the OP wrote.
No problem,its not clear as the OP has asked for one thing then produced sample code to answer a different question (from OP original post "I want the latest previous week dates from the max date. In this case the max date is 25-APR-2012. so the latest previous week dates are 31-DEC-2011 and 27-DEC-2011.")
Fitz
April 25, 2012 at 4:12 am
@Fitz : So, it all comes down to how the OP decides what the "Latest Date" is.
@rajn.knit07 : Please be more elaborate on how you decide which one of the dates is the "Latest Date".
April 25, 2012 at 6:27 pm
Here's another way:
DECLARE @datetest TABLE ([test_date] [datetime] NULL
)
insert into @datetest
select '2012-04-25' union all select '2011-12-31'
union all select '2011-12-27' union all select '2011-12-23'
union all select '2011-12-23' union all select '2011-12-20'
;WITH a (test_date, rk) AS (
SELECT test_date, ROW_NUMBER() OVER (PARTITION BY (SELECT NULL) ORDER BY test_date DESC)
FROM @datetest)
SELECT test_date
FROM a WHERE rk BETWEEN 2 and 3
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply