Left joining and only returning first row from t2

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 2005 Forums

Transact-SQL (2005)

Left joining and only returning first row from t2

Author

Topic

portrman
Starting Member

24 Posts

Posted - 2009-06-17 : 13:03:36

I have two tables with the following columns

Case: caseid
Task: whatid(maps to caseid), createdDate, time

A simple left join of these tables would be:
SELECT * FROM [Case] c LEFT JOIN Task t ON t.WhatId = c.CaseId

However, what I need is more complex. For each Case there are multiple Tasks. Each task has a Date/Time it was recorded and the amount of Time (in hours, so int) spent on the task. A task occurs on one and one day only, but a case may have tasks that occur in different months.

What I need is to determine for each Case, what month was the most expensive Time wise, then return a sum of all tasks for the case as if they occurred in that month. Here is what I have so far.

SELECT
a.WhatId,
MAX(a.TotalTime) MaxTime
FROM
(
SELECT
YEAR(t.CreatedDate) as CurrYear, MONTH(t.CreatedDate) as CurrMonth, t.WhatId, sum(t.Time) as TotalTime
FROM
[Case] c
LEFT JOIN Task t ON t.WhatId = c.CaseId
WHERE t.Time IS NOT NULL
GROUP BY YEAR(t.CreatedDate), MONTH(t.CreatedDate), t.WhatId
) AS A
GROUP BY a.WhatId

The inner select is going to sum up all the task per case per month, then I use the outter select to determine for each case, which month was the most expensive. All that seems to work well. The next step is I need to year/month data and that's where I'm having trouble with the join.

SELECT
*
FROM
(
SELECT
a.WhatId,
MAX(a.TotalTime) MaxTime
FROM
(
SELECT
YEAR(t.CreatedDate) as CurrYear, MONTH(t.CreatedDate) as CurrMonth, t.WhatId, sum(t.Time) as TotalTime
FROM
[Case] c
LEFT JOIN Task t ON t.WhatId = c.CaseId
WHERE t.Time IS NOT NULL
GROUP BY YEAR(t.CreatedDate), MONTH(t.CreatedDate), t.WhatId
) AS A
GROUP BY a.WhatId
) AS CaseMax

LEFT JOIN
(
SELECT
YEAR(t.CreatedDate) as CurrYear, MONTH(t.CreatedDate) as CurrMonth, t.WhatId, sum(t.Time) as TotalTime
FROM
[Case] c
LEFT JOIN Task t ON t.WhatId = c.CaseId
WHERE t.Time IS NOT NULL
GROUP BY YEAR(t.CreatedDate), MONTH(t.CreatedDate), t.WhatId
) AS CaseInfo ON
CaseMax.WhatId = CaseInfo.WhatId
AND CaseMax.MaxTime = CaseInfo.TotalTime

The problem is, for a specific case, the max time may happen in more than one month. In month1: 5hours, month2: 2hours, month3: 5hours. So with this second larger sql statement, I'll get 2 months showing as the max month. It doesn't matter which maxmonth 'wins' in the end, but I need it ot return just one of them. Then the next step will be to determine how much time was spent overall for the case, something I haven't gotten to with the above statement.

In the end, I want something like

CaseId(or WhatId) - Year - Month(same column or seperate is fine) - TotalCaseTime

It's important that in the end I have the caseId because I'll need to join into other tables to determine various bits of information that are import. So yes, more joins will be happening.

I don't need a performant query, but I do need a query and need to try and stay away from cursors, I can't use a storedProc. I was trying to use HAVING or DISTINCT but having no luck. I saw some examples where a CTE was used but I don't understand them well enough.

Anyone willing to take a crack at this crazy one? Thx!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 13:16:48

[code]SELECT c.CaseId,t2.TotalcaseTime,YEAR(t.Date) AS Year,MONTH(t.Date) AS Month,t.Hours
FROM Case c
LEFT JOIN (SELECT whatid,SUM(time) AS TotalCaseTime
FROM Task
GROUP BY whatid) t2
ON t2.whatid =c.CaseId
OUTER APPLY (SELECT TOP 1 DATEADD(mm,DATEDIFF(mm,0,createdDate),0) AS Date,SUM(time) AS Hours
FROM Task
WHERE whatid=c.CaseId
GROUP BY whatid,DATEADD(mm,DATEDIFF(mm,0,createdDate),0)
ORDER BY Hours DESC)t
[/code]

portrman
Starting Member

24 Posts

Posted - 2009-06-17 : 13:27:08

Wow! Thanks visakh16. Never heard of outer apply but it looks really cool. I've never seen that dateadd/datediff pattern before, will have see exactly how your using that. /me goes off to read...

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 13:29:15

welcome

feel free ask if you've any doubt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources