Order Count Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order Count Stored Procedure

Author

Topic

PETE314
Starting Member

37 Posts

Posted - 2005-09-15 : 02:26:35

Access 2000 project (.adp) frontend
SQLServer2000 backend

I have created a stored procedure to count orders. It takes a total daily order count and then it is supposed to count the daily orders for six clients. So in the end it should come up in a sort of crosstab table....here is my syntax....

Alter Procedure procDOCC6
@ST1 nvarchar(6),
@ST2 nvarchar(6),
@ST3 nvarchar(6),
@ST4 nvarchar(6),
@ST5 nvarchar(6),
@ST6 nvarchar(6),
@Month int,
@Year int

WITH RECOMPILE
AS
SET NOCOUNT ON

SELECT TOP 100 PERCENT dbo.tblOrderInformation.dtmOrderDate,
COUNT(dbo.tblOrderInformation.strOrderInfoID) AS TotalInv,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST1 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv1,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST2 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv2,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST3 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv3,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST4 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv4,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST5 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv5,
COUNT(CASE dbo.tblPropertyDetails.strClientID WHEN @ST6 THEN dbo.tblOrderInformation.strOrderInfoID ELSE 0 END) AS Serv6
FROM dbo.tblOrderInformation INNER JOIN dbo.tblPropertyDetails ON dbo.tblOrderInformation.numCentury = dbo.tblPropertyDetails.numCentury AND dbo.tblOrderInformation.strOrderID = dbo.tblPropertyDetails.strOrderID
WHERE (YEAR(dbo.tblOrderInformation.dtmOrderDate) = @Year) AND (MONTH(dbo.tblOrderInformation.dtmOrderDate) = @Month) AND ((dbo.tblOrderInformation.blnCanceled)=0)
GROUP BY dbo.tblOrderInformation.dtmOrderDate ORDER BY dbo.tblOrderInformation.dtmOrderDate

6 clientID's are passed to the SP as well a month# and year....the problem I am having is that I am getting the full count for each of the clients instead of the count for just that client...Hopefully this is a simple syntax problem...I was wondering if anyone could tell me how to fix this syntax.....

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 02:30:01

SUM(CASE dbo.tblPropertyDetails.strClientID WHEN @ST1 THEN 1 ELSE 0 END) AS Serv1

etc.

Kristen

PETE314
Starting Member

37 Posts

Posted - 2005-09-15 : 02:41:49

Ahhhhhh yes I see it now....it basically sums up 1's for each record when the case statement is true....and with the Inner joins in the FROM statement that means only orders for that company show.....

Thank You very much.....

I knew it was something easy that was in front of my face the whole time :D

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 04:59:55

Sorry, my reply was terse - I was rushing to take kids to skool!

K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources