?
Solved

datediff sql

Posted on 2008-10-17
13
Medium Priority
?
528 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
[X]
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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 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