October 9, 2012 at 9:29 am
Afternoon all,
I'm embaressed to say that ive hit a wall with a query that ive been trying to write,
The query has 2 parts to it,
The first part looks a the number of installs that took place within a week
SELECT Wk,[Cust no#], [Job Ref], Routed_Date, Eng
FROM [AVC_Data_Warehouse].[dbo].[test]
where Routed_date between '2012-09-01' and '2012-09-07'
and Job_Type = 'install'
and Real_status = 'COMP'
The second part looks at the number of revisits within the same week
Select Wk,[Cust no#], [Job Ref], Routed_Date, Eng
FROM [AVC_Data_Warehouse].[dbo].[test]
where Routed_date > '2012-09-01'
and Job_Type = 'Service Call'
and Real_status = 'COMP'
I then join them on the Customer Number however the desired outcome is not what i'd like
Select *
from
(
SELECT Wk,[Cust no#], [Job Ref], Routed_Date, Eng
FROM [AVC_Data_Warehouse].[dbo].[test]
where Routed_date between '2012-09-01' and '2012-09-07'
and Job_Type = 'install'
and Real_status = 'COMP'
)a
Left Join
(Select Wk,[Cust no#], [Job Ref], Routed_Date, Eng
FROM [AVC_Data_Warehouse].[dbo].[test]
where Routed_date > '2012-09-01'
and Job_Type = 'Service Call'
and Real_status = 'COMP'
) B on a.[Cust no#] = b.[Cust no#]
So the issue i am having is...
If there are 3 Installs and 1 Revisit, i would like to return the Install where the date to revisit is the smallest, min(datediff(a.routed_date,b.routeddate)
If there is 1 install but three revisits, i would like to return the most recent revisit after the install
Hopefully that makes sense
October 9, 2012 at 9:46 am
Can you post ddl (create table scritps), sample data (insert statements) and desired output based on your sample data? I think once we can see the problem and have something to work with we can help you out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 9:59 am
Thanks for the reply Sean,
Unfortunately due to data protection issues im unable to post any of my data or even samples of...
i have however found a work around,
Thanks for offering to help nonetheless
Waqqas 🙂
October 9, 2012 at 10:15 am
waqqas.zia (10/9/2012)
Thanks for the reply Sean,Unfortunately due to data protection issues im unable to post any of my data or even samples of...
i have however found a work around,
Thanks for offering to help nonetheless
Waqqas 🙂
Understand about posting real data. You can always make up data that simulates your issue to protect the innocent. 😉 Glad you figure it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply