Date Results Broken Out by Month

[dldev]

Sorry, I forgot to put the subject on this.

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

 

[TheJon]

You are trying to create a cross tab, grouped by customer. Not knowing your table structure, try something like this, which will show the last n months grouped by customer, showing each month across the table:

declare @rptdate smalldatetime
set @rptdate = getdate()

  select  custname
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, @rptdate, 6), 6) then quantity else 0 end) month1_quant
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, @rptdate, 6), 6) then xtndprce else 0 end) month1_value
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, dateadd(m, -1, @rptdate), 6), 6) then quantity else 0 end) month2_quant
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, dateadd(m, -1, @rptdate), 6), 6) then xtndprce  else 0 end) month2_value
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, dateadd(m, -2, @rptdate), 6), 6) then quantity else 0 end) month3_quant
        , sum(case when right(convert(varchar, docdate, 6), 6) = right(convert(varchar, dateadd(m, -2, @rptdate), 6), 6) then xtndprce  else 0 end) month3_value    from  sop30200
group by  custname
order by  custname

Specify @rptdate when calling it and add more months as necessary.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]