Hello experts, I have this stor

[dldev]

Hello experts,

I have this stored proc which returns sales for a given beginning and ending date range. The challenge I face is breaking out the results so the results are displayed by month as follows:

Sales By Customer:
Qty $ Qty $
Jan Jan Feb Feb
ABC Salon 3,456 6,666.00 5,555 10,111.00
Beauty Supply 222 666.00 333 666.00

Here is the stored proc:

CREATE procedure dbo.udsp_DR_PostedUnitSalesByCustomer
(
@begDate datetime = null,
@endDate datetime = null
)
as
begin

set nocount on
SELECT
[CUSTOMER] = SOP30200.CUSTNAME,
[QTY] = ISNULL(CAST(ROUND(SUM(SOP30300.QUANTITY),2) AS NUMERIC(19,0)),0),
[EXT PRICE] = SUM(SOP30300.XTNDPRCE)
FROM
SOP30200 INNER JOIN SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND
SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
WHERE
SOP30300.SOPTYPE = 3 AND
@begDate IS NULL AND @endDate IS NULL OR SOP30200.DOCDATE BETWEEN @begDate AND @endDate
GROUP BY
SOP30200.CUSTNAME
ORDER BY
SOP30200.CUSTNAME
END

GO

I was looking at trying to extract the month with the datepart function and tried several things but could not get it, so I come to you for help please.

Thank you,
Dennis

 

[Otto]

Here is example (substitute your fields).

select CUSTNAME,
 case MM when 1 then QTY else 0 end QTY_01,
 case MM when 1 then VALUE else 0 end VALUE_01,
/* ... */
 case MM when 2 then QTY else 0 end QTY_02,
 case MM when 2 then VALUE else 0 end VALUE_02
/* ... */
 from (
select CUSTNAME, month(DOCDATE) MM, sum(QTY) QTY, sum(QTY*PRICE) VALUE from CIKK
 where DOCDATE between @begDate and @endDate
 group by CUSTNAME, month(UTELADDAT)
) INS
 order by 1