Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
SQL_Nid
Starting Member
2 Posts |
Posted - 2013-06-16 : 02:42:35
|
Hi,I am having data in a table in the formL_Limit U_Limit Asset Timestamp10 20 1000 2013-06-14 16:52:57.91020 30 1500 2013-06-14 16:52:57.91030 40 2200 2013-06-14 16:52:57.91010 20 2000 2013-06-15 18:52:57.91020 30 1300 2013-06-15 18:52:57.91030 40 2100 2013-06-15 18:52:57.91010 20 3000 2013-06-16 18:20:27.91020 30 2300 2013-06-16 18:20:27.91030 40 1100 2013-06-16 18:20:27.910 i.e. a Job appends data(i.e. value of asset) for same ranges 10 to 20,20 to 30 and 30 to 40 everyday when I run a job schedule. This data will be stored on a daily basis for months.Also, this limit range is also saved separately in 'Range' table as columns: Lower_Limit Upper_Limit10 2020 3030 40 My requirement is to show this data in this format in a view to show asset value on 3 days i.e. Today, before 7 days, before 30 days:L_Limit U_Limit Asset_Today Asset_Before7Days Asset_Before30Days10 20 20 3030 40 Please suggest how can I get data in this format?Thanks in advance. |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-16 : 11:14:16
|
Assuming that you want to show asset value from 6/9/2013 under Asset_Before7Days column and asset value from 5/17/2013 under Asset_Before30Days column if today is 6/16/2013Is this what you want?[CODE]DECLARE @QDate DATE = Getdate();SELECT L_Limit, U_Limit, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = @QDate) THEN Asset END) AS TodaysAsset, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = DATEADD(Day, -7, @QDate)) THEN Asset END) AS AssetWeekAgo, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = DATEADD(Day, -30, @QDate)) THEN Asset END) AS AssetMonthAgo FROM @TABLE1 GROUP BY L_Limit, U_Limit;-- ORSELECT T.L_Limit, T.U_Limit, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = @QDate) THEN Asset END) AS TodaysAsset, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = DATEADD(Day, -7, @QDate)) THEN Asset END) AS AssetWeekAgo, MAX(CASE WHEN (CAST([TimeStamp] as DATE) = DATEADD(Day, -30, @QDate)) THEN Asset END) AS AssetMonthAgo FROM @TABLE1 T INNER JOIN @RangeTable R ON (T.L_Limit = R.L_Limit and T.U_Limit = R.U_Limit) GROUP BY T.L_Limit, T.U_Limit[/CODE] |
|
|
|
|
|
|
|