Solved

# datediff sql

Posted on 2008-10-17
521 Views
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
``````
0
Question by:Camnoc
• 7
• 6

LVL 39

Expert Comment

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

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

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

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
``````
0

LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22746264

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
``````
0

Author Closing Comment

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

LVL 39

Expert Comment

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

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

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

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

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

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

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

## Featured Post

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.