Solved

datediff sql

Posted on 2008-10-17
13
521 Views
Last Modified: 2010-04-21
I need to get a datediff ,  WEEK_HRS, DATEDIFF(n, [total week], WEEK_HRS)
                      AS REMAINING. My weekly hours are entered as CONVERT(VARCHAR, 2095, 3) AS WEEK_HRS.  2095 is the minutes. My result from total week 540 - week_hrs 2095 = 101783520 which is mad.

SELECT     CAST([total week] / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST([total week] % 60 AS varchar(2)), 2) AS TimeInHours, empid, CONVERT(char(5), 

                      START_MON, 108) AS START_MON, CONVERT(char(5), LUNCH_OUT_MON, 108) AS LUNCH_OUT_MON, CONVERT(char(5), LUNCH_BACK_MON, 108) 

                      AS LUNCH_BACK_MON, CONVERT(char(5), END_MON, 108) AS END_MON, Mon_morning, Mon_Evening, CONVERT(char(5), START_TUE, 108) 

                      AS START_TUE, CONVERT(char(5), LUNCH_OUT_TUE, 108) AS LUNCH_OUT_TUE, CONVERT(CHAR(5), LUNCH_BACK_TUE, 108) AS LUNCH_BACK_TUE, 

                      CONVERT(CHAR(5), END_TUE, 108) AS END_TUE, TUE_morning, TUE_Evening, CONVERT(CHAR(5), START_Wed, 108) AS START_WED, 

                      CONVERT(CHAR(5), LUNCH_OUT_Wed, 108) AS LUNCH_OUT_Wed, CONVERT(CHAR(5), LUNCH_BACK_Wed, 108) AS LUNCH_BACK_WED, 

                      CONVERT(CHAR(5), END_Wed, 108) AS END_Wed, Wed_morning, Wed_Evening, CONVERT(CHAR(5), START_Thu, 108) AS START_THU, 

                      CONVERT(CHAR(5), LUNCH_OUT_Thu, 108) AS LUNCH_OUT_THU, CONVERT(CHAR(5), LUNCH_BACK_Thu, 108) AS LUNCH_BACK_THU, 

                      CONVERT(CHAR(5), END_Thu, 108) AS END_THU, Thu_morning, Thu_Evening, CONVERT(CHAR(5), START_Fri, 108) AS START_FRI, CONVERT(CHAR(5), 

                      LUNCH_OUT_Fri, 108) AS LUNCH_OUT_FRI, CONVERT(CHAR(5), LUNCH_BACK_Fri, 108) AS LUNCH_BACK_FRI, CONVERT(CHAR(5), END_Fri, 108) 

                      AS END_FRI, Fri_morning, Fri_Evening, CAST(tot_Monday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Monday % 60 AS varchar(2)), 2) 

                      AS TOT_MONDAY, CAST(tot_Tueday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Tueday % 60 AS varchar(2)), 2) AS TOT_TUEDAY, 

                      CAST(tot_Wedday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Wedday % 60 AS varchar(2)), 2) AS TOT_WEDDAY, 

                      CAST(tot_Thuday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Thuday % 60 AS varchar(2)), 2) AS TOT_THUDAY, CAST(tot_Friday / 60 AS varchar(7)) 

                      + ':' + RIGHT('00' + CAST(tot_Friday % 60 AS varchar(2)), 2) AS TOT_FRIDAY, [total week], WEEK_HRS, DATEDIFF(n, [total week], WEEK_HRS) 

                      AS REMAINING

FROM         (SELECT     time2.*, tot_Monday + tot_Tueday + tot_Wedday + tot_Thuday + tot_Friday AS [total week]

                       FROM          (SELECT     time1.*, mon_morning + mon_evening AS tot_Monday, tue_morning + tue_evening AS tot_Tueday, 

                                                                      wed_morning + wed_evening AS tot_Wedday, thu_morning + thu_evening AS tot_Thuday, 

                                                                      fri_morning + fri_evening AS tot_Friday

                                               FROM          (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, 

                                                                                              LUNCH_OUT_MON) AS Mon_morning, DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, START_TUE, 

                                                                                              LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) AS TUE_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed, 

                                                                                              END_Wed, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, 

                                                                                              END_Wed) AS Wed_Evening, START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu,

                                                                                               LUNCH_OUT_Thu) AS Thu_morning, DATEDIFF(n, LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, START_Fri, 

                                                                                              LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, CONVERT(VARCHAR, '2095', 3) AS WEEK_HRS

                                                                       FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime

Open in new window

0
Comment
Question by:Camnoc
  • 7
  • 6
13 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22744609
You don't want a datediff because you aren't dealing with date columns.  Just do a simple subtract.

select week_hrs-[total week] as remaining......
0
 

Author Comment

by:Camnoc
ID: 22746150
Hi Brandon
Fantastic.
One problem converting remaining to hh.mm using
CAST(remaining/ 60 AS varchar(7)) + ':' + RIGHT ('00' + CAST(remaining % 60 AS varchar(2)), 2)
not working?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22746197
Unless you put: week_hrs-[total week] as remaining

into a subselect, you can't reference it as remaining.

Rather than me trying to wedge it in up there, post what you have and I'll fix it :)
0
 

Author Comment

by:Camnoc
ID: 22746223
Thank you
Code attached
SELECT     CAST([total week] / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST([total week] % 60 AS varchar(2)), 2) AS TimeInHours, empid, CONVERT(char(5), 

                      START_MON, 108) AS START_MON, CONVERT(char(5), LUNCH_OUT_MON, 108) AS LUNCH_OUT_MON, CONVERT(char(5), LUNCH_BACK_MON, 108) 

                      AS LUNCH_BACK_MON, CONVERT(char(5), END_MON, 108) AS END_MON, Mon_morning, Mon_Evening, CONVERT(char(5), START_TUE, 108) 

                      AS START_TUE, CONVERT(char(5), LUNCH_OUT_TUE, 108) AS LUNCH_OUT_TUE, CONVERT(CHAR(5), LUNCH_BACK_TUE, 108) AS LUNCH_BACK_TUE, 

                      CONVERT(CHAR(5), END_TUE, 108) AS END_TUE, TUE_morning, TUE_Evening, CONVERT(CHAR(5), START_Wed, 108) AS START_WED, 

                      CONVERT(CHAR(5), LUNCH_OUT_Wed, 108) AS LUNCH_OUT_Wed, CONVERT(CHAR(5), LUNCH_BACK_Wed, 108) AS LUNCH_BACK_WED, 

                      CONVERT(CHAR(5), END_Wed, 108) AS END_Wed, Wed_morning, Wed_Evening, CONVERT(CHAR(5), START_Thu, 108) AS START_THU, 

                      CONVERT(CHAR(5), LUNCH_OUT_Thu, 108) AS LUNCH_OUT_THU, CONVERT(CHAR(5), LUNCH_BACK_Thu, 108) AS LUNCH_BACK_THU, 

                      CONVERT(CHAR(5), END_Thu, 108) AS END_THU, Thu_morning, Thu_Evening, CONVERT(CHAR(5), START_Fri, 108) AS START_FRI, CONVERT(CHAR(5), 

                      LUNCH_OUT_Fri, 108) AS LUNCH_OUT_FRI, CONVERT(CHAR(5), LUNCH_BACK_Fri, 108) AS LUNCH_BACK_FRI, CONVERT(CHAR(5), END_Fri, 108) 

                      AS END_FRI, Fri_morning, Fri_Evening, CAST(tot_Monday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Monday % 60 AS varchar(2)), 2) 

                      AS TOT_MONDAY, CAST(tot_Tueday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Tueday % 60 AS varchar(2)), 2) AS TOT_TUEDAY, 

                      CAST(tot_Wedday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Wedday % 60 AS varchar(2)), 2) AS TOT_WEDDAY, 

                      CAST(tot_Thuday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Thuday % 60 AS varchar(2)), 2) AS TOT_THUDAY, CAST(tot_Friday / 60 AS varchar(7)) 

                      + ':' + RIGHT('00' + CAST(tot_Friday % 60 AS varchar(2)), 2) AS TOT_FRIDAY, [total week], WEEK_HRS - [total week] AS remaining, WEEK_HRS

