Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

datediff sql

Posted on 2008-10-17
13
524 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 28
SQL USE DATABASE VARIABLE 5 27
SQL Recursion 6 16
SQL Find Carriage Return and Delete it. 3 11
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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