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
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.