Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Selecting all records in each group after a flagged record in that group in SQL


  • Please log in to reply
1 reply to this topic

#1 hispaladin

hispaladin

  • Members
  • 245 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Middle of a corn field
  • Local time:04:37 PM

Posted 10 June 2015 - 12:06 PM

OK, I tried to make the title as accurate as possible and still fit in the topic section.  I have a database with 3 tables in it -- a product table that has 3 columns: ID, productName, void--, --a tank table with 5 columns: TankID, Tank(name of the tank), Capacity, ProductID(foreign key referencing the product table), and void--, --a log table with 6 columns: ID, TankID(FK to the tank table), ticket, gallons, PhysicalInv, void--

In all the tables the void column is a bit value that is there to allow the record to be filtered out of all results without removing it from the database.  The idea is that users will be able to enter a ticket for each product that is moved into/out of the tanks and the report will sum all those tickets together to show the running balance of the tanks.  The catch that I am running into is when they run a physical inventory on the tanks I want the query to start from that number clean each time and not access the records from the previous tickets.  

So if I have these tables

Product Table
ProductID, ProductName, Void
1          chem1        0
2          chem2        0
3          chem3        0

Tank Table
TankID, TankName, Capacity, ProductID, Void
1       East50    50000     1          0
2       West50    50000     2          0
3       North30   30000     3          0

Log Table
ID, TankID, Ticket, Gallons, PhysicalInv, Void
1   1       1234    500      0            0
2   2       1235    300      0            0
3   3       1236    150      0            0
4   1       9999    4000     1            0
5   2       9999    4000     1            0
6   3       9999    3000     1            0
7   1       1239    -500     0            0

 

I would want to get this result

 

Tank,   Product, Capacity, Gallons, EmptySpace
East50  chem1    
50000     3500     46500
West50  chem2    50000     4000     46000
North30 chem3    30000     3000     27000

 

I have tried this but (obviously it didn't work or I wouldn't be asking here) it only selects the last record with the inventory bit flag marked and not the last one for each group

select t.Tank as Tank, p.ProductName as product, t.Capacity as Capacity, sum(l.Gallons) as Total, t.Capacity - sum(l.Gallons) as EmptySpace
	from AGP_BlkInv_Log as l join AGP_BlkInv_Tanks as t on l.TankID = t.TankID join AGP_BlkInv_Products as p on t.ProductID = p.ProductID
	where l.ID >= (select top 1 ID from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID, ID order by ID desc) and t.Void = 0 and t.Tank
	group by t.Tank, p.ProductName, t.Capacity

This is for a new project so the database structure is not set in stone so if there is a better way to do it by changing some of the structure then that is still a possibility.

Thanks for any help you may have



BC AdBot (Login to Remove)

 


#2 hispaladin

hispaladin
  • Topic Starter

  • Members
  • 245 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Middle of a corn field
  • Local time:04:37 PM

Posted 11 June 2015 - 10:13 AM

WOOHOO!!!  I got it working!  I used a cross apply to join the tables to the select result that I was trying to use in the where clause.  Here is the code that I used to get it working.

	select t.Tank as Tank, p.ProductName as product, t.Capacity as Capacity, sum(l.Gallons) as Total, t.Capacity - sum(l.Gallons) as EmptySpace
	from AGP_BlkInv_Log as l join AGP_BlkInv_Tanks as t on l.TankID = t.TankID join AGP_BlkInv_Products as p on t.ProductID = p.ProductID
	cross apply (select max(ID) as ID, TankID from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID) as c
	where l.ID >= c.ID and l.TankID = c.TankID
	group by t.Tank, p.ProductName, t.Capacity





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users