Display data for yesterday and today in the same row

I have the following table in SQL Server Express representing name, date (which contains today's date and yesterday's date), and value

 ID  |  Name  |    Date    |  Value
 1   |  ABC   | 07/03/2012 |   20
 2   |  ABC   | 07/04/2012 |   22
 3   |  XYZ   | 07/03/2012 |   16
 4   |  XYZ   | 07/04/2012 |   18

How can I create a view in SQL Server which will display the data for a particular 'Name' in two columns as Today's Value and Yesterday's value? I tried using derived table and union but I did not get the desired output? What should be the approach. The output I am looking for is as follows:

 ID  |  Name  | Today's Value | Yesterday's Value
 1   |  ABC   |     22        |      20
 2   |  XYZ   |     18        |      16

Answers


I'm not sure if it's a coincidence that every row has a matched pair, that they're always exactly a day apart, and that you want July 4th to somehow represent "today" or if that is just supposed to reflect the highest day found in the table. Given the limited info and the sample data, this view gets the desired results:

CREATE VIEW dbo.view_name
AS
  WITH x AS 
  (
    SELECT ID, Name, [Date], Value,
      rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [Date] DESC)
    FROM dbo.table_name
  )
  SELECT x2.ID, x2.Name, 
    [Today's Value] = x.Value,
    [Yesterday's Value] = x2.Value
  FROM x
  INNER JOIN x AS x2 
  -- not sure what you want if there is no data for "yesterday";
  -- maybe above should be an outer join
  ON x.Name = x2.Name

  -- if there may be gaps between days:
  AND x.rn = x2.rn - 1

  -- if there will always be data for every day: 
  AND x.[Date] = DATEADD(DAY, 1, x2.[Date])

  WHERE x.rn = 1
  ORDER BY x2.ID;

...however you should strive to include any edge cases or extraneous information that doesn't work quite so nicely as the four rows you've provided.


You can do this with a simple join:

select id, name, t.value as todayValue, y.value as yesterdayValue
from (select t.*
      from t
      where t.date = cast(getdate() as date)
     ) t left outer join
     (select t.*
      from t
      where t.date = cast(dateadd(d, -1, getdate()) as date)
     ) y
     on t.name = y.name

You can make this a view by adding "create view as" before the select.

This formulation assumes the data is exactly as you specify it in your question. Exactly one row for each day.


Need Your Help

Effect to audio and video file

ios iphone avfoundation core-audio

In my app I want to give effect to audio and video file like it is given to Pheed Application. I want to mix audio recorded sound with predefine clips. And also want to give sound different effect...

More than 15 KML-layers wont be visible on Google Maps API-based page

javascript google-maps google-maps-api-3 kml

I have this strange issue: I have to generate many KML-layers from separate KML-files to a single map window. The amounts vary between like a couple and less than fifty. At the moment my code is