July 17, 2009 at 5:48 pm
Hi. What a great resource this is.
I have a T-SQL grouping query where i am using a subquery to give me second column.
select FiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3) Mth,
Sum(case when TimeType='YVW Crib' then 0 else Hours end) UnprodHrs,
(select Sum(case when TimeType='YVW Crib' then 0 else Hours end) from P24_tblJobpacExtract
where JobNo in('710102','710103','710107','710108','710109')) TotalHrs
fromP24_tblJobpacExtract
whereleft(costcode,2) in ('61','65') and right(costcode,2) not in('10','99')
Group ByFiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3)
Order ByFiscalMonthDttm
that is giving me the following results:
2008-09-01 00:00:00.000Sep2426.2594428.44
2008-10-01 00:00:00.000Oct2090.2594428.44
2008-11-01 00:00:00.000Nov1739.7594428.44
2008-12-01 00:00:00.000Dec231994428.44
2009-01-01 00:00:00.000Jan131694428.44
2009-02-01 00:00:00.000Feb1452.2594428.44
2009-03-01 00:00:00.000Mar1925.0194428.44
2009-04-01 00:00:00.000Apr228394428.44
2009-05-01 00:00:00.000May1468.594428.44
2009-06-01 00:00:00.000Jun1403.594428.44
My problem is that I need the last column (TotalHrs) to be only the hours for that particular month, not the whole period.
Any help greatly appreciated. Thanks
July 17, 2009 at 11:21 pm
Of course, this hasn't been tested because I don't have the table structures nor the test data... but it should be close...
select FiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3) Mth,
Sum(case when TimeType='YVW Crib' then 0 else Hours end) UnprodHrs,
(select Sum(case when TimeType='YVW Crib' then 0 else Hours end) from P24_tblJobpacExtract t2
where JobNo in('710102','710103','710107','710108','710109')
AND t2.FiscalMonthDTTM = t1.FiscalMonthDTTM) TotalHrs
from P24_tblJobpacExtract t1
where left(costcode,2) in ('61','65') and right(costcode,2) not in('10','99')
Group By FiscalMonthDTTM, left(datename(month,FiscalMonthDTTM),3)
Order By FiscalMonthDttm
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2009 at 3:40 am
Yep, spot on. Thanks a lot Jeff
July 18, 2009 at 3:54 am
Curious, but would this work as well?
select
FiscalMonthDTTM,
left(datename(month,FiscalMonthDTTM),3) Mth,
Sum(case when TimeType='YVW Crib'
then 0
else Hours
end) UnprodHrs,
sum(case when JobNo in ('710102','710103','710107','710108','710109')
then case when TimeType = 'YVW Crib'
then 0
else Hours
end
else 0
end) as TotalHrs
from
P24_tblJobpacExtract
where
left(costcode,2) in ('61','65') and
right(costcode,2) not in('10','99')
Group By
FiscalMonthDTTM,
left(datename(month,FiscalMonthDTTM),3)
Order By
FiscalMonthDttm;
If so, it eliminates the subquery.
July 18, 2009 at 7:28 am
Absolutely, Lynn. Nicely done.
As a side bar, I used to believe that correlated sub-queries were a form of RBAR and, in many cases, they absolutely are and will have the related performance problems. But, if the correlated sub-query has an equality only correlation, they resolve to be something behind the scenes more like what you wrote and actually don't suffer as RBAR. Gail actually turned me on to that fact a ways back and I tested the heck out of it. Now, if you throw in an inequality, all bets are off because you've just built a triangular join.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2009 at 9:02 am
Jeff Moden (7/18/2009)
Now, if you throw in an inequality, all bets are off because you've just built a triangular join.
Inequality or a TOP 1 ... ORDER BY. Both of those force the subquery to be evaluated once per row of the outer query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply