Author |
Topic |
2revup
Posting Yak Master
112 Posts |
Posted - 2013-02-28 : 23:43:07
|
Ok I have tsql brain teaser I have 2 tables that I need to do a count on, and those table I need to join and then divide the counts Keep in mind the counts will return multiple rowsData might look like thisUserLogin Case corr avgtest1 15 48 30test2 54 68 1.2So something like this you'd think might work:Select Agent_Login, COUNT(Agent_Login)as cases, userlogin, COUNT(userLogin) as Correspondences, cast(userLogin as float)/ Agent_Login as 'corr_Avg'from CaseDetails_AllReportsinner join Correspondences on Agent_Login=userlogingroup by Agent_Login, userloginBut because the group appears to be executed first it multiples the numbers of the count - this sucksSo I was able to come up with this query to join the tables with out screwing the data, but there is nowhere I can seem to divide the count valuesSELECT ISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin, ISNULL(ca2.cases, 0) as cases, ISNULL(cr2.Correspondence, 0) as Correspondence FROM ( SELECT ca.Agent_Login, COUNT(Agent_Login)as cases FROM CaseDetails_AllReports ca GROUP BY Agent_Login ) ca2 FULL OUTER JOIN ( SELECT cr.userLogin, COUNT(userLogin) as Correspondence FROM Correspondences cr GROUP BY cr.userLogin ) cr2 ON ca2.Agent_Login = cr2.userLoginany gurus ? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-28 : 23:51:43
|
May be this?SELECTISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,ISNULL(ca2.cases, 0) as cases,ISNULL(cr2.Correspondence, 0) as Correspondence,CAST(ISNULL(cases, 0.0) AS FLOAT)/Correspondence AS Corr_AvgFROM(SELECT ca.Agent_Login, COUNT(Agent_Login)as casesFROM CaseDetails_AllReports caGROUP BY Agent_Login) ca2FULL OUTER JOIN(SELECT cr.userLogin, COUNT(userLogin) as CorrespondenceFROM Correspondences crGROUP BY cr.userLogin) cr2 ON ca2.Agent_Login = cr2.userLogin--Chandu |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-01 : 00:15:02
|
Chandu your a champion!!!!!!!!solved! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-01 : 00:27:11
|
quote: Originally posted by 2revup Chandu your a champion!!!!!!!!solved!
Thank you--Chandu |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-01 : 00:37:11
|
One more slight addition if I could be so bold. SELECT ISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin, ISNULL(ca2.cases, 0) as cases, ISNULL(cr2.Correspondence, 0) as Correspondence, CAST(ISNULL(cr2.Correspondence, 0.0) AS FLOAT)/ca2.cases AS Corr_Av FROM ( SELECT ca.Agent_Login, COUNT(Agent_Login)as cases FROM CaseDetails_AllReports ca GROUP BY Agent_Login ) ca2 FULL OUTER JOIN ( SELECT cr.userLogin, COUNT(userLogin) as Correspondence FROM Correspondences cr GROUP BY cr.userLogin ) cr2 ON ca2.Agent_Login = cr2.userLogin where cr.Creation_Date BETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000'Throws this error The multi-part identifier "cr.Creation_Date" could not be bound.the Creation_Date does reside in both tables Correspondences and CaseDetails_AllReports if that at all helps. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 01:14:21
|
it should be SELECTISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,ISNULL(ca2.cases, 0) as cases,ISNULL(cr2.Correspondence, 0) as Correspondence,CAST(ISNULL(cr2.Correspondence, 0.0) AS FLOAT)/ca2.cases AS Corr_AvFROM(SELECT ca.Agent_Login, COUNT(Agent_Login)as casesFROM CaseDetails_AllReports caGROUP BY Agent_Login) ca2FULL OUTER JOIN(SELECT cr.userLogin, COUNT(userLogin) as CorrespondenceFROM Correspondences crGROUP BY cr.userLogin) cr2 ON ca2.Agent_Login = cr2.userLoginAND cr2.Creation_DateBETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000' otherwise it will get reduced to RIGHT JOIN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-01 : 01:57:08
|
getting "Incorrect syntax near '21:28:57.000'." tried different variations nothing... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 02:02:44
|
whats the datatype of Creation_Date?try this tooAND cr2.Creation_Date >= '2013-02-22 21:28:57.000' AND cr2.Creation_Date<= '2013-02-25 21:28:57.000'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-01 : 02:07:48
|
Hmmm got it working was just some of my stupid syntax, but its telling me that the column is invalid "Invalid column name 'Creation_Date'."To answer your question the Creation_Date on both Correspondences and CaseDetails_AllReports is datetime for the datatype.the dates in the query were pulled out directly from the table |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 02:16:38
|
ok got your issue. you dont have it specified in derived tablesSELECTISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,ISNULL(ca2.cases, 0) as cases,ISNULL(cr2.Correspondence, 0) as Correspondence,CAST(ISNULL(cr2.Correspondence, 0.0) AS FLOAT)/ca2.cases AS Corr_AvFROM(SELECT ca.Agent_Login, COUNT(Agent_Login)as casesFROM CaseDetails_AllReports caWHERE ca.Creation_Date BETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000'GROUP BY Agent_Login) ca2FULL OUTER JOIN(SELECT cr.userLogin, COUNT(userLogin) as CorrespondenceFROM Correspondences crWHERE cr.Creation_Date BETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000'GROUP BY cr.userLogin) cr2 ON ca2.Agent_Login = cr2.userLogin ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-01 : 02:29:22
|
Absolutely brilliant!!!!! You have been able to help me have a restful weekend, thank you so very much! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 03:30:30
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-25 : 03:30:45
|
I hate to bring this back up but i need to be able to do get the date outside of each single query so I need to go back to this if at all possible?SELECTISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,ISNULL(ca2.cases, 0) as cases,ISNULL(cr2.Correspondence, 0) as Correspondence,CAST(ISNULL(cr2.Correspondence, 0.0) AS FLOAT)/ca2.cases AS Corr_AvFROM(SELECT ca.Agent_Login, COUNT(Agent_Login)as casesFROM CaseDetails_AllReports caGROUP BY Agent_Login) ca2FULL OUTER JOIN(SELECT cr.userLogin, COUNT(userLogin) as CorrespondenceFROM Correspondences crGROUP BY cr.userLogin) cr2 ON ca2.Agent_Login = cr2.userLoginAND cr2.Creation_DateBETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000' |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-25 : 03:50:44
|
I should add when trying the above I get Invalid column name 'Creation_Date' however the column certainly does exist. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 04:30:28
|
try this once..........(SELECT DISTINCT cr.userLogin, COUNT(userLogin) OVER(PARTITION BY cr.userLogin) as Correspondence, cr.Creation_dateFROM Correspondences cr) cr2--Chandu |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-25 : 06:55:34
|
Thanks bandi, but the group by on that one wont work unfortuantly.Msg 8120, Level 16, State 1, Line 19Column 'Correspondences_all.Creation_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-03-25 : 07:07:57
|
maybe there is a better way to do this, why dont I give you the full story: I have a query that is as below:select Distinct UserLogin, CaseID, Supervisor, Location from Correspondences_All It returns (just a few results below)User1 001255788711 Supervisor1 SYDUser2 001273569661 Supervisor2 SYDuser2 001274285831 Supervisor2 SYDNow if I go and do:select Distinct UserLogin, CaseID, Supervisor, Location, creation_date from Correspondences_All User1 001255788711 Supervisor1 SYD 2013-01-18 16:47:18.000User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000User2 001273569661 Supervisor2 SYD 2013-01-25 18:45:38.000The problem with this is as you can see its got repeating cased ID numbers because of the dates, this will stuff up the count of the caseID's , as I need them to be a unique as you can see I am dividing correspondences by casesID'sSo if I could somehow group the caseID's then that would solve the whole problem, but I just don't see how that is possible with the above. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 08:33:53
|
Do you want COUNT of userLogins regardless of Creation_date? Why you need Creation_date filter in outer query?--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 09:44:11
|
If you want to group them based on CaseIDs obviously you cant show all the date values associated. So you need to tell us which date value you want to show ie earliest, latest or a random one. Based on that you can apply group by over rest of the fields and apply MIN,MAX or any other aggregate function over date field.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-03-25 : 14:35:36
|
quote: Originally posted by 2revup maybe there is a better way to do this, why dont I give you the full story: I have a query that is as below:select Distinct UserLogin, CaseID, Supervisor, Location from Correspondences_All It returns (just a few results below)User1 001255788711 Supervisor1 SYDUser2 001273569661 Supervisor2 SYDuser2 001274285831 Supervisor2 SYDNow if I go and do:select Distinct UserLogin, CaseID, Supervisor, Location, creation_date from Correspondences_All User1 001255788711 Supervisor1 SYD 2013-01-18 16:47:18.000User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000User2 001273569661 Supervisor2 SYD 2013-01-25 18:45:38.000The problem with this is as you can see its got repeating cased ID numbers because of the dates, this will stuff up the count of the caseID's , as I need them to be a unique as you can see I am dividing correspondences by casesID'sSo if I could somehow group the caseID's then that would solve the whole problem, but I just don't see how that is possible with the above.
When you do a COUNT, you can make it a distinct count.For example:SELECT ..., COUNT(DISTINCT caseID) AS CaseID_Count, ...FROM ...GROUP BY ... When you do that, SQL will only count distinct caseID, ignoring duplicate caseIDs for that count. |
|
|
Previous Page&nsp;
Next Page
|