Easy answer? SQL Query - difference by date

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

Easy answer? SQL Query - difference by date

Author

Topic

bs0d
Starting Member

2 Posts

Posted - 2011-08-26 : 17:14:29

I have a query I developed in access, but I don't know how to convert to MS SQL.

I need to query widgets from a table that have values by date, and subtract the values from eachother for each widget based on the two dates specified.

For example
item 1 on 1/1/2011 value = 15
item 1 on 1/15/2011 value = 10
then item 1 calculated differenec would be -5.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-26 : 19:01:10

use datediff()

select datediff(day, '2011-01-15', '2011-01-01')

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 04:27:12

you need something like


SELECT Item,SUM(CASE WHEN Datefield='20110115' THEN value ELSE 0 END) - SUM(CASE WHEN Datefield='20110101' THEN value ELSE 0 END)  AS Difference
FROM Table
GROUP BY Item

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 03:42:16

Post your ACCESS code

Madhivanan

Failing to plan is Planning to fail

bs0d
Starting Member

2 Posts

Posted - 2011-08-29 : 09:15:39

Thanks for the feedback. Here's the ACCESS code:


SELECT 
Min(AC_DAILY.D_DATE) AS MinOfD_DATE, 
Max(AC_DAILY.D_DATE) AS MaxOfD_DATE, 
AC_PROPERTY.NUM, 
AC_PROPERTY.MTR_NUM, 
AC_PROPERTY.DESCRIPTION, 
Last([ac_daily].[vol])-First([ac_daily].[vol]) AS [vol Variance], Last([ac_daily].[var2])-First([ac_daily].[var2]) AS [var2 Variance], Last([ac_daily].[var3])-First([ac_daily].[var3]) AS [var3 Variance], Last([ac_daily].[var4])-First([ac_daily].[var4]) AS [var4 Variance]  

FROM AC_DAILY INNER JOIN AC_PROPERTY ON AC_DAILY.PROPNUM = AC_PROPERTY.PROPNUM  

WHERE (((AC_DAILY.D_DATE) Between #"1/1/2011"# And #"1/15/2011"#) AND ((AC_PROPERTY.MTR_TYPE) Is Null))  

GROUP BY 
AC_PROPERTY.DESCRIPTION, 
AC_PROPERTY.NUM, 
AC_PROPERTY.MTR_NUM

HAVING (((Last([ac_daily].[vol])-First([ac_daily].[vol]))<="-10" Or (Last([ac_daily].[vol])-First([ac_daily].[vol]))>="10"))

ORDER BY 
AC_PROPERTY.DESCRIPTION, 
AC_PROPERTY.NUM, 
Last([ac_daily].[VOL])-First([ac_daily].[VOL]);)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources