Todays latest and yesterdays latest record

Question

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 Todays latest record, and yesterdays latest record of A1,A2,A3,A4 showing like 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 how to write query to get data

Answer 1

This seems to give your expected output, including the two "dashed" results for 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 set up the sample data, we start constructing the query by way of a few CTEs. The first, Dated, just removes the time portion from the mis-named Date column.

Numbered then assigns row numbers to each result, based on dates and codes.

Codes gets the set of all codes for which we have data, so that we can produce results whether or not a particular code has an entry for today or yesterday.

Finally, we use these CTEs to construct your result set, by way of a 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   -