Need SQL Query Help from Gurus

[ctrl+alt+delete]

I have a table that stores ratings information (column called PPMSBRTotal)
for vendors on a monthly basis. The ratings data is stored along with the
date period it's relevant to (column called MonthYear) and other vendor
data.

I need to be produce a result set that calculates the average rating for
each quarter as well as the annual average rating. So what I have come up
with are the following queries:

SELECT AVG(PPMSBRTotal) AS Q1_SBRTotal FROM CompanyRatings WHERE
(DATEPART(mm, MonthYear) BETWEEN 1 AND 3) AND (DATEPART(yy, MonthYear) =
2004) AND (VendorID = '#rsVendors.vendorID#')

SELECT AVG(PPMSBRTotal) AS Q2_SBRTotal FROM CompanyRatings WHERE
(DATEPART(mm, MonthYear) BETWEEN 4 AND 6) AND (DATEPART(yy, MonthYear) =
2004) AND (VendorID = '#rsVendors.vendorID#')

SELECT AVG(PPMSBRTotal) AS Q3_SBRTotal FROM CompanyRatings WHERE
(DATEPART(mm, MonthYear) BETWEEN 7 AND 9) AND (DATEPART(yy, MonthYear) =
2004) AND (VendorID = '#rsVendors.vendorID#')

SELECT AVG(PPMSBRTotal) AS Q4_SBRTotal FROM CompanyRatings WHERE
(DATEPART(mm, MonthYear) BETWEEN 10 AND 12) AND (DATEPART(yy, MonthYear) =
2004) AND (VendorID = '#rsVendors.vendorID#')

SELECT AVG(PPMSBRTotal) AS Annual_SBRTotal FROM CompanyRatings WHERE
(DATEPART(mm, MonthYear) BETWEEN 1 AND 12) AND (DATEPART(yy, MonthYear) =
2004) AND (VendorID = '#rsVendors.vendorID#')

My problem is that I have to compile all of this data into one result set
for all of the vendors so it can be output into an Excel spreadsheet.
(Generating the Excel file is no trouble.)

Currently, I have a loop setup that runs each one of the above queries and
writes the output to a table cell for each vendor. That equates to 5 queries
times 156 vendors = way too many seperate queries for the old dual PIII
600MHz SQL server! It takes about 40 seconds to process and output into the
Excel table.

Is there a way to combine all of the db activity into one query or stored
procedure?

[Bill Sahlas]

First, how often do you have to do this? 40 secs isn't that bad.

Second, as a best practice you'd be better off compilting the querries in
one stored proc. The proc can take parameters such as beginQtr and endQtr,
forYear, forVendor.
This can be run using a cf app where you'd still have the loop for each
quarter and vendor.

Lastly, read this article authored by Samuel Neff
[url]http://www.macromedia.com/devnet/mx/coldfusion/articles/stored_procs.html[/url]

"ctrl+alt+delete" <thenetwerx@REMOVEmsn.com> wrote in message
news:cv5bvp$p18$1@forums.macromedia.com...

