问题描述:

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

网友答案:

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   -
网友答案:
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

Example at SQL Fiddle.

网友答案:

Try this:

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)
网友答案:

Here is 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
相关阅读:
Top