Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-09-04 : 23:54:51

Hi guys,

I'm writing a query but have got a bit stuck on one point and not sure how to approach it.

I have a table with date (datetime) and outstandingorders (int).
What I need to retrieve, is the change in outstandingorders between Monday and Friday.

So we have say the following:
2008-08-28 115
2008-08-29 90
2008-09-01 100
2008-09-02 105
2008-09-03 102
2008-09-04 146
2008-09-05 110

What I would want to do, is 110 - 100, result of 10.
This is the outstandingorders value for 2008-09-05 (Friday) less that of 2008-09-01 (Monday).

Is there any way I can make this work?
I'm really not sure where to start with it.

Thanks guys,
Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-05 : 00:00:16

without more details on your table's PK etc, maybe like this ?


select f.outstandingorders - m.outstandingorders 
from   table1 f
       inner join table1 m on  f.[date] = '20080905'
                           and m.[date] = '20080901'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-05 : 00:55:22

[code]SET DATEFIRST 7

SELECT DATENAME(yy,DATEADD(wk,DATEDIFF(wk,0,date),0))+DATENAME(wk,DATEADD(wk,DATEDIFF(wk,0,date),0)),
SUM(CASE WHEN DATEPART(dw,date)=2 THEN outstandingorders ELSE 0 END)-SUM(CASE WHEN DATEPART(dw,date)=6 THEN outstandingorders ELSE 0 END) AS Weekdiff
FROM Table
GROUP BY DATENAME(yy,DATEADD(wk,DATEDIFF(wk,0,date),0))+DATENAME(wk,DATEADD(wk,DATEDIFF(wk,0,date),0))[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources