Solved

SQL Queries to work out average between days

Posted on 2008-10-20
33
391 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
ID: 22757650
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
ID: 22757681
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
ID: 22757830
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 22758351
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
ID: 22758404
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
ID: 22758460
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
ID: 22758484
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
ID: 22758523
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
ID: 22758581
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
ID: 22759415
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
ID: 22760310
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
ID: 22760519
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
ID: 22760745
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
ID: 22760828
I am not sure I understand the question. What dates to remove? Did the last query work?
0
 

Author Comment

by:KeirMcCann
ID: 22760854
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
ID: 22761011
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
 

Author Comment

by:KeirMcCann
ID: 22761174
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
ID: 22761498
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
ID: 22761610
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
ID: 22761834
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
ID: 22761879
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
ID: 22762032
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
ID: 22762086
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
ID: 22762231
Run:
EXEC sp_columns 'OSCL'

Open in new window

0
 

Author Comment

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

thanks

keir
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22762399
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
ID: 22762416
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
ID: 22762437
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'U_IIS_DDT'.



0
 
LVL 26

Expert Comment

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

Author Comment

by:KeirMcCann
ID: 22762510
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
ID: 22762552
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
ID: 22762572
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
ID: 22762585
Finally! :o)

Glad I could help.

Cheers!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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