>I have a table that stores ratings information (column called PPMSBRTotal)
>for vendors on a monthly basis. The ratings data is stored along with the
>date period it's relevant to (column called MonthYear) and other vendor
>data.
>
> I need to be produce a result set that calculates the average rating for
> each quarter as well as the annual average rating. So what I have come up
> with are the following queries:
>
> SELECT AVG(PPMSBRTotal) AS Q1_SBRTotal FROM CompanyRatings WHERE
> (DATEPART(mm, MonthYear) BETWEEN 1 AND 3) AND (DATEPART(yy, MonthYear) =
> 2004) AND (VendorID = '#rsVendors.vendorID#')
>
> SELECT AVG(PPMSBRTotal) AS Q2_SBRTotal FROM CompanyRatings WHERE
> (DATEPART(mm, MonthYear) BETWEEN 4 AND 6) AND (DATEPART(yy, MonthYear) =
> 2004) AND (VendorID = '#rsVendors.vendorID#')
>
> SELECT AVG(PPMSBRTotal) AS Q3_SBRTotal FROM CompanyRatings WHERE
> (DATEPART(mm, MonthYear) BETWEEN 7 AND 9) AND (DATEPART(yy, MonthYear) =
> 2004) AND (VendorID = '#rsVendors.vendorID#')
>
> SELECT AVG(PPMSBRTotal) AS Q4_SBRTotal FROM CompanyRatings WHERE
> (DATEPART(mm, MonthYear) BETWEEN 10 AND 12) AND (DATEPART(yy, MonthYear) =
> 2004) AND (VendorID = '#rsVendors.vendorID#')
>
> SELECT AVG(PPMSBRTotal) AS Annual_SBRTotal FROM CompanyRatings WHERE
> (DATEPART(mm, MonthYear) BETWEEN 1 AND 12) AND (DATEPART(yy, MonthYear) =
> 2004) AND (VendorID = '#rsVendors.vendorID#')
>
> My problem is that I have to compile all of this data into one result set
> for all of the vendors so it can be output into an Excel spreadsheet.
> (Generating the Excel file is no trouble.)
>
> Currently, I have a loop setup that runs each one of the above queries and
> writes the output to a table cell for each vendor. That equates to 5
> queries times 156 vendors = way too many seperate queries for the old dual
> PIII 600MHz SQL server! It takes about 40 seconds to process and output
> into the Excel table.
>
> Is there a way to combine all of the db activity into one query or stored
> procedure?
>

[Bill Sahlas]

One more thing that's important to understand is that once a stored
procedure is comile it has with it execution plan and statistics about how
best to get the data. When you have this information storted at the db
level then you performance improves dramatically.


"Bill Sahlas" <bsahlas@macromedia.com> wrote in message
news:cv5l4v$8e6$1@forums.macromedia.com...

> First, how often do you have to do this? 40 secs isn't that bad.
>
> Second, as a best practice you'd be better off compilting the querries in
> one stored proc. The proc can take parameters such as beginQtr and
> endQtr, forYear, forVendor.
> This can be run using a cf app where you'd still have the loop for each
> quarter and vendor.
>
> Lastly, read this article authored by Samuel Neff
> [url]http://www.macromedia.com/devnet/mx/coldfusion/articles/stored_procs.html[/url]
>
> "ctrl+alt+delete" <thenetwerx@REMOVEmsn.com> wrote in message
> news:cv5bvp$p18$1@forums.macromedia.com...

>>I have a table that stores ratings information (column called PPMSBRTotal)
>>for vendors on a monthly basis. The ratings data is stored along with the
>>date period it's relevant to (column called MonthYear) and other vendor
>>data.
>>
>> I need to be produce a result set that calculates the average rating for
>> each quarter as well as the annual average rating. So what I have come up
>> with are the following queries:
>>
>> SELECT AVG(PPMSBRTotal) AS Q1_SBRTotal FROM CompanyRatings WHERE
>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 3) AND (DATEPART(yy, MonthYear) =
>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>
>> SELECT AVG(PPMSBRTotal) AS Q2_SBRTotal FROM CompanyRatings WHERE
>> (DATEPART(mm, MonthYear) BETWEEN 4 AND 6) AND (DATEPART(yy, MonthYear) =
>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>
>> SELECT AVG(PPMSBRTotal) AS Q3_SBRTotal FROM CompanyRatings WHERE
>> (DATEPART(mm, MonthYear) BETWEEN 7 AND 9) AND (DATEPART(yy, MonthYear) =
>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>
>> SELECT AVG(PPMSBRTotal) AS Q4_SBRTotal FROM CompanyRatings WHERE
>> (DATEPART(mm, MonthYear) BETWEEN 10 AND 12) AND (DATEPART(yy, MonthYear)
>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>
>> SELECT AVG(PPMSBRTotal) AS Annual_SBRTotal FROM CompanyRatings WHERE
>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 12) AND (DATEPART(yy, MonthYear) =
>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>
>> My problem is that I have to compile all of this data into one result set
>> for all of the vendors so it can be output into an Excel spreadsheet.
>> (Generating the Excel file is no trouble.)
>>
>> Currently, I have a loop setup that runs each one of the above queries
>> and writes the output to a table cell for each vendor. That equates to 5
>> queries times 156 vendors = way too many seperate queries for the old
>> dual PIII 600MHz SQL server! It takes about 40 seconds to process and
>> output into the Excel table.
>>
>> Is there a way to combine all of the db activity into one query or stored
>> procedure?
>>

>
>

[ctrl+alt+delete]

I came up with a better query and will roll it into a stored proc as well.

What I did was add each of averages queries into the vendorID query (not
shown in my earlier post) as sub-queries. Now SQL Server rips through it in
less than a second and the web server doesn't have to loop over it anymore.

"Bill Sahlas" <bsahlas@macromedia.com> wrote in message
news:cv5lbo$8k8$1@forums.macromedia.com...

> One more thing that's important to understand is that once a stored
> procedure is comile it has with it execution plan and statistics about how
> best to get the data. When you have this information storted at the db
> level then you performance improves dramatically.
>
>
> "Bill Sahlas" <bsahlas@macromedia.com> wrote in message
> news:cv5l4v$8e6$1@forums.macromedia.com...

>> First, how often do you have to do this? 40 secs isn't that bad.
>>
>> Second, as a best practice you'd be better off compilting the querries in
>> one stored proc. The proc can take parameters such as beginQtr and
>> endQtr, forYear, forVendor.
>> This can be run using a cf app where you'd still have the loop for each
>> quarter and vendor.
>>
>> Lastly, read this article authored by Samuel Neff
>> [url]http://www.macromedia.com/devnet/mx/coldfusion/articles/stored_procs.html[/url]
>>
>> "ctrl+alt+delete" <thenetwerx@REMOVEmsn.com> wrote in message
>> news:cv5bvp$p18$1@forums.macromedia.com...

>>>I have a table that stores ratings information (column called
>>>PPMSBRTotal) for vendors on a monthly basis. The ratings data is stored
>>>along with the date period it's relevant to (column called MonthYear) and
>>>other vendor data.
>>>
>>> I need to be produce a result set that calculates the average rating for
>>> each quarter as well as the annual average rating. So what I have come
>>> up with are the following queries:
>>>
>>> SELECT AVG(PPMSBRTotal) AS Q1_SBRTotal FROM CompanyRatings WHERE
>>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 3) AND (DATEPART(yy, MonthYear) =
>>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>
>>> SELECT AVG(PPMSBRTotal) AS Q2_SBRTotal FROM CompanyRatings WHERE
>>> (DATEPART(mm, MonthYear) BETWEEN 4 AND 6) AND (DATEPART(yy, MonthYear) =
>>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>
>>> SELECT AVG(PPMSBRTotal) AS Q3_SBRTotal FROM CompanyRatings WHERE
>>> (DATEPART(mm, MonthYear) BETWEEN 7 AND 9) AND (DATEPART(yy, MonthYear) =
>>> 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>
>>> SELECT AVG(PPMSBRTotal) AS Q4_SBRTotal FROM CompanyRatings WHERE
>>> (DATEPART(mm, MonthYear) BETWEEN 10 AND 12) AND (DATEPART(yy, MonthYear)
>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>
>>> SELECT AVG(PPMSBRTotal) AS Annual_SBRTotal FROM CompanyRatings WHERE
>>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 12) AND (DATEPART(yy, MonthYear)
>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>
>>> My problem is that I have to compile all of this data into one result
>>> set for all of the vendors so it can be output into an Excel
>>> spreadsheet. (Generating the Excel file is no trouble.)
>>>
>>> Currently, I have a loop setup that runs each one of the above queries
>>> and writes the output to a table cell for each vendor. That equates to 5
>>> queries times 156 vendors = way too many seperate queries for the old
>>> dual PIII 600MHz SQL server! It takes about 40 seconds to process and
>>> output into the Excel table.
>>>
>>> Is there a way to combine all of the db activity into one query or
>>> stored procedure?
>>>

>>
>>

>
>

[Bill Sahlas]

nice, glad you've got it going. good idea -

"ctrl+alt+delete" <thenetwerx@REMOVEmsn.com> wrote in message
news:cv5li1$90c$1@forums.macromedia.com...

>I came up with a better query and will roll it into a stored proc as well.
>
> What I did was add each of averages queries into the vendorID query (not
> shown in my earlier post) as sub-queries. Now SQL Server rips through it
> in less than a second and the web server doesn't have to loop over it
> anymore.
>
> "Bill Sahlas" <bsahlas@macromedia.com> wrote in message
> news:cv5lbo$8k8$1@forums.macromedia.com...

>> One more thing that's important to understand is that once a stored
>> procedure is comile it has with it execution plan and statistics about
>> how best to get the data. When you have this information storted at the
>> db level then you performance improves dramatically.
>>
>>
>> "Bill Sahlas" <bsahlas@macromedia.com> wrote in message
>> news:cv5l4v$8e6$1@forums.macromedia.com...

>>> First, how often do you have to do this? 40 secs isn't that bad.
>>>
>>> Second, as a best practice you'd be better off compilting the querries
>>> in one stored proc. The proc can take parameters such as beginQtr and
>>> endQtr, forYear, forVendor.
>>> This can be run using a cf app where you'd still have the loop for each
>>> quarter and vendor.
>>>
>>> Lastly, read this article authored by Samuel Neff
>>> [url]http://www.macromedia.com/devnet/mx/coldfusion/articles/stored_procs.html[/url]
>>>
>>> "ctrl+alt+delete" <thenetwerx@REMOVEmsn.com> wrote in message
>>> news:cv5bvp$p18$1@forums.macromedia.com...
>>>>I have a table that stores ratings information (column called
>>>>PPMSBRTotal) for vendors on a monthly basis. The ratings data is stored
>>>>along with the date period it's relevant to (column called MonthYear)
>>>>and other vendor data.
>>>>
>>>> I need to be produce a result set that calculates the average rating
>>>> for each quarter as well as the annual average rating. So what I have
>>>> come up with are the following queries:
>>>>
>>>> SELECT AVG(PPMSBRTotal) AS Q1_SBRTotal FROM CompanyRatings WHERE
>>>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 3) AND (DATEPART(yy, MonthYear)
>>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>>
>>>> SELECT AVG(PPMSBRTotal) AS Q2_SBRTotal FROM CompanyRatings WHERE
>>>> (DATEPART(mm, MonthYear) BETWEEN 4 AND 6) AND (DATEPART(yy, MonthYear)
>>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>>
>>>> SELECT AVG(PPMSBRTotal) AS Q3_SBRTotal FROM CompanyRatings WHERE
>>>> (DATEPART(mm, MonthYear) BETWEEN 7 AND 9) AND (DATEPART(yy, MonthYear)
>>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>>
>>>> SELECT AVG(PPMSBRTotal) AS Q4_SBRTotal FROM CompanyRatings WHERE
>>>> (DATEPART(mm, MonthYear) BETWEEN 10 AND 12) AND (DATEPART(yy,
>>>> MonthYear) = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>>
>>>> SELECT AVG(PPMSBRTotal) AS Annual_SBRTotal FROM CompanyRatings WHERE
>>>> (DATEPART(mm, MonthYear) BETWEEN 1 AND 12) AND (DATEPART(yy, MonthYear)
>>>> = 2004) AND (VendorID = '#rsVendors.vendorID#')
>>>>
>>>> My problem is that I have to compile all of this data into one result
>>>> set for all of the vendors so it can be output into an Excel
>>>> spreadsheet. (Generating the Excel file is no trouble.)
>>>>
>>>> Currently, I have a loop setup that runs each one of the above queries
>>>> and writes the output to a table cell for each vendor. That equates to
>>>> 5 queries times 156 vendors = way too many seperate queries for the old
>>>> dual PIII 600MHz SQL server! It takes about 40 seconds to process and
>>>> output into the Excel table.
>>>>
>>>> Is there a way to combine all of the db activity into one query or
>>>> stored procedure?
>>>>
>>>
>>>

>>
>>

>
>