Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TSQL Hell - brain teaser

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 rows

Data might look like this
UserLogin Case corr avg
test1 15 48 30
test2 54 68 1.2

So 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_AllReports
inner join Correspondences on Agent_Login=userlogin
group by Agent_Login, userlogin

But because the group appears to be executed first it multiples the numbers of the count - this sucks

So 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 values

SELECT
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.userLogin


any gurus ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:48:20
post proper data with expected output. Its quite hard from your post above on what exactly you're trying to get

see link below to understand way to post a question with easy "consumable" data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-28 : 23:51:43
May be this?

SELECT
ISNULL(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_Avg

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


--
Chandu
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-01 : 00:15:02
Chandu your a champion!!!!!!!!
solved!
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 01:14:21
it should be

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
AND cr2.Creation_Date
BETWEEN '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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 02:02:44
whats the datatype of Creation_Date?

try this too

AND cr2.Creation_Date >= '2013-02-22 21:28:57.000'
AND cr2.Creation_Date<= '2013-02-25 21:28:57.000'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 tables


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
WHERE ca.Creation_Date BETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000'
GROUP BY Agent_Login
) ca2
FULL OUTER JOIN
(
SELECT cr.userLogin, COUNT(userLogin) as Correspondence

FROM Correspondences cr
WHERE 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 03:30:30
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
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
AND cr2.Creation_Date
BETWEEN '2013-02-22 21:28:57.000' AND '2013-02-25 21:28:57.000'
Go to Top of Page

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.
Go to Top of Page

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_date
FROM Correspondences cr
) cr2

--
Chandu
Go to Top of Page

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




Go to Top of Page

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 SYD
User2 001273569661 Supervisor2 SYD
user2 001274285831 Supervisor2 SYD

Now 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.000
User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:45:38.000

The 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's

So 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.
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 SYD
User2 001273569661 Supervisor2 SYD
user2 001274285831 Supervisor2 SYD

Now 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.000
User1 001273569661 Supervisor1 SYD 2013-01-23 17:18:39.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:13:59.000
User2 001273569661 Supervisor2 SYD 2013-01-25 18:45:38.000

The 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's

So 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.
Go to Top of Page
    Next Page

- Advertisement -