December 7, 2016 at 10:51 am
Ok, SQL Server 2012.
I'm new to Over Partition...
Before I would write a select with a minimum value from a child level as follows....
Select ....,
isnull(y.[MinNumber], 0) as [Week01Minimum]
From ParentTable a
left outer join (selecta.[RecordID],
isnull(min(A.[Number]), 0) as [MinNumber]
from[ChildTable] a
where a.[StartDate] between @Week02 and @Week03
group by a.[RecordID]) y on y.[RecordID] = a.[RecordID]
I can get back counts and things doing the following...
select....,
count(nullif(0,(case when a.[StartDate] between @Week02 and @Week03 then 1 else 0 end))) over(partition BY a.RecordID order by a.RecordID ID asc) as [Week01Total],
...
I can't figure out how to get a minimum back as in my original left joined sub select using the over partition - can anyone shed some light on how to do it?
The reason I wanted to use the partition is I need to do it for 52 different weeks in the query.
Is there a performance gain using the partition over or should I just stick with my tried and tested sub select in the left outer join?
Many thanks
Charlotte CB
December 7, 2016 at 12:12 pm
Charlottecb (12/7/2016)
...From ParentTable a
left outer join (selecta.[RecordID],
isnull(min(A.[Number]), 0) as [MinNumber]
from[ChildTable] a
where a.[StartDate] between @Week02 and @Week03
group by a.[RecordID]) y on y.[RecordID] = a.[RecordID]
...
Do you have a calendar table, or something that can help you group the ChildTable StartDate values into weeks? If so you could just group by the ChildTable RecordID and WeekNumber in your subquery, and return the WeekNumber as well. That would probably be easier to write and run than writing 52 different window function expressions.
You could put the values of your variables @WeekXX into a 3 column table:
WeekNumber tinyint,
WeekStartDate Date,
WeekEndDate Date
then joining ChildTable to that should be fairly easy.
December 7, 2016 at 12:28 pm
That's a good idea. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply