cursor Help
-
CREATE PROCEDURE report_insert_monthly_sales
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #sales
(
product varchar(20),
sqty int default(0),
jan int default(0),
feb int default(0),
 
INSERT into #sales (product)
select Product from product_forecast
DECLARE @prod varchar(20),@qty int ,@jan int,@feb int
DECLARE sales_cursor CURSOR FOR
/// Get the monthly Sales value from invoice table
SELECT
product,
SUM(qty) AS qty,
jan = CASE WHEN MONTH(invoice_date) = 1 THEN isnull( SUM(qty) ,0)
END,
feb = CASE WHEN MONTH(invoice_date) = 2 THEN isnull(SUM(qty),0)
END,
MONTH(invoice_date) as months
FROM
dbo.sales_det
WHERE YEAR(invoice_date) = 2005 AND (product = 'MT-103-0602-200')
GROUP BY product, MONTH(invoice_date)
OPEN sales_cursor
FETCH NEXT FROM sales_cursor
INTO @prod,@qty,@jan,@feb
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #sales
SET sqty=sqty+@qty,
where
product= @prod
FETCH NEXT FROM sales_cursor
INTO @prod,@qty,@jan,@feb
END
CLose sales_cursor
Deallocate sales_cursor
end
select * from #sales
Why My Feb and Jan Value always NULL
Product Total Qty Jan Feb
MT-103-0602-200 26 NULL NULL
Actual Value
MT-103-0602-200 26 16 10
any think wrong with SQL Scripts or How can i improve
this code
Thanks in Advance
Kind Regards
Vijay Vijayaratnam
-
Why are you using a cursor?
UPDATE #Sales
SET sQty = Agg.qty, Jan=Agg.jan, Feb=Agg.feb
FROM (SELECT
product,
SUM(qty) AS qty,
SUM(CASE WHEN MONTH(invoice_date) = 1
THEN ISNULL(qty,0)
ELSE 0
END) AS Jan,
SUM(CASE WHEN MONTH(invoice_date) = 2
THEN ISNULL(qty,0)
ELSE 0
END) AS Feb,
FROM
dbo.sales_det
WHERE YEAR(invoice_date) = 2005
GROUP BY product) Agg
WHERE Agg.product = #Sales.Product
not tested, but should give you an idea.
As for why you were getting nulls, the case statement was incorrect. what you want is to sum the quantity if the month is the one in question, else 0
Also, from what I can tell, the group by month isn't needed.
The above query will give you the overall and the by-month quantities for each product for the year 2005.
If this isn't what you want, please post some sample data and desired output and I'll revise my suggestion.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
HI HTH
Thanks for your Help. Its working fine
Kind regards
Vijay
Have a nice day
-
Hi MTH
If sales year more than 1 Year
How can i write the scripts
CREATE TABLE #sales
(
product varchar(20),
sqty int default(0),
year in
jan int default(0),
feb int default(0),
 
--- Thank MTH
update #sales
SET sqty=pf.qty,
year=pf.sales_year
jan=pf.jan
feb=pf.feb,
FROM (
SELECT
product,
year=year(invoice_date) as sales_year
SUM(qty) as qty,
SUM(CASE WHEN MONTH(invoice_date) = 1 THEN (qty) else 0 END) as jan,
SUM(CASE WHEN MONTH(invoice_date) = 2 THEN (qty) else 0 END) AS feb
FROM
dbo.sales_det
WHERE YEAR(invoice_date)
GROUP BY product,year(invoice_date)
) pf
where #sales.product=pf.product
Kind regards
vijay
-
btw HTH stands for Hope that helps.
Close, but not quite.
Create the table sales and populate the years and products you want. (I assume you initially populate it because not all the products are in the salesdet table. Is that right?)
UPDATE #Sales
SET sQty = Agg.qty, Jan=Agg.jan, Feb=Agg.feb
FROM (SELECT
product,
YEAR(invoice_date) AS yr,
SUM(qty) AS qty,
SUM(CASE WHEN MONTH(invoice_date) = 1
THEN ISNULL(qty,0)
ELSE 0
END) AS Jan,
SUM(CASE WHEN MONTH(invoice_date) = 2
THEN ISNULL(qty,0)
ELSE 0
END) AS Feb
FROM
dbo.sales_det
GROUP BY product, YEAR(invoiceDate)) Agg
WHERE Agg.product = #Sales.Product AND Agg.yr = #Sales.Year
If all your products are in sales_det then you don't need the initial INSERT into #sales (product) ...
and you can just create the table then
INSERT INTO #Sales (Product, Year, sqty, Jan, Feb)
SELECT
product,
YEAR(invoice_date) AS yr,
SUM(qty) AS qty,
SUM(CASE WHEN MONTH(invoice_date) = 1
THEN ISNULL(qty,0)
ELSE 0
END) AS Jan,
SUM(CASE WHEN MONTH(invoice_date) = 2
THEN ISNULL(qty,0)
ELSE 0
END) AS Feb
FROM
dbo.sales_det
GROUP BY product, YEAR(invoiceDate)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Actually, come to think of it, the initial insert that you do to insert all the products is unnecessary. If there's a possibility that there will be products in Product_Forcast that aren't in Sales_det, use this.
INSERT INTO #Sales (Product, Year, sqty, Jan, Feb)
SELECT
pf.product,
YEAR(invoice_date) AS yr,
SUM(qty) AS qty,
SUM(CASE WHEN MONTH(invoice_date) = 1
THEN ISNULL(qty,0)
ELSE 0
END) AS Jan,
SUM(CASE WHEN MONTH(invoice_date) = 2
THEN ISNULL(qty,0)
ELSE 0
END) AS Feb
FROM
dbo.product_forecast pf LEFT OUTER JOIN dbo.sales_det ON pf.Product=Sales_det.Product
GROUP BY pf.product, YEAR(invoiceDate)
The left outer join will ensure that you get all products in product_forecast and matching sales values, where they exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply