June 16, 2013 at 12:54 am
Hi,
I am having data in a table in the form
L_Limit U_Limit Asset Timestamp
10 20 1000 2013-06-14 16:52:57.910
20 30 1500 2013-06-14 16:52:57.910
30 40 2200 2013-06-14 16:52:57.910
10 20 2000 2013-06-15 18:52:57.910
20 30 1300 2013-06-15 18:52:57.910
30 40 2100 2013-06-15 18:52:57.910
10 20 3000 2013-06-16 18:20:27.910
20 30 2300 2013-06-16 18:20:27.910
30 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_Limit
10 20
20 30
30 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_Before30Days
10 20
20 30
30 40
Please suggest how can I get data in this format?
Thanks in advance.
June 16, 2013 at 7:46 am
nidhi.finance1 (6/16/2013)
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 30
30 40
Please suggest how can I get data in this format?
Thanks in advance.
based on what you have posted so far....what results are you expecting to return from your data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 16, 2013 at 8:26 am
Something like this..
SELECT L_Limit,
U_Limit,
SUM(CASE
WHEN DATEDIFF(day, Timestamp, CURRENT_TIMESTAMP) = 0 THEN Asset
ELSE 0
END) AS Asset_Today,
SUM(CASE
WHEN DATEDIFF(day, Timestamp, CURRENT_TIMESTAMP) = 7 THEN Asset
ELSE 0
END) AS Asset_Before7Days,
SUM(CASE
WHEN DATEDIFF(day, Timestamp, CURRENT_TIMESTAMP) = 30 THEN Asset
ELSE 0
END) AS Asset_Before30Days
FROM YourTableName -- Replace with you table name
WHERE DATEDIFF(day, Timestamp, CURRENT_TIMESTAMP) IN ( 0, 7, 30 )
GROUP BY L_Limit,
U_Limit
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply