May 2, 2013 at 7:23 am
I am getting my data of current year even after adding range date. i tried everything but not found way to understand what i done wrong.
[Code]
PROCEDURE [dbo].[procClosingStock]
-- Add the parameters for the stored procedure here
(
@ITEM_ID1 int
--@PreviousYear date,
--@currentYear date
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
create table #TempClosingStock
(
Closing_Soh int,
item_id int,
created_date date
)
(
select SUM(stock_quantity),item_id,Created_Date from [Stock_Header] where Stock_Type='stock in' and item_id=@item_id1 group by Item_ID,Created_Date
)
update #TempClosingStock
set Closing_Soh=Closing_Soh-ISNULL((select SUM(stock_quantity) from [Stock_Header] where Stock_Type='stock out' and item_id=@item_id1 group by Item_ID),0)
-- Insert statements for procedure here
SELECT Closing_Soh,item_id,created_date from #TempClosingStock where item_id=@item_id1 and created_date between '2012-04-1T00:00:00.000' AND '2013-01-3T00:00:00.000'
drop table #TempClosingStock
END
[/code]
May 2, 2013 at 7:33 am
vks.gautam1 (5/2/2013)
I am getting my data of current year even after adding range date. i tried everything but not found way to understand what i done wrong.
Hi and welcome to SSC. Can you please explain what the issue is. We don't know what your data looks like or what you are trying to do.
I ran your code through a formatter so it is easier to read:
PROCEDURE [dbo].[procClosingStock]
-- Add the parameters for the stored procedure here
(@ITEM_ID1 INT
--@PreviousYear date,
--@currentYear date
) AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TempClosingStock (
Closing_Soh INT
,item_id INT
,created_date DATE
) (
SELECT SUM(stock_quantity)
,item_id
,Created_Date FROM [Stock_Header] WHERE Stock_Type = 'stock in'
AND item_id = @item_id1 GROUP BY Item_ID
,Created_Date
)
UPDATE #TempClosingStock
SET Closing_Soh = Closing_Soh - ISNULL((
SELECT SUM(stock_quantity)
FROM [Stock_Header]
WHERE Stock_Type = 'stock out'
AND item_id = @item_id1
GROUP BY Item_ID
), 0)
-- Insert statements for procedure here
SELECT Closing_Soh
,item_id
,created_date
FROM #TempClosingStock
WHERE item_id = @item_id1
AND created_date BETWEEN '2012-04-1T00:00:00.000'
AND '2013-01-3T00:00:00.000'
DROP TABLE #TempClosingStock
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 7:35 am
Hi
You have this in your SP:
SELECT Closing_Soh,item_id,created_date
from #TempClosingStock where item_id=@item_id1 and
created_date between '2012-04-1T00:00:00.000' AND '2013-01-3T00:00:00.000'
Is '2012-04-1T00:00:00.000' AND '2013-01-3T00:00:00.000' ok? What is the datetime format at your site?
Why don't you pass dates as arguments instead of having them hard coded?
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
May 2, 2013 at 7:39 am
I think I may see part of the problem. You create a temp table but you never populate it. Then you try to update it and select from it.
CREATE TABLE #TempClosingStock (
Closing_Soh INT
,item_id INT
,created_date DATE
) (
SELECT SUM(stock_quantity)
,item_id
,Created_Date FROM [Stock_Header] WHERE Stock_Type = 'stock in'
AND item_id = @item_id1 GROUP BY Item_ID
,Created_Date
)
I am guessing that should be
CREATE TABLE #TempClosingStock
(
Closing_Soh INT
,item_id INT
,created_date DATE
)
insert #TempClosingStock
SELECT SUM(stock_quantity)
,item_id
,Created_Date
FROM [Stock_Header]
WHERE Stock_Type = 'stock in'
AND item_id = @item_id1
and created_date > '2012-04-1T00:00:00.000'
AND created_date < '2013-01-3T00:00:00.000'
GROUP BY Item_ID, Created_Date
I moved the date condition here because you if you are going to filter them out at the end there is no need to populate your temp table with data you don't need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 7:40 am
ok i tell you guys i am getting my records even out of my criteria date.
i have used date time picker from my application to send criteria dates.. i want data between two dated like " between 1-apr-2012 and 31-12-2012 .
but im getting data of 2013 also..
May 2, 2013 at 7:51 am
thnx It is working now as per you recommnded
May 2, 2013 at 7:56 am
vks.gautam1 (5/2/2013)
thnx It is working now as per you recommnded
The big question is do you know why that fixed it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply