January 18, 2010 at 8:24 am
I have the following sp that will not pull 2010 in a query, however from the fields behind it I can pull 2010 information. For example I know there has been items invoiced in 2010.
I am new to running totals in sql.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE _xxxxx AS
--GET ALL POSSIBLE MONTHS/YEARS BY CUSTOMER/SALESREP
if exists (select 1 from tempdb..sysobjects where Name = '##CustomerByDateWithoutFreight')
drop table ##CustomerByDateWithoutFreight
SELECT DISTINCT
Cust_Num,
SlsMan,
[Month],
Month2,
[YEAR]
INTO
##CustomerByDateWithoutFreight
FROM
INV_HDR
CROSS JOIN MonthYear
WHERE
INV_DATE >= '01/01/2005'
AND INV_SEQ = '0'
AND INV_SEQ = '0'
AND INV_NUM > ' 60000'
AND INV_NUM < '9999999999'
--BUILD RUNNING TOTALS TABLE
if exists (select 1 from tempdb..sysobjects where Name = '##RunningTotalCursorWithoutFreight')
DROP TABLE ##RunningTotalCursorWithoutFreight
CREATE TABLE ##RunningTotalCursorWithoutFreight (SalesRep NVARCHAR(50), Cust_Num NVARCHAR(50), Month NVARCHAR(30), Month2 INT, Year INT, Revenue DECIMAL(20,2), MRT_Filtered_Year DECIMAL(20,2))
DECLARE RunningTotalCursor CURSOR
FOR
--PUT IN REVENUE
SELECT
cbd.SlsMan,
cbd.Cust_Num,
cbd.Month,
cbd.Month2,
cbd.Year,
ISNULL(SUM(i.QTY_INVOICED * i.PRICE), 0) AS Revenue
FROM
##CustomerByDateWithoutFreight cbd
LEFT OUTER JOIN INV_ITEM i
INNER JOIN INV_HDR ih
ON i.inv_num = ih.inv_num
AND ih.inv_date >= '01/01/2005'
AND ih.inv_seq='0'
AND ih.inv_seq='0'
AND ih.INV_NUM>' 60000'
AND ih.INV_NUM < '9999999999'
ON cbd.Year = YEAR(ih.INV_DATE)
AND cbd.Month2 = Month(ih.INV_DATE)
AND cbd.Cust_Num = ih.Cust_Num
AND cbd.SlsMan = ih.SlsMan
WHERE
cbd.SlsMan IS NOT NULL
GROUP BY
cbd.SlsMan,
cbd.Cust_Num,
cbd.Year,
cbd.Month,
cbd.Month2
ORDER BY
cbd.SlsMan,
cbd.Cust_Num,
cbd.Year,
cbd.Month2
DECLARE @SlsMan NVARCHAR(30), @Cust_Num NVARCHAR(30), @Month NVARCHAR(30), @Month2 INT, @Year INT, @Revenue DECIMAL(20,2)
DECLARE @MRT_Filtered_Year Decimal(20,2)
OPEN RunningTotalCursor
FETCH NEXT FROM RunningTotalCursor INTO @SlsMan , @Cust_Num, @Month, @Month2, @Year, @Revenue
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @MRT_Filtered_Year =
(
SELECT
SUM(ii.QTY_INVOICED * ii.PRICE)
FROM
INV_ITEM ii
JOIN INV_HDR ih ON ii.INV_NUM = ih.INV_NUM
WHERE
ih.INV_DATE >= '01/01/2005'
AND ii.INV_SEQ = '0'
AND ih.INV_SEQ = '0'
AND ih.INV_NUM > ' 60000' AND ih.INV_NUM < '9999999999'
AND ih.SLSMAN = @SlsMan
AND ih.CUST_NUM = @CUST_NUM
AND YEAR(ih.INV_DATE) = @Year
AND Month(ih.INV_Date) <= @Month2
)
INSERT INTO ##RunningTotalCursorWithoutFreight
SELECT DISTINCT
@SlsMan AS SalesRep,
@Cust_Num AS Cust_Num,
@Month as [Month],
@Month2 AS Month2,
@Year as [Year],
@Revenue AS Revenue,
ISNULL(@MRT_Filtered_Year, 0) AS MRT_Filtered_Year
END
FETCH NEXT FROM RunningTotalCursor INTO @SlsMan , @Cust_Num, @Month, @Month2, @Year, @Revenue
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--FIND PERCENT VARIANCE
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME = '##RunningTotalSummaryWithoutFreight')
DROP TABLE ##RunningTotalSummaryWithoutFreight
SELECT
r1.SalesRep,
r1.Cust_Num,
r1.Month,
r1.Month2,
r1.Year,
r1.Revenue,
r1.MRT_Filtered_Year,
ISNULL(r2.MRT_Filtered_Year, 0) AS MRT_Previous_Year,
CASE
WHEN r1.MRT_Filtered_Year > 0 AND r2.MRT_Filtered_Year = 0 THEN 1
WHEN r2.MRT_Filtered_Year = 0 THEN 0
ELSE (r1.MRT_Filtered_Year - r2.MRT_Filtered_Year) / r2.MRT_Filtered_Year
END AS Percent_Variance,
CASE
WHEN r1.MRT_Filtered_Year > 0 AND r2.MRT_Filtered_Year = 0 THEN 'GREATER THAN .05'
WHEN r2.MRT_Filtered_Year = 0 THEN 'BETWEEN -.05 AND .05'
WHEN (r1.MRT_Filtered_Year - r2.MRT_Filtered_Year) / r2.MRT_Filtered_Year <= -.05 THEN 'LESS THAN -.05'
WHEN (r1.MRT_Filtered_Year - r2.MRT_Filtered_Year) / r2.MRT_Filtered_Year >= .05 THEN 'GREATER THAN .05'
ELSE 'BETWEEN -.05 AND .05'
END AS Percent_Variance_FILTER
INTO
##RunningTotalSummaryWithoutFreight
FROM
##RunningTotalCursorWithoutFreight r1
LEFT OUTER JOIN ##RunningTotalCursorWithoutFreight r2
ON r1.SalesRep = r2.SalesRep
AND r1.Cust_Num = r2.Cust_Num
AND r1.Month = r2.Month
AND r1.Year -1 = r2.Year
--ADD DETAIL DATA
TRUNCATE TABLE _xxx
INSERT INTO _xxx
SELECT
r.Cust_Num as CustNum,
ca.name as CustomerName,
r.Cust_Num + ' - ' + ISNULL(ca.Name , 'No Name') AS CustomerKey,
ih.Cust_Seq as ShiptoNum,
ih.Inv_Date as DateInvoiced,
ih.Inv_Num as InvNum,
ih.Co_Num as CoNum,
r.YEAR as Year,
r.Month as Month,
'QTR'+DATENAME(QUARTER,ih.Inv_Date) as Quarter,
i.Item as ItemNumber,
it.Description as Description,
it.Alt_Item as AltItemNumber,
i.Qty_Invoiced as Quantity,
i.Price as Price,
(i.Qty_Invoiced * i.Price) AS REVENUE,
it.Unit_Cost as Cost,
(i.Qty_Invoiced * (i.Price - it.Unit_Cost)) AS PROFIT,
r.SalesRep as SalesRep,
it.Product_Code as ProductCode,
ca.telex_num as BuyerGroup,
iw.Qty_On_Hand as On_Hand,
iw.Qty_Reorder as Safety_Stock,
iw.Qty_Ordered as On_Order,
r.MRT_Previous_Year,
r.MRT_Filtered_Year,
r.Percent_Variance,
r.Percent_Variance_Filter,
ci.Uf_WeightMin as Weight,
(i.Qty_Invoiced * ci.Uf_WeightMin * sf.[2008CPL]) as DerivedFreight,
(i.Qty_Invoiced * i.Price)-(i.Qty_Invoiced * ci.Uf_WeightMin * sf.[2008CPL]) as RevenueWOF,
0 as ItemGross,
0 AS InvoiceGross,
ih.comm_due as CommDue,
0 AS ItemComm
FROM
##RunningTotalSummaryWithoutFreight r
LEFT OUTER JOIN CUSTADDR ca
ON r.CUST_NUM = ca.CUST_NUM
AND ca.CUST_SEQ= '0'
LEFT OUTER JOIN INV_HDR ih
ON r.SalesRep = ih.SlsMan
AND r.Cust_Num = ih.Cust_Num
AND r.Month = DATENAME(MONTH,ih.Inv_Date)
AND r.Year = DATENAME(YEAR,ih.Inv_Date)
AND ih.INV_DATE >= '01/01/2005'
AND ih.INV_SEQ = '0'
AND ih.INV_NUM > ' 60000' AND ih.INV_NUM < '9999999999'
LEFT OUTER JOIN INV_ITEM i
ON i.INV_NUM = ih.INV_NUM
AND i.INV_SEQ = '0'
LEFT OUTER JOIN ITEM it
ON i.ITEM = it.ITEM
LEFT OUTER JOIN ITEMWHSE iw
ON it.ALT_ITEM = iw.ITEM
LEFT OUTER JOIN COITEM ci
ON ci.CO_NUM = i.CO_NUM
AND ci.CO_LINE=i.CO_LINE
LEFT OUTER JOIN dbo._STATEFREIGHT$ sf
ON sf.state = ca.STATE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 18, 2010 at 9:53 am
Does your MonthYear table have entries for 2010?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 18, 2010 at 9:55 am
No it does not. I just found that out. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply