Today's last and yesterday's last entry
I have a table with Code and Date
Code Date
----------------------------
A1 21 May 2015 15:47
A2 21 May 2015 10:30
A3 20 May 2015 10:30
A4 21 May 2015 10:30
A1 19 May 2015 15:20
A2 21 May 2015 12:30
A3 19 May 2015 05:30
A4 18 May 2015 15:38
A1 19 May 2015 05:30
A2 20 May 2015 05:30
A3 21 May 2015 05:30
A4 21 May 2015 05:30
A3 21 May 2015 06:30
A1 21 May 2015 05:30
I need to get the latest Todays record and yesterday's most recent record A1, A2, A3, A4 shown below
Flag Code Date
-----------------------------------------
Today A1 21 May 2015 15:47
Today A2 21 May 2015 10:30
Today A3 21 May 2015 06:30
Today A4 21 May 2015 10:30
Yesterday A1 --
Yesterday A2 20 May 2015 05:30
Yesterday A3 20 May 2015 10:30
Yesterday A4 --
Help me write a request to get data
source to share
This seems to give the expected result, including the two dashed results from yesterday:
declare @t table (Code char(2),[Date] datetime)
insert into @t(Code,Date) values
('A1','2015-05-21T15:47:00'),
('A2','2015-05-21T10:30:00'),
('A3','2015-05-20T10:30:00'),
('A4','2015-05-21T10:30:00'),
('A1','2015-05-19T15:20:00'),
('A2','2015-05-21T12:30:00'),
('A3','2015-05-19T05:30:00'),
('A4','2015-05-18T15:38:00'),
('A1','2015-05-19T05:30:00'),
('A2','2015-05-20T05:30:00'),
('A3','2015-05-21T05:30:00'),
('A4','2015-05-21T05:30:00'),
('A3','2015-05-21T06:30:00'),
('A1','2015-05-21T05:30:00')
;With Dated as (
select *,DATEADD(day,DATEDIFF(day,0,[Date]),0) as BetterDate
from @t
), Numbered as (
select *,ROW_NUMBER() OVER (
PARTITION BY Code,BetterDate
ORDER BY [Date] desc) as rn
from Dated
), Codes as (
select distinct Code from @t
)
select
'Today' as Occasion,
c.Code,
COALESCE(CONVERT(varchar(20),n1.Date),'-') as Date
from
Codes c
left join
Numbered n1
on
c.Code = n1.Code and
n1.rn = 1 and
n1.BetterDate = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
union all
select
'Yesterday',
c.Code,
COALESCE(CONVERT(varchar(20),n1.Date),'-') as Date
from
Codes c
left join
Numbered n1
on
c.Code = n1.Code and
n1.rn = 1 and
n1.BetterDate = DATEADD(day,DATEDIFF(day,0,GETDATE()),-1)
order by Occasion,Code
After we have installed the sample data, we start building a query using several CTEs. The first, Dated
simply removes the temporary portion from the incorrectly named column Date
.
Numbered
then assigns line numbers to each result based on dates and codes.
Codes
gets a set of all codes for which we have data so that we can give results regardless of whether a particular code has a record for today or yesterday.
Finally, we use these CTEs to build your result set with UNION ALL
Result:
Occasion Code Date
--------- ---- --------------------
Today A1 May 21 2015 3:47PM
Today A2 May 21 2015 12:30PM
Today A3 May 21 2015 6:30AM
Today A4 May 21 2015 10:30AM
Yesterday A1 -
Yesterday A2 May 20 2015 5:30AM
Yesterday A3 May 20 2015 10:30AM
Yesterday A4 -
source to share
select case
when cast([Date] as date) >= cast(getdate() as date) then 'Today'
else 'Yesterday'
end as Flag
, Code
, Date
from (
select row_number() over (
partition by Code, cast([Date] as date)
order by [Date] desc) rn
, *
from YourTable
where cast([Date] as date) > dateadd(day, -1, cast(getdate() as date))
) as SubQueryAlias
where rn = 1
source to share
Here's some code:
DECLARE @t TABLE(Code CHAR(2), Date DATETIME)
INSERT INTO @t
VALUES ( 'A1', '21 May 2015 15:47' ),
( 'A2', '21 May 2015 10:30' ),
( 'A3', '20 May 2015 10:30' ),
( 'A4', '21 May 2015 10:30' ),
( 'A1', '19 May 2015 15:20' ),
( 'A2', '21 May 2015 12:30' ),
( 'A3', '19 May 2015 05:30' ),
( 'A4', '18 May 2015 15:38' ),
( 'A1', '19 May 2015 05:30' ),
( 'A2', '20 May 2015 05:30' ),
( 'A3', '21 May 2015 05:30' ),
( 'A4', '21 May 2015 05:30' ),
( 'A3', '21 May 2015 06:30' ),
( 'A1', '21 May 2015 05:30' )
;WITH codes AS(SELECT DISTINCT Code, d FROM @t
CROSS JOIN (VALUES(CAST(GETDATE() AS DATE)),
(CAST(DATEADD(dd, -1, GETDATE()) AS DATE)))d(d))
SELECT CASE WHEN DAY(GETDATE()) = DAY(Date) THEN 'Today' ELSE 'Yestarday' END Day ,
c.Code ,
MAX(Date) AS Date
FROM codes c
LEFT JOIN @t t ON t.Code = c.Code AND CAST(t.Date AS DATE) = c.d
WHERE Date IS NULL OR Date > CAST(DATEADD(dd, -1, GETDATE()) AS DATE)
GROUP BY c.Code , DAY(Date)
ORDER BY Day, Code
Output:
Day Code Date
Today A1 2015-05-21 15:47:00.000
Today A2 2015-05-21 12:30:00.000
Today A3 2015-05-21 06:30:00.000
Today A4 2015-05-21 10:30:00.000
Yestarday A1 NULL
Yestarday A2 2015-05-20 05:30:00.000
Yestarday A3 2015-05-20 10:30:00.000
Yestarday A4 NULL
source to share
Try the following:
SELECT f.Name, c.code, MAX(y.[Date]) AS [Date]
FROM (SELECT -1 ID, 'yesterday' Name
UNION ALL
SELECT 0, 'today') f
CROSS JOIN
(SELECT code
FROM yourTable
GROUP BY code) c
LEFT OUTER JOIN
yourTable y ON c.code = y.code AND DATEDIFF(DAY, GETDATE(), y.[Date]) = f.ID
WHERE
ISNULL(DATEDIFF(DAY, GETDATE(), y.[Date]), 0) > -2
GROUP BY
f.Name, c.code, ISNULL(DATEDIFF(DAY, GETDATE(), y.[Date]), 0)
source to share