January 21, 2004 at 4:41 pm
I am trying to create a stored procedure that will provide Salesman information by product and month. I would want the result set to look something like this.
Salesman Product Month1 Month2 Month3 Month4 Month5 ....
Joe Brown Shirt 25 20 30 50 60
I would want a row for each product sold. The real difficulty is that the procedure needs to return columns for a variable number of months.
The procedure would accept inputs for Salesman and Start Date and End Date.
The date range would be unknown and a column would need to be generated for each month in the date range. I'm not sure how to accomodate this unknown number of columns. The data is stored in the database by individual item sold, so it would need to be rolled up to a monthly total.
Could you give me an idea of how to structure the procedure to accomodate this?
Thanks
January 21, 2004 at 6:21 pm
This really should be done on the front-end; it would be easy with a report writer that supports cross-tab reports (and most do). There are also some third-party utilities available for doing this sort of thing (http://www.rac4sql.com, http://www.ag-software.com/xp_ags_crosstab.aspx). But here's one way to do it with dynamic T-SQL:
USE Northwind
go
CREATE PROC p_SalesByMo @startdate datetime, @enddate datetime, @emp int AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SELECT @sql = ISNULL(@sql + ',
','') + 'SUM(CASE WHEN DATEDIFF(m,@startdate,ShippedDate) = ' + CAST(Number AS varchar)
+ ' THEN Quantity ELSE 0 END) Month' + CAST(Number + 1 AS varchar)
FROM master..spt_values
WHERE Type = 'P' AND Number <= DATEDIFF(m,@startdate,@enddate)
ORDER BY Number
SET @sql = 'SELECT ProductId,'
+ @sql + '
FROM Orders o JOIN [Order Details] d ON d.OrderId = o.OrderId
WHERE o.EmployeeID = @emp
GROUP BY d.ProductID'
EXEC sp_executesql @sql,N'@startdate datetime, @enddate datetime, @emp int',@startdate,@enddate,@emp
--Jonathan
January 22, 2004 at 1:53 am
Why not create your stored procedure to do your aggregation into a summary table then use MS Excel to do your Crosstabs?
January 22, 2004 at 2:06 am
Hi Charlesd,
XTAB will do the job for you - no fuss.
It's big but you only have to copy and paste it - no messing with code because the XTAB stored procedure gives you full control through its parameter interface. Here's the URL.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=95904
Below is a Northwind test using XTAB for a query similiar to the one you requested.
Your SALESMAN filter would go in the WHERE clause.
Hope this helps.
Ian
--------------------------------------
Use Northwind
EXEC sp_XTAB
'Northwind',
'xtORDMONTH',
'Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
'CustomerID, ProductID',
'CAST(Month(ShippedDate) AS varchar(4))',
'',
'Orders.OrderID',
'COUNT',
'NULL',
'WHERE ShippedDate Between |May 1,1997| AND |July 1,1997|',
''
SELECT xtORDMONTH.* FROM xtORDMONTH
January 22, 2004 at 9:50 am
Jonathan,
Thanks for your reply. I realize that it would be preferable to use a report writer to do the job, but I would like to try your method. We have been using VB for most of our report writing. I need to modify your sql somewhat, but I seem to be having a problem getting it to run correctly. I don't need to account for Quantity. I'm dealing with a system that has a single line entry per item. How would I modify the following to remove the reference to quantity:
'SUM(CASE WHEN DATEDIFF(m,@startdate,DateEnteredInSystem) =
' + CAST(Number AS varchar)
+ ' THEN Quantity ELSE 0 END) Month' + CAST(Number + 1 AS varchar)
Thanks again for your help.
charlesd
January 22, 2004 at 3:21 pm
If you just need a count, replace the column identifier (Quantity) with just the number 1:
...THEN 1 ELSE 0 END
It's easier (and better distributed) to do this in VB, too...
CREATE PROC p_SalesMonths @startdate datetime, @enddate datetime, @emp int AS
SET NOCOUNT ON
SELECT d.ProductID, DATEDIFF(m,@startdate,o.ShippedDate) Mon, COUNT(*) Cnt
FROM [Order Details] d JOIN Orders o ON o.OrderID = d.OrderID
WHERE o.ShippedDate >= CAST(CONVERT(char(8),@startdate,112) AS datetime) - DAY(@startdate) + 1
AND o.ShippedDate < DATEADD(m,1,CONVERT(char(8),@enddate,112)) - DAY(@enddate) + 1
AND o.EmployeeID = @emp
GROUP BY d.ProductID, DATEDIFF(m,@startdate,o.ShippedDate)
ORDER BY d.ProductID, Mon
Sub PivotSales(dtStartDate As Date, dtEndDate As Date, nEmployee As Integer)
Dim nMonths As Integer, n As Integer
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As ADODB.Recordset
Dim sConn As String
Const SERVER As String = "TestSever2"
sConn = "Provider=SQLOLEDB;Server=" & SERVER & ";Database=Northwind;Integrated Security=SSPI"
cnn.Open sConn
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "p_SalesMonths"
.Parameters.Append .CreateParameter("@startdate", adDBDate, adParamInput, , dtStartDate)
.Parameters.Append .CreateParameter("@enddate", adDBDate, adParamInput, , dtEndDate)
.Parameters.Append .CreateParameter("@emp", adInteger, adParamInput, , nEmployee)
Set rst = .Execute
End With
Set cmd = Nothing
nMonths = DateDiff("m", dtStartDate, dtEndDate)
Debug.Print "ProductID",
For n = 0 To nMonths
Debug.Print "Month" & n + 1,
Next
Debug.Print
With rst
While Not .EOF
Debug.Print .Fields(0),
For n = 0 To nMonths
If .EOF Then
Debug.Print 0,
Else
If .Fields(1) = n Then
Debug.Print .Fields(2),
.MoveNext
Else
Debug.Print 0,
End If
End If
Next
Debug.Print
Wend
End With
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply