use cursor to display rows and group them by one column using variable

Question

I have two tables:

Sales.SalesOrderHeader(SalesOrderID(PK), SalesOrderNumber, Status,..)
Sales.SalesOrderDetail(SalesOrderID(PK,FK), ..)

I've declared a variable @numdetail and a cursor SaleReportCursor. How can I Print something like:

    SalesOrderNumber1 (3 items) was shipped.
    SalesOrderNumber2 (4 items) was shipped.
    SalesOrderNumber3 (2 items) was shipped.

So that products with the same SalesOrderID can be grouped and counted? This is what I got for the Cursor:

DECLARE
@salesOrderID INT,
@salesOrderNum NVARCHAR(25), 
@dueDate DATETIME, 
@status tinyint,  
@message varchar(80), 
@numDetail INT,
@count INT = 0,
@astatus varchar(10);    
DECLARE salesReportCursor CURSOR 
FOR
select s.SalesOrderID, SalesOrderNumber,DueDate, Status 
FROM [Sales].[SalesOrderDetail] s 
join Sales.SalesOrderHeader h
on s.SalesOrderID=h.SalesOrderID
where 
h.DueDate between '2008-08-01' and '2008-08-31'
group by SalesOrderNumber,s.SalesOrderID,DueDate, Status
Order by SalesOrderNumber desc
FOR READ ONLY

OPEN salesReportCursor

FETCH NEXT from salesReportCursor
INTO @salesOrderID, @salesOrderNum , @dueDate , @status; 
WHILE @@FETCH_STATUS = 0
BEGIN
set @numDetail= 1
if @salesOrderNum =@salesOrderNum
set @numDetail=@numDetail+1
Set @astatus= 
case when @status=1 then 'In process'
    when @status=2 then 'Approved'
    when @status=3 then 'Backordered' 
    when @status=4 then 'Rejected' 
    when @status=5 then 'Shipped' 
    when @status=6 then 'Cancelled'
    end 
Select @message=cast(@salesOrderNum as varchar) 
+' ('+ cast(@numDetail as varchar)+' items) due '+
cast(@dueDate as varchar) +' is '+ @astatus
set @count=@count+1
Print @message
FETCH NEXT from salesReportCursor
INTO @salesOrderID, @salesOrderNum , @status; 
END

CLOSE salesReportCursor
DEALLOCATE salesReportCursor

What my outcome looks like:

    SalesOrderNumber1 (2 items) was shipped.
    SalesOrderNumber2 (2 items) was shipped.
    SalesOrderNumber3 (2 items) was shipped.

I think this was because of after the BEGIN where I set @numdetail initially as 1 for each row then add it by 1. I wonder how to group and count products with the same SalesOrderID?

Answer 1

You shouldn't be needing a cursor at all for that...

Try this instead (use COUNT to get number of items per order):

select
    SalesOrderNumber + ' (' + CONVERT(varchar, COUNT(*)) + ' items) due ' + CONVERT(varchar, h.DueDate, 20) + ' is ' + Statuses.Name
FROM
    [Sales].[SalesOrderDetail] s 
    join Sales.SalesOrderHeader h
        on s.SalesOrderID=h.SalesOrderID
    join (
        select 1 as Status, 'In process' as Name
        union all select 2, 'Approved'
        union all select 3, 'Backordered' 
        union all select 4, 'Rejected' 
        union all select 5, 'Shipped' 
        union all select 6, 'Cancelled'
    ) Statuses on
        Statuses.Status = h.Status
where 
    h.DueDate between '2008-08-01' and '2008-08-31'
group by
    h.SalesOrderNumber,
    s.SalesOrderID,
    h.DueDate,
    h.Status
Order by
    h.SalesOrderNumber desc

(I haven't tried the code myself, so there could be some syntax errors, but I hope you get the point)

You should also put the statuses in its own table... Preferably a table without an identity column, and use it as a lookup table.