FROM         (SELECT     time2.*, tot_Monday + tot_Tueday + tot_Wedday + tot_Thuday + tot_Friday AS [total week]

                       FROM          (SELECT     time1.*, mon_morning + mon_evening AS tot_Monday, tue_morning + tue_evening AS tot_Tueday, 

                                                                      wed_morning + wed_evening AS tot_Wedday, thu_morning + thu_evening AS tot_Thuday, 

                                                                      fri_morning + fri_evening AS tot_Friday

                                               FROM          (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, 

                                                                                              LUNCH_OUT_MON) AS Mon_morning, DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, START_TUE, 

                                                                                              LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) AS TUE_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed, 

                                                                                              END_Wed, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, 

                                                                                              END_Wed) AS Wed_Evening, START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu,

                                                                                               LUNCH_OUT_Thu) AS Thu_morning, DATEDIFF(n, LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, START_Fri, 

                                                                                              LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, CONVERT(VARCHAR, '2095', 3) AS WEEK_HRS

                                                                       FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22746264
All I did was add:

select a.*, CAST(unformatted_remaining/ 60 AS varchar(7)) + ':' + RIGHT ('00' + CAST(unformatted_remaining % 60 AS varchar(2)), 2) as remaining
from (
...
...
...
)a

where ... is your existing query.

This allows you to reference the alias of remaining and prevent you from having to do the formula above.  I also renamed remaining as unformatted_Remaining inside the query so that it could be called remaining above.
select a.*, CAST(unformatted_remaining/ 60 AS varchar(7)) + ':' + RIGHT ('00' + CAST(unformatted_remaining % 60 AS varchar(2)), 2) as remaining

from (

SELECT     CAST([total week] / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST([total week] % 60 AS varchar(2)), 2) AS TimeInHours, empid, CONVERT(char(5), 

                      START_MON, 108) AS START_MON, CONVERT(char(5), LUNCH_OUT_MON, 108) AS LUNCH_OUT_MON, CONVERT(char(5), LUNCH_BACK_MON, 108) 

                      AS LUNCH_BACK_MON, CONVERT(char(5), END_MON, 108) AS END_MON, Mon_morning, Mon_Evening, CONVERT(char(5), START_TUE, 108) 

                      AS START_TUE, CONVERT(char(5), LUNCH_OUT_TUE, 108) AS LUNCH_OUT_TUE, CONVERT(CHAR(5), LUNCH_BACK_TUE, 108) AS LUNCH_BACK_TUE, 

                      CONVERT(CHAR(5), END_TUE, 108) AS END_TUE, TUE_morning, TUE_Evening, CONVERT(CHAR(5), START_Wed, 108) AS START_WED, 

                      CONVERT(CHAR(5), LUNCH_OUT_Wed, 108) AS LUNCH_OUT_Wed, CONVERT(CHAR(5), LUNCH_BACK_Wed, 108) AS LUNCH_BACK_WED, 

                      CONVERT(CHAR(5), END_Wed, 108) AS END_Wed, Wed_morning, Wed_Evening, CONVERT(CHAR(5), START_Thu, 108) AS START_THU, 

                      CONVERT(CHAR(5), LUNCH_OUT_Thu, 108) AS LUNCH_OUT_THU, CONVERT(CHAR(5), LUNCH_BACK_Thu, 108) AS LUNCH_BACK_THU, 

                      CONVERT(CHAR(5), END_Thu, 108) AS END_THU, Thu_morning, Thu_Evening, CONVERT(CHAR(5), START_Fri, 108) AS START_FRI, CONVERT(CHAR(5), 

                      LUNCH_OUT_Fri, 108) AS LUNCH_OUT_FRI, CONVERT(CHAR(5), LUNCH_BACK_Fri, 108) AS LUNCH_BACK_FRI, CONVERT(CHAR(5), END_Fri, 108) 

                      AS END_FRI, Fri_morning, Fri_Evening, CAST(tot_Monday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Monday % 60 AS varchar(2)), 2) 

                      AS TOT_MONDAY, CAST(tot_Tueday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Tueday % 60 AS varchar(2)), 2) AS TOT_TUEDAY, 

                      CAST(tot_Wedday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Wedday % 60 AS varchar(2)), 2) AS TOT_WEDDAY, 

                      CAST(tot_Thuday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Thuday % 60 AS varchar(2)), 2) AS TOT_THUDAY, CAST(tot_Friday / 60 AS varchar(7)) 

                      + ':' + RIGHT('00' + CAST(tot_Friday % 60 AS varchar(2)), 2) AS TOT_FRIDAY, [total week], WEEK_HRS - [total week] AS unformatted_remaining, WEEK_HRS

FROM         (SELECT     time2.*, tot_Monday + tot_Tueday + tot_Wedday + tot_Thuday + tot_Friday AS [total week]

                       FROM          (SELECT     time1.*, mon_morning + mon_evening AS tot_Monday, tue_morning + tue_evening AS tot_Tueday, 

                                                                      wed_morning + wed_evening AS tot_Wedday, thu_morning + thu_evening AS tot_Thuday, 

                                                                      fri_morning + fri_evening AS tot_Friday

                                               FROM          (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, 

                                                                                              LUNCH_OUT_MON) AS Mon_morning, DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, START_TUE, 

                                                                                              LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) AS TUE_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed, 

                                                                                              END_Wed, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, 

                                                                                              END_Wed) AS Wed_Evening, START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu,

                                                                                               LUNCH_OUT_Thu) AS Thu_morning, DATEDIFF(n, LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, START_Fri, 

                                                                                              LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning, 

                                                                                              DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, CONVERT(VARCHAR, '2095', 3) AS WEEK_HRS

                                                                       FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime

)a

Open in new window

0
 

Author Closing Comment

by:Camnoc
ID: 31507268
Thank you so much
I can see what I was doing wrong "stupid"
I'm glad I didnt give in to using excel. This is beginning to work.
Wouldnt be posssible without your help.
May need help with grouping   or summing fields keep an eye out.

Thanks again

Paul
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22746853
You should really consider, if you have the option to do so, changing your data model to not store data in a row per week format.  It makes SO much stuff so difficult.
0
 

Author Comment

by:Camnoc
ID: 22749666
Hi Brandon

I couldnt agree more. I started off doing as you said but my boss had other ideas he wanted it in this format. "The boss is always right".
Without your help I wouldnt have got it to this stage.

Thanks again
Paul
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22750061
Don't fool yourself into believing or thinking "the boss is always right".  BUT, if the "BOSS" is a competent person and is capable of providing just reason for doing what they desire to do then it is valid that they would have the last say.
0
 

Author Comment

by:Camnoc
ID: 22750132
Well I happer to be very lucky that my boss fits into the catagory you outlined above, why else would I be struggeling with SQL,ASP and any other script to get this *x**X timesheet finished @ 11:45 on a Saturday night. I still think your approach would have been easier. The concelation is I have learned a great deal. I really need to get around our IT Department to upgrade the server to SQL 2005 , Time is much easier to handle seemingly?.

Paul
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22750247
It's not that time is easier, but you can manipulate data easier.  Using things like common table expressions (which are great for recursion) instead of derived tables make code simpler.
0
 

Author Comment

by:Camnoc
ID: 22750263
Iam going to bed, its 01:40. Its all worth it when things finally work as you want them.
I am sure we will meet again soon.

Thanks for all

Paul
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22750289
I, like all the other experts, will be here for you.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now