Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collect data on the basis of timestamp from a table in a view Expand / Collapse
Author
Message
Posted Sunday, June 16, 2013 12:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 16, 2013 8:14 AM
Points: 5, Visits: 13
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.
Post #1463929
Posted Sunday, June 16, 2013 7:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 1,912, Visits: 19,379
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_Before30Days
10 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
Post #1463954
Posted Sunday, June 16, 2013 8:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
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




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1463955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse