November 3, 2008 at 7:57 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 6:06 am
Any chance you feel like translating for 2000? I have the same issue, although I'm playing with diagnoses instead of specialty, but I can convert that part.
Like the OP, I have it working using a loop, but don't like that I haven't had time to figure something else out.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 4, 2008 at 12:21 pm
I am not in aposition to test it, but this should be cloes:
Select V.[Vendor ID], CY.TotalBags as [Bags Last Year], LY.TotalBags as [Bags This Year]
From Vendors V
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as Bags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) CY on V.[Vendor ID] = CY.[Vendor] and CY.Yr = DatePart("Year", Getdate())
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as Bags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) LY on V.[Vendor ID] = LY.[Vendor] and LY.Yr = (DatePart("Year", Getdate())-1)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 6, 2008 at 3:42 pm
rbarryyoung (11/4/2008)
I am not in aposition to test it, but this should be cloes:
Select V.[Vendor ID], CY.TotalBags as [Bags Last Year], LY.TotalBags as [Bags This Year]
From Vendors V
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as Bags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) CY on V.[Vendor ID] = CY.[Vendor] and CY.Yr = DatePart("Year", Getdate())
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as Bags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) LY on V.[Vendor ID] = LY.[Vendor] and LY.Yr = (DatePart("Year", Getdate())-1)
Thanks Rbar, hadn't seen that trick before. Couple misnamed columns from outer to inner queries, nothing big. Here:
Select V.[Vendor ID], CY.TotalBags as [Bags Last Year], LY.TotalBags as [Bags This Year]
From Vendors V
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as TotalBags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) CY on V.[Vendor ID] = CY.[Vendor ID] and CY.Yr = DatePart("Year", Getdate())
Left Join (Select [Vendor ID], DatePart("Year",[Order Date]), Sum([Bags]) as TotalBags
FROM [Orders]
Group By [Vendor ID], DatePart("Year",[Order Date])
) LY on V.[Vendor ID] = LY.[Vendor ID] and LY.Yr = (DatePart("Year", Getdate())-1)
Thanks!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 6, 2008 at 4:10 pm
Threads can be rated. I rated this 5 stars because there is a lot of instructive stuff in this. It has been helpful to see techniques for expanding a simple join into a set of joins, all within a single statement.
SQL is fundamentally different from pretty much anything else so sometimes programming experience won't allow you to predict or intuit how SQL will solve a familiar problem.
I'm not a huge fan of SQL so far because it is so dense and non-English but I imagine that as it becomes more second nature I'll be okay with that. But I'm sure that I'm not the only newbe coming from other programming paradigms to find the paradigm a bit off-putting.
But I'm starting to see that it is powerful stuff.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
November 6, 2008 at 7:43 pm
jcrawf02 (11/6/2008)
Thanks Rbar, hadn't seen that trick before.
You're welcome, uh, jcraw(?), but you know I don't like that nickname. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 6, 2008 at 7:45 pm
billross (11/6/2008)
Threads can be rated. I rated this 5 stars because there is a lot of instructive stuff in this. It has been helpful to see techniques for expanding a simple join into a set of joins, all within a single statement.
Thanks for the feedback, Bill.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply