Start Free Trial

asked on

SQL Queries to work out average between days

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

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?

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

ASKER

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

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

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

ASKER

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

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.

ASKER

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

ASKER

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

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

ASKER

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.

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

ASKER

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

I am not sure I understand the question. What dates to remove? Did the last query work?

ASKER

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

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.

ASKER

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?

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.

ASKER

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?

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

ASKER

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?

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?

ASKER

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.

Run:

EXEC sp_columns 'OSCL'

Open in new window

ASKER

they all have datetime next to them, so i guess thats what they are?

thanks

keir

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

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

ASKER

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

don't select that again!
run the last code i sent you.

ASKER

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

i wish i was more help!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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!

Finally! :o)

Glad I could help.

Cheers!