July 15, 2009 at 7:39 am
I am trying to join together two tables, one with user information, one with calls. I'll put the query below. The query is cross joined with a filters table that basically limits the results to a set of criteria. Basically I am looking for all the people from a certain division with a certain job classification, and then I am trying to find the last call they had for that division and classification. The problem I am having is if they have never had a call in a certain division and classification, then they don't show up in the join, even if they are in it now, they don't show up in the join. I'm not sure how to fix it. The ugly SQL isn't helping. If anybody is bored and wants to take a glance at it, I would love the assistance.
SELECT DISTINCT
TOP 100 PERCENT E.Emp_HireDate, E.Emp_Dist, E.Emp_Dept, E.Emp_ID, E.FullName,
E.Emp_ClassName, E.Emp_Phone1, E.Emp_Phone2,
MAX(C.CallDate) AS MaxOfCallDate
FROM dbo.SECO_VIEW_EmpList AS E LEFT OUTER JOIN
dbo.SECO_VIEW_CentralCallOuts AS C ON E.Emp_ID = C.Emp_ID CROSS JOIN
dbo.tblDLCentralFilters AS F
GROUP BY E.Emp_HireDate, E.Emp_Dist, E.Emp_Dept, E.Emp_ID, E.FullName, E.Emp_ClassName, E.Emp_Phone1,
E.Emp_Phone2, F.CentralDNoF,
F.CentralStores, F.CentralDENG, F.CentralDFS, F.CentralDOPS, F.CentralDPH,
F.CentralDRET, F.CentralDSE, F.CentralDWH, F.CentralCNoF,
F.CentralC1, F.CentralC2, F.CentralC3, F.CentralCLH, F.CentralCF1, F.CentralCF2,
F.CentralCL1, F.CentralCMT1, F.CentralCNA, F.CentralCRE,
F.CentralCS1, F.CentralCST, F.CentralCSU, F.CentralCSV, F.CentralCT1,
F.CentralCT2, F.CentralCW1, C.CallDist, C.Emp_Class
HAVING (E.Emp_Dist LIKE (CASE WHEN F.CentralDNoF = 0 THEN '' ELSE '%' END) AND
C.CallDist LIKE (CASE WHEN F.CentralDNoF = 0 THEN '' ELSE '%' END) OR
E.Emp_Dist = (CASE WHEN F.CentralStores = 0 THEN 'CEN' ELSE '' END) AND
C.CallDist = (CASE WHEN F.CentralStores = 0 THEN 'CEN' ELSE '' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDENG = 0 THEN '' ELSE 'ENG' END) AND
C.CallDist = (CASE WHEN F.CentralDENG = 0 THEN '' ELSE 'ENG' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDFS = 0 THEN '' ELSE 'FS' END)
AND C.CallDist = (CASE WHEN F.CentralDFS = 0 THEN '' ELSE 'FS' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDOPS = 0 THEN '' ELSE 'OPS' END)
AND C.CallDist = (CASE WHEN F.CentralDOPS = 0 THEN '' ELSE 'OPS' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDPH = 0 THEN '' ELSE 'PH' END)
AND C.CallDist = (CASE WHEN F.CentralDPH = 0 THEN '' ELSE 'PH' END) OR
E.Emp_Dist LIKE (CASE WHEN F.CentralDRET = 0 THEN '' ELSE 'RE%' END) AND
C.CallDist LIKE (CASE WHEN F.CentralDRET = 0 THEN '' ELSE 'RE%' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDSE = 0 THEN '' ELSE 'SE' END)
AND C.CallDist = (CASE WHEN F.CentralDSE = 0 THEN '' ELSE 'SE' END) OR
E.Emp_Dist = (CASE WHEN F.CentralDWH = 0 THEN '' ELSE 'WH' END)
AND C.CallDist = (CASE WHEN F.CentralDWH = 0 THEN '' ELSE 'WH' END))
AND (E.Emp_Dist <> 'NOR') AND (E.Emp_Dist <> 'EST') AND (E.Emp_Dist <> 'GDO')
AND (E.Emp_Dist <> 'EDO') AND (C.CallDist <> 'NOR') AND
(C.CallDist <> 'EST') AND (C.CallDist <> 'GDO') AND (C.CallDist <> 'EDO') AND
(E.Emp_ClassName LIKE (CASE WHEN F.CentralCNoF = 0 THEN '' ELSE '%' END) AND
C.Emp_Class LIKE (CASE WHEN F.CentralCNoF = 0 THEN '' ELSE '%' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralC1 = 0 THEN '' ELSE '1' END)
AND C.Emp_Class = (CASE WHEN F.CentralC1 = 0 THEN '' ELSE '1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralC2 = 0 THEN '' ELSE '2' END)
AND C.Emp_Class = (CASE WHEN F.CentralC2 = 0 THEN '' ELSE '2' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralC3 = 0 THEN '' ELSE '3' END)
AND C.Emp_Class = (CASE WHEN F.CentralC3 = 0 THEN '' ELSE '3' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCLH = 0 THEN '' ELSE 'LH' END) AND
C.Emp_Class = (CASE WHEN F.CentralCLH = 0 THEN '' ELSE 'LH' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCF1 = 0 THEN '' ELSE 'F1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCF1 = 0 THEN '' ELSE 'F1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCF2 = 0 THEN '' ELSE 'F2' END) AND
C.Emp_Class = (CASE WHEN F.CentralCF2 = 0 THEN '' ELSE 'F2' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCL1 = 0 THEN '' ELSE 'L1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCL1 = 0 THEN '' ELSE 'L1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCMT1 = 0 THEN '' ELSE 'MT1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCMT1 = 0 THEN '' ELSE 'MT1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCNA = 0 THEN '' ELSE 'NA' END) AND
C.Emp_Class = (CASE WHEN F.CentralCNA = 0 THEN '' ELSE 'NA' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCRE = 0 THEN '' ELSE 'RE' END) AND
C.Emp_Class = (CASE WHEN F.CentralCRE = 0 THEN '' ELSE 'RE' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCS1 = 0 THEN '' ELSE 'S1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCS1 = 0 THEN '' ELSE 'S1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCST = 0 THEN '' ELSE 'ST' END) AND
C.Emp_Class = (CASE WHEN F.CentralCST = 0 THEN '' ELSE 'ST' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCSU = 0 THEN '' ELSE 'SU' END) AND
C.Emp_Class = (CASE WHEN F.CentralCSU = 0 THEN '' ELSE 'SU' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCSV = 0 THEN '' ELSE 'SV' END) AND
C.Emp_Class = (CASE WHEN F.CentralCSV = 0 THEN '' ELSE 'SV' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCT1 = 0 THEN '' ELSE 'T1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCT1 = 0 THEN '' ELSE 'T1' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCT2 = 0 THEN '' ELSE 'T2' END) AND
C.Emp_Class = (CASE WHEN F.CentralCT2 = 0 THEN '' ELSE 'T2' END) OR
E.Emp_ClassName = (CASE WHEN F.CentralCW1 = 0 THEN '' ELSE 'W1' END) AND
C.Emp_Class = (CASE WHEN F.CentralCW1 = 0 THEN '' ELSE 'W1' END))
AND (MAX(C.CallDate) >= GETDATE() -
(SELECT CentralAge
FROM dbo.tblDLRecordAge) OR
MAX(C.CallDate) IS NULL)
ORDER BY MAX(C.CallDate) DESC
July 15, 2009 at 8:55 am
Can you post table definitions, some sample data, and the expected/desired results from the sample data? The first article linked in my signature explains how to do this and some of the why. You've gotten several views, but no posts to this probably because you have not included the information I am asking for.
A first step would probably be to break the query down into steps. I'd start by eliminating the cross join on filters to just get the employees and last call data, then I'd work on applying the filters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2009 at 8:56 am
Could you provide the table definitions and some sample data, now it's very hard to understand what you actually want.
It think if you would put your whole like structure in some kind of temporary table, would simplify the join.
Maybe consider using the over clause with the max then you won't need a group by on all columns.
July 15, 2009 at 8:59 am
Greetngs Sean,
I had to make a few guesses about what you were trying to do with the SQL code but I do have a script that you could try to see if it works for you. I put some comments in it too to let you know what I was thinking when I wrote it.
/*
First, I built the CentralFilters table selection. It would be the most costly process wise.
Also, by building it first, the interpretation of it's data happens only once, not for each record.
I did change one of the case statements as it appeared backwards from all the others:
(CASE WHEN F.CentralStores = 0 THEN 'CEN' ELSE '' END) was changed to:
(CASE WHEN F.CentralStores = 0 THEN '' ELSE 'CEN' END)
If this was wrong, then you can change it back to be 'CEN' when this value is 0.
It appears that you were checking before on both the employee and call both needed to have the same Classification or Division
in order to be selected. From what you had stated in your posting, it appears that the filter is based on where the employee
has for their Classification or Division. You do not say if the Employee can take calls outside of their Classification or
Division. Since they appear to need to be equal, then a selection based in Employee only should still let the filter work.
The filter is JOINed to EmpList to reduce EmpList to only the records that meet your criteria.
The one section that limits some Divisions to not be returned was changed to be a NOT IN () clause to make it smaller
and easier to see that it functions a bit separately from the other filters.
If all you want is the latest CallOut record, then you can get that probably by just sorting the calls in Descending CallDate
Order. Then get the first record only. This can be done by OUTER APPLYing the SELECT statement to the specific EmpList record and
filtering that based on both the Dist and Class being equal.
I am not sure where the GETDATE() - (SELECT CentralAge FROM dbo.tblDLRecordAge) fits in for your filtering criteria or
if it really was needed. If it is, then it should be easy to plug it back in.
I don't have your databases so this rewrite is basically off the cuff. That said, there could easily be some syntax
or logic errors in this statement.
*/
SELECT
E.Emp_HireDate,
E.Emp_Dist,
E.Emp_Dept,
E.Emp_ID,
E.FullName,
E.Emp_ClassName,
E.Emp_Phone1,
E.Emp_Phone2,
C2.CallDate
FROM dbo.SECO_VIEW_EmpList AS E
JOIN
(
SELECT
(
CASE
WHEN CentralDNoF 0 THEN '%'
WHEN CentralStores 0 THEN 'CEN'
WHEN CentralDENG 0 THEN 'ENG'
WHEN CentralDFS 0 THEN 'FS'
WHEN CentralDOPS 0 THEN 'OPS'
WHEN CentralDPH 0 THEN 'PH'
WHEN CentralDRET 0 THEN 'RE%'
WHEN CentralDSE 0 THEN 'SE'
WHEN CentralDWH 0 THEN 'WH'
END
) AS F_Dist,
(
WHEN CentralCNoF 0 THEN '%'
WHEN CentralC1 0 THEN '1'
WHEN CentralC2 0 THEN '2'
WHEN CentralC3 0 THEN '3'
WHEN CentralCLH 0 THEN 'LH'
WHEN CentralCF1 0 THEN 'F1'
WHEN CentralCF2 0 THEN 'F2'
WHEN CentralCL1 0 THEN 'L1'
WHEN CentralCMT1 0 THEN 'MT1'
WHEN CentralCNA 0 THEN 'NA'
WHEN CentralCRE 0 THEN 'RE'
WHEN CentralCS1 0 THEN 'S1'
WHEN CentralCST 0 THEN 'ST'
WHEN CentralCSU 0 THEN 'SU'
WHEN CentralCSV 0 THEN 'SV'
WHEN CentralCT1 0 THEN 'T1'
WHEN CentralCT2 0 THEN 'T2'
WHEN CentralCW1 0 THEN 'W1'
) AS F_Class
FROM dbo.tblDLCentralFilters
) AS F ON E.Emp_Dist LIKE F.F_Dist AND E.Emp_ClassName LIKE F_Class
OUTER APPLY
(
SELECT TOP(1)
C.Emp_ClassName,
C.CallDist,
C.CallDate
FROM dbo.SECO_VIEW_CentralCallOuts AS C
WHERE
E.Emp_ID = C.Emp_ID
AND E.Emp_ClassName = C.Emp_Class
AND E.Emp_Dist = C.CallDist
ORDER BY CallDate DESC
) AS C2
WHERE
E.Emp_Dist NOT IN('NOR', 'EST', 'GDO', 'EDO')
I hope that this helps you find what you are looking for, or gives you an idea of what extra information is needed to be given to help refine what you really need.
Have a good day.
Terry Steadman
July 15, 2009 at 9:21 am
It appears that you were checking before on both the employee and call both needed to have the same Classification or Division
in order to be selected. From what you had stated in your posting, it appears that the filter is based on where the employee
has for their Classification or Division. You do not say if the Employee can take calls outside of their Classification or
Division. Since they appear to need to be equal, then a selection based in Employee only should still let the filter work.
This is actually where the problem came up. Originally the calls were only in the same division and classification as the employee table. Now they've changed it such that they will switch the employee's division and classification, make a call, and then switch him back. So I had to add the checks on the calls for division and classification. This is used to make a rotational list on who they have to call out next basically, and the lists are specific to division and classification, so any call in one classification doesn't count toward the other. But if they have never had a call in a classification, it doesn't show up on the list now.
This was all written years ago in Access by an employee who left. There are actually about a dozen views stacked on top of this for business rules. I went through and translated it, but I really need to rewrite the whole thing one day.
Let me take a look at what you wrote, thansk for the help.
July 15, 2009 at 9:35 am
This is actually where the problem came up. Originally the calls were only in the same division and classification as the employee table. Now they've changed it such that they will switch the employee's division and classification, make a call, and then switch him back. So I had to add the checks on the calls for division and classification. This is used to make a rotational list on who they have to call out next basically, and the lists are specific to division and classification, so any call in one classification doesn't count toward the other. But if they have never had a call in a classification, it doesn't show up on the list now.
So, does this mean there is only 1 employee record per employee or is there multiple employee records based on which classifications and divisions they have been put in to?
If there is just 1 employee record, then you could switch the filter to work against the CallList instead of the EmpList.
This was all written years ago in Access by an employee who left. There are actually about a dozen views stacked on top of this for business rules. I went through and translated it, but I really need to rewrite the whole thing one day.
Good luck. A rewrite may take a lot of time and effort to first find and document the actual business rules. But, in the end, will be worth it. Especially if you write the new SQL from the ground up based only on the rules and not the old way the code was written.
Have a good day.
Terry Steadman
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply