Solved

SQL Queries to work out average between days

Posted on 2008-10-20
33
386 Views
Last Modified: 2012-08-13
I have the below piece of code which displays all the data i need for the next step however i am struggling a bit.  I need SQL to work out the days (excluding weekends)  between T0.[U_IIS_DDDT] to T0.[U_AVL_offered], T0.[U_IIS_DDT] to T0.[U_AVL_offered] and finally T0.[U_IIS_DDDT] to T0.U_iis_plan .  I then need it to work out the average days it takes to book in the appointment.  Basically its so our clients can see how efficient we are at booking/completing works.  Is this something that is feasible to do in SQL?  Thanks in advance for the help

Thanks

Keir
SELECT T0.[callID],T2.[Name],  T0.[U_iis_job], T1.[Name], T0.[createDate],  T0.[U_IIS_DDDT], T0.[U_AVL_offered], T0.U_iis_plan, T0.[U_AVL_NA1], T0.[closeDate] FROM OSCL T0  INNER JOIN OSCP T1 ON T0.problemTyp = T1.prblmTypID INNER JOIN OSCS T2 ON T0.status = T2.statusID WHERE T0.[createDate] >= '[%0]' and  T0.[createDate] <= '[%1]'

Open in new window

0
Comment
Question by:KeirMcCann
  • 18
  • 15
33 Comments
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You said:

0.[U_IIS_DDDT] to T0.[U_AVL_offered], T0.[U_IIS_DDT] to T0.[U_AVL_offered]

first is DDDT second DDT but in the select there is only DDDT. If there is no DDT than why did you mentioned twice the interval.

Can you come with some data example of what you want?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Another question is the WHERE clause, you use >= '[%0]' and <= '[%1]' which would indicate that the dates are strings. Are they?
0
 

Author Comment

by:KeirMcCann
Comment Utility
The dates are strings at the moment but this isnt how we want to work eventually,  the correct T0 is U_IIS_DDT my apologies for the typo's.  This returns the attached file.  

I need it to report the days it takes between (excluding weekends)

Deadline date and closing date
Deadline date and date offered
Deadline date and date planned

And then to work out the average days taken between (excluding weekends)

Deadline date and closing date
Deadline date and date offered
Deadline date and date planned



sql-query.JPG
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
First create this function:
CREATE FUNCTION dbo.fhGetWorkingDaysWithoutWeekEnds

(

	@StartDate datetime,

	@EndDate datetime

)

RETURNS INT

AS

BEGIN
 

	RETURN

		(

			SELECT

			   (DATEDIFF(dd, @StartDate, @EndDate) + 1)

			  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

			  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

			  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

		)
 

END 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
And then use the function in your select statements:
-- wirking days

SELECT 

	T0.[callID],

	T2.[Name],  

	T0.[U_iis_job], 

	T1.[Name], 

	T0.[createDate],  

	T0.[U_IIS_DDT], 

	T0.[U_AVL_offered], 

	T0.[U_iis_plan], 

	T0.[U_AVL_NA1], 

	T0.[closeDate], 

	dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_AVL_offered], 104)) AS DaysToOffer,

	dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_iis_plan], 104))	AS DaysToPlan,

	dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[closeDate], 104))		AS DaysToClose,

FROM 

	OSCL T0  

	INNER JOIN OSCP T1 

		ON T0.problemTyp = T1.prblmTypID 

	INNER JOIN OSCS T2 

		ON T0.status = T2.statusID 

WHERE 

	T0.[createDate] >= '[%0]' 

	AND  T0.[createDate] <= '[%1]'
 
 

-- calculate average days

SELECT 

	AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_AVL_offered], 104)))	AS AVGDaysToOffer,

	AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_iis_plan], 104)))		AS AVGDaysToPlan,

	AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[closeDate], 104)))		AS AVGDaysToClose,

FROM 

	OSCL T0  

	INNER JOIN OSCP T1 

		ON T0.problemTyp = T1.prblmTypID 

	INNER JOIN OSCS T2 

		ON T0.status = T2.statusID 

WHERE 

	T0.[createDate] >= '[%0]' 

	AND  T0.[createDate] <= '[%1]'

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
thanks for the info so far, how can i read up on creating a function in SQL?  Its not something i have ever done unfortunately
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I am not sure about your question. You create a function by running the SQL code I gave you for that in the database you need it.
0
 

Author Comment

by:KeirMcCann
Comment Utility
ahh i see now thanks

i get the following error on the 2nd code

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'FROM'.

does this generally mean a commor is missing??

Many many thanks

Keir
0
 

Author Comment

by:KeirMcCann
Comment Utility
got around those however this is the error i receive now:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U_IIS_DDT'.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Run the script with the function first to create it and the run the script bellow, there were some extra comas there. Make sure that you run the entire script and not just a selection of it, most likely what you did when you got the last error.
-- wirking days

SELECT 

        T0.[callID],

        T2.[Name],  

        T0.[U_iis_job], 

        T1.[Name], 

        T0.[createDate],  

        T0.[U_IIS_DDT], 

        T0.[U_AVL_offered], 

        T0.[U_iis_plan], 

        T0.[U_AVL_NA1], 

        T0.[closeDate], 

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_AVL_offered], 104))	AS DaysToOffer,

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_iis_plan], 104))    AS DaysToPlan,

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[closeDate], 104))		AS DaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

 

 

-- calculate average days

SELECT 

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_AVL_offered], 104)))    AS AVGDaysToOffer,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[U_iis_plan], 104)))		AS AVGDaysToPlan,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, T0.[U_IIS_DDT], 104), CONVERT(DATETIME, T0.[closeDate], 104)))		AS AVGDaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
thanks for your time again

the latest error i get is

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
This is because you have the dates stored as strings and empty strings generate error when converted to dates. Hopefully you don't have some unrecognize formats as in picture I saw DD.MM.YY format and that's why I used 104 converting code. It iis not recommendable to use strings for dates and you should change them to datetime columns if possible. Try now:
-- wirking days

SELECT 

        T0.[callID],

        T2.[Name],  

        T0.[U_iis_job], 

        T1.[Name], 

        T0.[createDate],  

        T0.[U_IIS_DDT], 

        T0.[U_AVL_offered], 

        T0.[U_iis_plan], 

        T0.[U_AVL_NA1], 

        T0.[closeDate], 

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_AVL_offered])),''), 104))	AS DaysToOffer,

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_iis_plan])),''), 104))    AS DaysToPlan,

        dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[closeDate])),''), 104))		AS DaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

 

 

-- calculate average days

SELECT 

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_AVL_offered])),''), 104)))    AS AVGDaysToOffer,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_iis_plan])),''), 104)))		AS AVGDaysToPlan,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[U_IIS_DDT])),''), 104), CONVERT(DATETIME, NULLIF(ltrim(rtrim(T0.[closeDate])),''), 104)))		AS AVGDaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
Thanks for your patience Zberteoc, i know i can be difficult to understand sometimes!

Could you remove the dates so it shows every record then? all our dates are DD.MM.YY !

Thanks again

Keir
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I am not sure I understand the question. What dates to remove? Did the last query work?
0
 

Author Comment

by:KeirMcCann
Comment Utility
If you could remove the strings from the query and it could retrieve all records rather than a selection

The error i got with this one was :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U_IIS_DDDT'.

Many thanks
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Thewre is no such sequence in my query: U_IIS_DDDT (3 Ds, olny 2Ds)

Maybe you run it again having the U_IIS_DDDT selected

Make sure that you either select the whole query or that you don't have anything selected and run it.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:KeirMcCann
Comment Utility
Hi sorry,

The table is DDDT i made the mistake in the 2nd post sorry and replaced the code you have written.  

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

Can we remove the string completely?


0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
What happens is this:

The strings are in the table in the date columns:

        [U_IIS_DDT],
        [U_AVL_offered],
        [U_iis_plan],
        [closeDate]

so a date like today is stored as string '20.10.08'. When we use datetime functions like datediff we need to use datetime values or strings that can be by default converted to datetime values. If the string is in a date format that is not recognize it will fail eith that conversion error. Now, that can come fro an empty string, I see in the picture that in the Date Offered column you only have 1 value and the rest are empty, or becasue you have string values for dates that are not recognized as dates.

So we cannot remove the strings as they are your dates data. What you could do is to replacea the column to ones that are of datetime type.

For now we have to check why the error because I eliminated the empty string issue so it means that you might have wrong date formats in there.

0
 

Author Comment

by:KeirMcCann
Comment Utility
I see a lot clearer now thanks, well historically these date strings have been used for around a year and probably encompass around 4000 records.  I dont think it is possible to enter an incorrect date within the SAP system so i would be suprised if there were any errors within the date strings however i can test tommorow if any incorrect formats could be possibly be entered.  By replacing the columns i presume that is new data that would need to be made as it doesnt currently exist?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
If the query below returns any rows they need to be fixed. Those will be dates that are not empty but are not in a valid date format.
select 

        [U_IIS_DDT], 

        [U_AVL_offered], 

        [U_iis_plan], 

        [closeDate]

from 

	OSCL

where

	( 

        ltrim(rtrim([U_IIS_DDT]))<>'' or 

        ltrim(rtrim([U_AVL_offered]))<>'' or 

        ltrim(rtrim([U_iis_plan]))<>'' or 

        ltrim(rtrim([closeDate]))<>''

	)

		and 

	(

        isdate([U_IIS_DDT])<>1 or 

        isdate([U_AVL_offered])<>1 or 

        isdate([U_iis_plan])<>1 or 

        isdate([closeDate])<>1

	)

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
hey it returned 4000 records lol :)

2008-10-24 00:00:00.000      NULL      2008-10-20 00:00:00.000      NULL
2008-11-05 00:00:00.000      NULL      2008-10-29 00:00:00.000      NULL
2008-11-05 00:00:00.000      NULL      2008-10-16 00:00:00.000      NULL
2008-11-05 00:00:00.000      NULL      2008-10-16 00:00:00.000      NULL
2008-11-05 00:00:00.000      NULL      NULL      2008-10-16 00:00:00.000
2008-11-05 00:00:00.000      NULL      2008-10-21 00:00:00.000      NULL
2008-11-05 00:00:00.000      NULL      2008-10-27 00:00:00.000      NULL

is the general format. Seems to change them to YYYY.MM.DD?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Can you show me the table structure for these 4 columns? Are you sure they are strings and not datetime? What is the software you used to pull out the result form the picture?
0
 

Author Comment

by:KeirMcCann
Comment Utility
table structure? how would i find that out?

The program to get the results was SAP Business One query generator, basically it just runs sql code.

I have no idea if they are strings or datetimes to be honest i am pretty new the furthest i have got in sql was generating the initial query.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Run:
EXEC sp_columns 'OSCL'

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
they all have datetime next to them, so i guess thats what they are?

thanks

keir
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Ok, than is simple. Wee need to get rid of all the converts and string manipulations:
-- wirking days

SELECT 

        T0.[callID],

        T2.[Name],  

        T0.[U_iis_job], 

        T1.[Name], 

        T0.[createDate],  

        T0.[U_IIS_DDT], 

        T0.[U_AVL_offered], 

        T0.[U_iis_plan], 

        T0.[U_AVL_NA1], 

        T0.[closeDate], 

        dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], [U_AVL_offered])	AS DaysToOffer,

        dbo.fhGetWorkingDaysWithoutWeekEnds([U_IIS_DDT], [U_iis_plan])			AS DaysToPlan,

        dbo.fhGetWorkingDaysWithoutWeekEnds([U_IIS_DDT], [closeDate])			AS DaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

 

 

-- calculate average days

SELECT 

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], [U_AVL_offered]))    AS AVGDaysToOffer,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds([U_IIS_DDT], [U_iis_plan]))			AS AVGDaysToPlan,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds([U_IIS_DDT], [closeDate]))		AS AVGDaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Sorry, I omitted the aliases:
-- working days

SELECT 

        T0.[callID],

        T2.[Name],  

        T0.[U_iis_job], 

        T1.[Name], 

        T0.[createDate],  

        T0.[U_IIS_DDT], 

        T0.[U_AVL_offered], 

        T0.[U_iis_plan], 

        T0.[U_AVL_NA1], 

        T0.[closeDate], 

        dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[U_AVL_offered])		AS DaysToOffer,

        dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[U_iis_plan])		AS DaysToPlan,

        dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[closeDate])			AS DaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

 

 

-- calculate average working days

SELECT 

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[U_AVL_offered]))    AS AVGDaysToOffer,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[U_iis_plan]))		AS AVGDaysToPlan,

        AVG(dbo.fhGetWorkingDaysWithoutWeekEnds(T0.[U_IIS_DDT], T0.[closeDate]))		AS AVGDaysToClose

FROM 

        OSCL T0  

        INNER JOIN OSCP T1 

                ON T0.problemTyp = T1.prblmTypID 

        INNER JOIN OSCS T2 

                ON T0.status = T2.statusID 

WHERE 

        T0.[createDate] >= '[%0]' 

        AND  T0.[createDate] <= '[%1]'

Open in new window

0
 

Author Comment

by:KeirMcCann
Comment Utility
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U_IIS_DDT'.



0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
don't select that again!
run the last code i sent you.
0
 

Author Comment

by:KeirMcCann
Comment Utility
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

i wish i was more help!
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
Oooh, get rid of the WHERE clause, that's generating the error becasue you compare dates with strings that are not dates.! :o))))
0
 

Author Comment

by:KeirMcCann
Comment Utility
omgosh it works! I love you man! Thanks this should brighten up my Monday morning!!!

Thanks for your patience i am not the best at explaining things!!!! Have a beer on me please!!!!

Thankssss man!
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Finally! :o)

Glad I could help.

Cheers!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

6 Experts available now in Live!

Get 1:1 Help Now