October 9, 2007 at 9:11 am
I have an code that breaks down sales by state and compares totals from last year with this year, per month with a YTD total comparison. I need the totals of each month calculated.
this is an example code with only one month.
I have tried a couple of different things but cant get it to display the information I need.
Anyone have nay advice?
set transaction isolation level read uncommitted
set nocount on
SELECT (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END) AS DomesticInternational, h.ShipToDeliveryLocationState,
DATEPART(yyyy, h.InvoiceDate) AS theYear,
SUM(CASE WHEN DatePart(MM, h.InvoiceDate) = 1 THEN (CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END) ELSE 0 END) AS January,
SUM(CASE WHEN DatePart(MM, h.InvoiceDate) <= DatePart(MM, GetDate()) THEN (CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END) ELSE 0 END) AS YTDTotal
FROM FS_ARInvoiceLine l
-- join invoice lines
INNER JOIN FS_ARInvoiceHeader h (nolock)
ON h.ARInvoiceHeaderKey = l.ARInvoiceHeaderKey
-- join customer master
INNER JOIN FS_Customer c (nolock)
ON c.CustomerKey = l.CustomerKey
-- join order lines
INNER JOIN FS_ARCustomerOrderHeader o (nolock)
ON o.ARCustomerOrderHeaderKey = l.ARCustomerOrderHeaderKey
-- join item master
LEFT OUTER JOIN FS_Item i (nolock)
ON i.ItemKey = l.ItemKey
-- join customer description
INNER JOIN STSS_CustomerClassDescription CC (nolock)
ON CC.CustomerClass1 = c.CustomerClass1
WHERE (DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) OR
DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 1 OR
DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 0 OR
DATEPART(yyyy, h.InvoiceDate) = DATEPART(yyyy, GETDATE()) - 0) AND (i.ItemReference2 = '55') AND (h.InvoiceStatus IN ('N', 'P', 'Y')) AND
(dbo.STSS_GetSaleType(c.CustomerID, c.CustomerClass2) = 'Distinguished Sales')
GROUP BY (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END), h.ShipToDeliveryLocationState, DATEPART(yyyy, h.InvoiceDate)
ORDER BY (CASE WHEN c.CustomerClass1 = 'I' THEN 'International' ELSE 'Domestic' END), h.ShipToDeliveryLocationState, DATEPART(yyyy, h.InvoiceDate) DESC
October 9, 2007 at 10:28 am
There are a few options to look at. First thought is that you need to incorporate the month component into the grouping, or you will be generating one number at a time. something like - group by YYYYMM of the date (makes your compares to previous year easy.)
Second - what's the expected output needed? I'm seeing two options:
Year Month monthlytotal LastYearMonth
2007 01 12345 23456
.....
YTD 99999 888888
OR
YEAR MONTH monthlytotal lastyearmonthly YTD LastYearYTD
Finally - try to post some specific DDL info about the relevant data.
I hope you're not averse to pregenerating some data (even if you refresh on the fly), cause this could really use it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 11:18 am
D or I State theyear YTD Totals Jan Feb Mar Apr May June
DomesticAL2007 4 276 1 3 15
DomesticAL2006 1 001 0 0 0
Totals NEEDED
This is the output of y query as of now.
I have the YTD - totals of each year, but what I also need the totals for YTD and each month.
any suggestions?
October 9, 2007 at 12:06 pm
not sure I fully understand all of the criteria on qualifying sales, but here's a rough sketch.
The current best way to do this is to pre-generate the monthly and YTD totals, then correlate them.
Create table salesbymonth (CustClass1 char, locstate char(2),yr int, YearMonth int, PrevYearMonth int,MonthTotal Money,YTDTotal money)
--create the monthly total
insert into salesbymonth (CustClass1, locstate,yr, YearMonth, PrevYearMonth ,MonthTotal )
select case when c.customerclass1='I' then 'I' else 'D' end, h.ShipToDeliveryLocationState,datepart(yy,invoicedate),left(convert(char,InvoiceDate,112),6),left(convert(char,dateadd(yy,-1,InvoiceDate),112),6), sum(CASE WHEN h.InvoiceType = 'R' THEN l.ShipQuantity * - 1 ELSE l.ShipQuantity END)
from
FROM FS_ARInvoiceLine l
-- join invoice lines
INNER JOIN FS_ARInvoiceHeader h (nolock)
ON h.ARInvoiceHeaderKey = l.ARInvoiceHeaderKey
-- join customer master
INNER JOIN FS_Customer c (nolock)
ON c.CustomerKey = l.CustomerKey
-- join item master
LEFT OUTER JOIN FS_Item i (nolock)
where
(h.InvoiceStatus IN ('N', 'P', 'Y')) AND
(dbo.STSS_GetSaleType(c.CustomerID, c.CustomerClass2) = 'Distinguished Sales')
--get set up to create the YTD values
Create index SalesByMonth_ix on SalesByMonth(CustClass,locstate,yearmonth)
Create index SalesByMonth_pix on SalesByMonth(CustClass,locstate,Prevyearmonth)
declare @previd int
declare @runsales money
set @previd=0
set @runsales=0
--set up the YTD values
update salesbymonth
set @runsales=YTDTotal=case when @previd=yr then @runsales else 0 end+MonthTotal,
@previd=yearmonth
from salesbymonth with (index(SalesByMonth_ix),tablock)
---and now - for the results
select
curr.customerclass1,curr.yearmonth,curr.monthtotal CurrentMonthtotal, prev.monthtotal PrevMonthTotal,curr.YTDTotal currentYTD, prev.YTDTotal PrevYTDTotal
from salesbymonth curr inner join salesbymonth prev
on curr.customerclass1=prev.customerclass1 and curr.locstate=prev.locstate and curr.prevmonth=prev.yearmonth
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply