• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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
0
rmardis
Asked:
rmardis
1 Solution
 
Reza RadCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now