Converting Start and End Dates to time records

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)

Converting Start and End Dates to time records

Author

Topic

homeguard
Starting Member

32 Posts

Posted - 2008-10-02 : 13:53:36

Hello,

I have to figure up turn around time for orders on a table.

The table is layed out like,

Order Num____Start______________________End
3431_________2008-09-26 06:18:11.000____2008-09-26 09:20:41.000
3432_________2008-09-25 16:12:29.000____2008-09-28 09:50:36.000

translate those records to:

Order Num______Date___________Seconds
3431___________2008-09-26_____10950
3432___________2008-09-25_____28171
3432___________2008-09-26_____86400
3432___________2008-09-27_____86400
3432___________2008-09-28_____35436

As you can see I am calculating how many seconds the order has accumulated each day.

I want to do some daily figures this is why I am figuring up each day.

Any suggestions? I can’t think of a good way to do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 14:16:59

[code]SELECT t.Order_Num,
DATEADD(dd,v.number,DATEADD(dd,DATEDIFF(dd,0,t.Start),0)) AS Date,
CASE WHEN DATEADD(dd,v.number,DATEADD(dd,DATEDIFF(dd,0,t.Start),0)) =DATEADD(dd,DATEDIFF(dd,0,t.End),0) THEN DATEDIFF(ss,DATEADD(dd,DATEDIFF(dd,0,t.End),0),t.End)
WHEN DATEADD(dd,v.number,DATEADD(dd,DATEDIFF(dd,0,t.Start),0))= DATEADD(dd,DATEDIFF(dd,0,t.Start),0) THEN 86400-DATEDIFF(ss,DATEADD(dd,DATEDIFF(dd,0,t.Start),0),t.Start)
ELSE 86400
END
FROm table t
CROSS JOIN master..spt_values v
WHERE type='p'
AND DATEADD(dd,v.number,DATEADD(dd,DATEDIFF(dd,0,t.Start),0)) <= DATEADD(dd,DATEDIFF(dd,0,t.End),0)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 15:17:03

See this topic which is what you need.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110247

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources