SQL Query Issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query Issue

Author

Topic

Sushil.SHARMA75
Starting Member

2 Posts

Posted - 2012-02-09 : 04:15:27

Need help to improve the below query

SELECT MAX(currvalue) - MIN(currvalue),
CONVERT(VARCHAR(10) , [datetime] , 103)
FROM LOG
WHERE [datetime] BETWEEN '20111215' AND '20111217'
GROUP BY CONVERT(VARCHAR(10) , [datetime] , 103);

My DATA is:
datetiem currvalue
----------------------------------
20111215 11:40:30 PM 200
20111215 11:48:30 PM 210

20111215 11:57:30 PM 240
20111216 12:03:30 PM 280

20111215 11:40:30 PM 320
20111215 11:40:30 PM 350

When I run the above query I get
20111215 40
20111216 70

But if we analysis we found that between
20111215 11:57:30 PM 240 and
20111216 12:03:30 PM 280

difference of 40 is lost.

Actually the data should be
20111215 40
20111216 110

Thank you very much for your help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 04:44:01

You are wrong, the example is wrong and that is really a pity


The given example data and the given select is bringing:
150	15/12/2011
0	16/12/2011

and that is 100% correct!

edit: pitty -> pity

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-09 : 05:21:12

So you data is more likely to look like this?

datetime                 currvalue
----------------------------------
20111215 11:40:30 PM        200
20111215 11:48:30 PM        210

20111215 11:57:30 PM        240
20111216 12:03:30 PM        280

20111216 11:40:30 PM        320
20111216 11:40:30 PM        350

And what you want for 20111216, is that you want the max from previous date (20111215) compared to max of 20111216, which turns out to be 110.

N 56°04'39.26"
E 12°55'05.63"

Sushil.SHARMA75
Starting Member

2 Posts

Posted - 2012-02-10 : 02:34:45

quote:
Originally posted by webfred

You are wrong, the example is wrong and that is really a pity

The given example data and the given select is bringing:
150 15/12/2011
0 16/12/2011

and that is 100% correct!

edit: pitty -> pity
No, you're never too old to Yak'n'Roll if you're too young to die.

Sorry the data post was not correct. The data is:

datetiem currvalue
--------------------- -------------
20111215 11:40:30 PM 200
20111215 11:48:30 PM 210

20111215 11:57:30 PM 240
20111216 12:03:30 PM 280

20111216 11:40:30 PM 320
20111216 11:40:30 PM 350

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:21:42

[code]
;With Value_Diff
AS
(
SELECT DATEADD(dd,DATEDIFF(dd,0,datetime ),0) AS [Date],
MAX(currvalue)-MIN(currvalue) AS diff
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,datetime ),0)
)

SELECT v1.[Date],v2.DiffSoFar
FROM Value_Diff v1
CROSS APPLY (SELECT SUM(Diff) AS DiffSoFar
FROM Value_Diff
WHERE [Date] < = v1.[Date]
)v2
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources