Solved

datediff sql

Posted on 2008-10-17
13
526 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 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