March 10, 2010 at 2:41 am
Hi,.
I have a table for example
table 1
columns are : Item_no , stock1,stock2 ,date
rows : 0001 ,5 ,5 ,'2010-01-01'
0001 ,6 ,7 ,'2010-01-02'
0001 ,0 ,5 ,'2010-01-03'
0002 ,2 ,5 ,'2010-01-02'
But here I need to retive the stock sum
like
sum(stock1) =,'2010-01-02'
And
sum(Stock2) <=,'2010-01-04'
can we make this in single query?
I tried here its working but takes too time...
Select sum(stock1),
(Select sum(stock2),item_no from table1
where date = '2010-01-02' group by item_no) as [stock on hand],
item_no from table1
where date = '2010-01-02'
group by item_no
March 10, 2010 at 2:59 am
Saravanan_tvr (3/10/2010)
I tried here its working but takes too time...Select sum(stock1),
(Select sum(stock2),item_no from table1
where date = '2010-01-02' group by item_no) as [stock on hand],
item_no from table1
where date = '2010-01-02'
group by item_no
This query doesnt work and its parameter also doesnt macth to ur requirement (same dates)
i tried with below querydeclare @t table (Item_no int , stock1 int,stock2 int ,date datetime )
insert into @t
select 0001 ,5 ,5 ,'2010-01-01' union select
0001 ,6 ,7 ,'2010-01-02' union select
0001 ,0 ,5 ,'2010-01-03' union select
0002 ,2 ,5 ,'2010-01-02'
Select sum(stock1),
(Select sum(stock2),item_no from @t
where date = '2010-01-02' group by item_no) as [stock on hand],
item_no from @t
where date = '2010-01-02'
group by item_no
Moreover, always post your query as i did [use (code = "sql") (/code)] shortcuts form left pane to get faster results
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 10, 2010 at 3:32 am
Does this give you the result you want?
declare @t table (Item_no int , stock1 int,stock2 int ,date datetime )
insert into @t
select 0001 ,5 ,5 ,'2010-01-01' union select
0001 ,6 ,7 ,'2010-01-02' union select
0001 ,0 ,5 ,'2010-01-03' union select
0002 ,2 ,5 ,'2010-01-02'
Select sum(case when date = '2010-01-02' then stock1 else 0 end),
sum(case when date <= '2010-01-04' then stock2 else 0 end) as [stock on hand],
item_no
from @t
where date < '2010-01-04'
group by item_no
/Markus
March 10, 2010 at 6:00 pm
Saravanan_tvr (3/10/2010)
Hi,.I have a table for example
table 1
columns are : Item_no , stock1,stock2 ,date
rows : 0001 ,5 ,5 ,'2010-01-01'
0001 ,6 ,7 ,'2010-01-02'
0001 ,0 ,5 ,'2010-01-03'
0002 ,2 ,5 ,'2010-01-02'
But here I need to retive the stock sum
like
sum(stock1) =,'2010-01-02'
And
sum(Stock2) <=,'2010-01-04'
can we make this in single query?
I tried here its working but takes too time...
Select sum(stock1),
(Select sum(stock2),item_no from table1
where date = '2010-01-02' group by item_no) as [stock on hand],
item_no from table1
where date = '2010-01-02'
group by item_no
For faster responses in the future, take a look at the article at the first link in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2010 at 8:58 pm
Test setup
DECLARE @t
TABLE (
item_no INTEGER NOT NULL,
stock1 INTEGER NOT NULL,
stock2 INTEGER NOT NULL,
date DATETIME NOT NULL
);
INSERT @t
(item_no, stock1, stock2, date)
SELECT 0001 ,5 ,5 ,'2010-01-01' UNION ALL SELECT
0001 ,6 ,7 ,'2010-01-02' UNION ALL SELECT
0001 ,0 ,5 ,'2010-01-03' UNION ALL SELECT
0002 ,2 ,5 ,'2010-01-02';
Solution
SELECT item_no,
sum_1 = SUM(CASE WHEN date = '2010-01-02' THEN stock1 ELSE 0 END),
sum_2 = SUM(CASE WHEN date <= '2010-01-04' THEN stock2 ELSE 0 END)
FROM @t
GROUP BY
item_no;
Output
item_no sum_1 sum_2
======= ===== =====
1 6 17
2 2 5
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply