January 18, 2009 at 3:28 am
I have been asked to develop a report that returns similar output as the attached excel file.
I am unsure regards which approach to take.
As a start point I have the following:
WITH QuotesByMonth AS
(
SELECT
MONTH (so.EffectiveDate)AS Month,
COUNT(so.SalesOrder)AS NumberOfQuotes,
SUM (si.CurItemValue) AS OrderTotal
FROM SalesOrders so
INNER JOIN SalesOrderItems si ON so.SalesOrder = si.SalesOrder
WHERE so.SystemType = 'Q'
GROUP BY so.EffectiveDate
)
SELECT * FROM QuotesByMonth
ORDER BY Month
The above does not return a single value for month (i.e. month 11 is referenced multiple times). I would hope to output:
Month---------NumberOfQuotes
Nov-------------50
I also tried MonthName(MONTH( to output the month name rather than 11, maybe this is best done in visual studio when developing the report?
I have included the query output as a second worksheet 'QueryOutPut' on the attached excel file.
How would you guys tackle the problem? What SQL functions should I be looking at? Are CTE's the way to go? When to use ROW_NUMBER(), RANK(), or DENSE_RANK()?
I am looking for some advice, guidance on how I should approach the problem, not a solution as I need to start writing some code else I am never going to improve.
Many Thanks,
Phil.
Update: Uploaded file as .xls as opposed .xlsx
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 18, 2009 at 6:05 am
You are grouping the results by the value of the date instead of grouping them by the month. If you have records from the same month but they are from a different day of the month or even just a different time , those records will not be grouped together. Pleas try this query and see if it helps (if you have records from more then one year, you’ll have to group the records by year and month):
SELECT
MONTH (so.EffectiveDate)AS Month,
COUNT(so.SalesOrder)AS NumberOfQuotes,
SUM (si.CurItemValue) AS OrderTotal
FROM SalesOrders so
INNER JOIN SalesOrderItems si ON so.SalesOrder = si.SalesOrder
WHERE so.SystemType = 'Q'
GROUP BY MONTH (so.EffectiveDate)
As for your question about ranking functions - I couldn’t open your excel file, so I can’t comment on that.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 18, 2009 at 7:27 am
Adi thanks for the reply, I have re-attached as .xls.
I have modified code as follows:
SELECT
YEAR (so.EffectiveDate) AS Year,
MONTH (so.EffectiveDate)AS Month,
COUNT(so.SalesOrder)AS NumberOfQuotes,
SUM (si.CurItemValue) AS OrderTotal
FROM SalesOrders so
INNER JOIN SalesOrderItems si ON so.SalesOrder = si.SalesOrder
WHERE so.SystemType = 'Q'
GROUP BY YEAR (so.EffectiveDate),MONTH (so.EffectiveDate)
My original query was using a CTE however I am aware a simple join would achieve the same result. It was a building block as I felt the required query would be a little more complex than a simple join. Maybe a stored procedure will be required? I am sure what I am looking to do is quite standard, I would be interested to know the approach taken by others.
Jumping ahead to the report I was considering a Matrix as I am sure once product categories have been defined they will want to know the sales by product category?
I was going to use 'From:' and 'To:' (year) as report parameters.
They want to compare like for like quotes/sales i.e. 2008 Vs 2007 however the system is new so with no history this is not possible at this time (I can use sample data for report creation).
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply