Calculation report between date...code need help

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculation report between date...code need help

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2012-01-06 : 15:36:59

I need to calculate the amount per reading base on the date it read for the current month. The result need to include the last record before the current month. Below is the sloppy code, but it work. Can someone help, to make this more easier.

thanks

Sample Data:
SELECT * INTO #tmp1 FROM (
SELECT 61117 AS ASSETID , 138026 AS WOPK,CAST('12/27/11' AS DATETIME) AS COMPLETE_DATE, 19765100 RESULT_READING UNION ALL
SELECT 61117 , 142648 ,'1/3/12', 19789500 UNION ALL
SELECT 61117 , 142648 ,'11/3/12', 19789500 UNION ALL
SELECT 61116 , 142648 ,'11/3/12', 19789500 UNION ALL
SELECT 61515 , 138148 ,'12/29/11', 47991100 UNION ALL
SELECT 61515 , 142770 ,'1/5/12', 48067600 UNION ALL
SELECT 61612 , 138160 ,'12/29/11', 4976700 UNION ALL
SELECT 61612 , 142664 ,'1/4/12', 497900 UNION ALL
SELECT 61612 , 142782 ,'1/4/12', 4979170 UNION ALL
SELECT 62042 , 138130 ,'12/29/11', 1069270 UNION ALL
SELECT 62042 , 142661 ,'1/4/12', 1103330 UNION ALL
SELECT 62042 , 142752 ,'1/5/12', 1109340 UNION ALL
SELECT 62049 , 138027 ,'12/29/11', 0 UNION ALL
SELECT 62049 , 142649 ,'1/2/12', 0 UNION ALL
SELECT 62049 , 142649 ,'11/2/12', 0 UNION ALL
SELECT 62054 , 133302 ,'12/19/11', 4221940 UNION ALL
SELECT 62054 , 142628 ,'1/5/12', 4281380 UNION ALL
SELECT 62064 , 138147 ,'12/29/11', 576250 UNION ALL
SELECT 62064 , 142769 ,'1/5/12', 589770 UNION ALL
SELECT 62091 , 138035 ,'12/29/11', 234990 UNION ALL
SELECT 62091 , 142657 ,'1/2/12', 234110 UNION ALL
SELECT 62091 , 142612 ,'1/5/12', 2368310 UNION ALL
SELECT 62120 , 138036 ,'12/29/11', 9655010 UNION ALL
SELECT 62120 , 142658 ,'1/2/12', 9704930 UNION ALL
SELECT 62120 , 142613 ,'1/5/12', 9724640 UNION ALL
SELECT 62150 , 138112 ,'12/29/11', 666040 UNION ALL
SELECT 62150 , 142734 ,'1/5/12', 666040 UNION ALL
SELECT 62184 , 138161 ,'12/29/11', 592300 UNION ALL
SELECT 62184 , 142665 ,'1/3/12', 860600 UNION ALL
SELECT 62184 , 142783 ,'1/4/12', 934500 UNION ALL
SELECT 74515 , 138075 ,'12/27/11', 493840 UNION ALL
SELECT 74515 , 142697 ,'1/3/12', 503700 UNION ALL
SELECT 74542 , 138076 ,'12/27/11', 350960 UNION ALL
SELECT 74542 , 142698 ,'1/3/12', 351700 UNION ALL
SELECT 74644 , 91687 ,'11/2/11', 140920 UNION ALL
SELECT 74644 , 142768 ,'1/5/12', 72860
)B

Code to get result:

;WITH we
AS (SELECT assetid, wopk, complete_date, result_reading, Rank() OVER(PARTITION BY assetid ORDER BY id, complete_date, wopk) AS id
FROM (SELECT 1 AS id,assetid,wopk,complete_date,result_reading
FROM #tmp1
WHERE wopk IN (SELECT MAX(wopk) wopk
FROM #tmp1
WHERE complete_date < Dateadd(s, -1,
Dateadd(mm, Datediff(m, 0
,
Getdate()), 0
)
)
GROUP BY assetid)
AND assetid IN (SELECT MAX(assetid) assetid
FROM #tmp1
WHERE complete_date BETWEEN
Dateadd(s, -1,
Dateadd(mm, Datediff
(m,
0,
Getdate(
)), 0))
AND
Dateadd(s, -1,
Dateadd(mm, Datediff(m, 0,
Getdate(
)) + 1, 0))
GROUP BY assetid)
UNION ALL
SELECT 2,assetid,wopk,complete_date,result_reading
FROM #tmp1
WHERE complete_date BETWEEN Dateadd(s, -1, Dateadd(mm, Datediff
(m, 0
,
Getdate(
)), 0))
AND
Dateadd(s, -1,
Dateadd(mm, Datediff(
m, 0,
Getdate()
) + 1, 0)
))x)
SELECT *,
Datediff(DAY, complete_date, (SELECT TOP 1 complete_date
FROM we
WHERE id < a.id
AND assetid = a.assetid
ORDER BY id)) AS date_between_read,
( (SELECT TOP 1 result_reading
FROM we
WHERE id < a.id
AND assetid = a.assetid
ORDER BY id) - result_reading ) AS result_between_read,
( (SELECT TOP 1 result_reading
FROM we
WHERE id < a.id
AND assetid = a.assetid
ORDER BY id) - result_reading ) / Nullif(
Datediff(DAY, complete_date, (SELECT TOP 1 complete_date
FROM we
WHERE id < a.id
AND assetid = a.assetid
ORDER BY id)), 0) AS perday_result
FROM we a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 03:04:41

wont this be sufficient?


;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY AssetID,WOPK,RESULT_Reading ORDER BY COmplete_Date) AS Rn,
COUNT(CASE WHEN YEAR(COmplete_Date)=2011 THEN 1 END) OVER (PARTITION BY AssetID) AS Cnt,
*
FROM #tmp1
)

SELECT c.assetid,c.wopk,c.complete_date,c.result_reading,row_number() over (partition by assetid order by wopk) as id,
DATEDIFF(dd,c.Complete_Date,c1.Complete_Date) AS date_between_read,
c1.Result_Reading-c.Result_Reading AS result_between_read,
(c1.Result_Reading-c.Result_Reading)/DATEDIFF(dd,c.Complete_Date,c1.Complete_Date) AS perday_result
FROM CTE c
OUTER APPLY (SELECT TOP 1  Result_Reading,Complete_Date
             FROM CTE
             WHERE AssetID = c.AssetID
             AND datediff(yy,0,Complete_date) < datediff(yy,0,c.Complete_date)
             ORDER BY Complete_date DESC)c1
WHERE c.Rn=1
AND c.Cnt > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hai
Yak Posting Veteran

84 Posts

Posted - 2012-01-09 : 13:46:05

visakh16,

thank you for the suggestion, however this "SELECT ROW_NUMBER() OVER (PARTITION BY AssetID,WOPK,RESULT_Reading ORDER BY COmplete_Date) AS Rn,
COUNT(CASE WHEN YEAR(COmplete_Date)=2011 THEN 1 END) OVER (PARTITION BY AssetID) AS Cnt,
*
FROM #tmp1" may not work. The data need from current month, and the last record read from early if exist. With 'COUNT(CASE WHEN YEAR(COmplete_Date)=2011 THEN 1 END' a record with current month and have no previous will not so up. This need to be count.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 04:09:24

quote:
Originally posted by hai

visakh16,

thank you for the suggestion, however this "SELECT ROW_NUMBER() OVER (PARTITION BY AssetID,WOPK,RESULT_Reading ORDER BY COmplete_Date) AS Rn,
COUNT(CASE WHEN YEAR(COmplete_Date)=2011 THEN 1 END) OVER (PARTITION BY AssetID) AS Cnt,
*
FROM #tmp1" may not work. The data need from current month, and the last record read from early if exist. With 'COUNT(CASE WHEN YEAR(COmplete_Date)=2011 THEN 1 END' a record with current month and have no previous will not so up. This need to be count.

thanks

it will show up so far as it has at least one record in year 2011

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources