loop through items with sql query

i have the attached quey which returns the correct value for one item from our items database.  how can i change this query to work with all items in our database instead of having to run for one item at a time.

declare @Item nvarchar(15)
declare @DateF datetime
declare @DateT datetime

set @Item = '19168'
set @DateF = '01-01-2009'
set @DateT = getdate()

--READITEM 19168
select t_item as Item,t_stoc --from ttiitm001100 --where t_item = @Item -
--select 4873 -
-

(select sum(t_quan) from ttdinv700100   --Production Issue Always (+)
where t_item = @Item and t_kost = 8 and t_trdt >= @DateF and t_trdt <= @DateT)
--(62729)
+

(select sum(t_quan) from ttdinv700100   --Sales Order Delivery (-)
where t_item = @Item and t_kost = 6 and t_trdt >= @DateF and t_trdt <= @DateT)
--(61513)
+

(select sum(t_quan) from ttdinv700100  --Production Issue (-)
where t_item = @Item and t_kost = 7 and t_trdt >= @DateF and t_trdt <= @DateT)
--(300)
--+ 306
+

(select replace(sum(t_quan),'-','') from ttdinv700100  --Inventory Adjustment Could be either (+/-)
where t_item = @Item and t_kost = 1 and t_trdt >= @DateF and t_trdt <= @DateT and t_quan < 0)
-- (-306)
-

(select sum(t_quan) from ttdinv700100   --Inventory Adjustment Could be either (+/-)
where t_item = @Item and t_kost = 1 and t_trdt >= @DateF and t_trdt <= @DateT and t_quan > 0)
--(1997)

as [Inventory]from ttiitm001100 where t_item = @Item

--Number Should be 2266
rmardisAsked:
Who is Participating?
 
Reza RadConsultant, TrainerCommented:
you can use while inside your sql scripts like this

declare @countdown int
select @countdown=count(Item) from table1

while (@countdown>0)
begin
--put your code here
end
0
 
SharathData EngineerCommented:
You have commented lot of code. Can you explain what exactly you want?

I would suggest you to avoid loop if possible.
0
 
pcelbaCommented:
Try this:
declare @Item nvarchar(15)
declare @DateF datetime
declare @DateT datetime

set @Item = '19168'
set @DateF = '01-01-2009'
set @DateT = getdate()


SELECT i.t_item as Item, i.t_stoc + s.Sumt_kost AS [Inventory]
  FROM ttiitm001100 i
 INNER JOIN (
       SELECT t_item, SUM(CASE WHEN t_kost IN (1,8) THEN -t_quan WHEN t_kost IN (6,7) THEN t_quan ELSE 0 END) Sumt_kost
         FROM ttdinv700100
        WHERE t_trdt BETWEEN @DateF AND @DateT
        GROUP BY t_item ) s
    ON s.t_item = i.t_item

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pcelbaCommented:
In the case some items from ttiitm001100 do not have records in ttdinv700100 you need LEFT JOIN:
--declare @Item nvarchar(15) 
declare @DateF datetime 
declare @DateT datetime 
 
--set @Item = '19168' 
set @DateF = '01-01-2009' 
set @DateT = getdate() 
 
 
SELECT i.t_item as Item, i.t_stoc + ISNULL(s.Sumt_kost,0) AS [Inventory] 
  FROM ttiitm001100 i 
  LEFT JOIN ( 
       SELECT t_item, SUM(CASE WHEN t_kost IN (1,8) THEN -t_quan WHEN t_kost IN (6,7) THEN t_quan ELSE 0 END) Sumt_kost 
         FROM ttdinv700100 
        WHERE t_trdt BETWEEN @DateF AND @DateT 
        GROUP BY t_item ) s 
    ON s.t_item = i.t_item

Open in new window

0
 
rmardisAuthor Commented:
Thank you, worked like a charm

Sorry so late but i thought i had closed this already.

Thanks again
0
 
pcelbaCommented:
Hmm, what's wrong on my solution?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.