SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TSQL Hell - brain teaser
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

2revup
Posting Yak Master

112 Posts

Posted - 02/28/2013 :  23:43:07  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 02/28/2013 :  23:48:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/28/2013 :  23:51:43  Show Profile  Reply with Quote
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 - 03/01/2013 :  00:15:02  Show Profile  Reply with Quote
Chandu your a champion!!!!!!!!
solved!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/01/2013 :  00:27:11  Show Profile  Reply with Quote
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 - 03/01/2013 :  00:37:11  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 03/01/2013 :  01:14:21  Show Profile  Reply with Quote
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 - 03/01/2013 :  01:57:08  Show Profile  Reply with Quote
getting "Incorrect syntax near '21:28:57.000'." tried different variations nothing...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/01/2013 :  02:02:44  Show Profile  Reply with Quote
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 - 03/01/2013 :  02:07:48  Show Profile  Reply with Quote
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

Edited by - 2revup on 03/01/2013 02:10:12
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/01/2013 :  02:16:38  Show Profile  Reply with Quote
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 - 03/01/2013 :  02:29:22  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 03/01/2013 :  03:30:30  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 03/25/2013 :  03:30:45  Show Profile  Reply with Quote
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 - 03/25/2013 :  03:50:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/25/2013 :  04:30:28  Show Profile  Reply with Quote
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 - 03/25/2013 :  06:55:34  Show Profile  Reply with Quote
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 - 03/25/2013 :  07:07:57  Show Profile  Reply with Quote
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.

Edited by - 2revup on 03/25/2013 07:10:10
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/25/2013 :  08:33:53  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 03/25/2013 :  09:44:11  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
411 Posts

Posted - 03/25/2013 :  14:35:36  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000