I have a table that stores inventory tickets for a bulk chemical storage facility. I have it set so that the user enters a ticket for each shuttle of chemical that ships out or comes in. The user also does a physical inventory each month(or sometimes more often) and the record is stored just like a regular ticket only a Physical Inventory bit column is checked. The table has a column for the ID (identity), ticket number, storage tank, gallons moved, and physical inventory. I need to run a query that will sum the total gallons for each tank for every ticket after the last inventory ticket(including the inventory ticket for each tank). The query that I have right now sums everything after the last inventory ticket created not the last one for each tank.

```
    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 max(ID) from dbo.AGP_BlkInv_Log where PhyisicalInv = 1 group by TankID, ID) and t.Void = 0 and t.Tank not like 'f%'
group by t.Tank, p.ProductName, t.Capacity

```

I have looked at cross apply and partition by and they might be the solution that I need but I don't understand how to use them to get what I want.  

Also this is a new project so the database structure could be changed if it would make things easier.

I am running MS-SQL server 2012 

Edit: Not sure if this is what you are looking for but here is the script to create the tables

```
`CREATE TABLE [dbo].[AGP_BlkInv_Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nchar](50) NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Products] PRIMARY KEY CLUSTERED 
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Products] ADD  CONSTRAINT 
[DF_AGP_BlkInv_Products_Void]  DEFAULT ((0)) FOR [Void]
GO`

CREATE TABLE [dbo].[AGP_BlkInv_Tanks](
[TankID] [int] IDENTITY(1,1) NOT NULL,
[Tank] [nchar](25) NOT NULL,
[Capacity] [float] NOT NULL,
[ProductID] [int] NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Tanks] PRIMARY KEY CLUSTERED 
(
[TankID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks] ADD  CONSTRAINT
[DF_AGP_BlkInv_Tanks_Void] 
DEFAULT ((0)) FOR [Void]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks]  WITH CHECK ADD  CONSTRAINT
[FK_AGP_BlkInv_Tanks_AGP_BlkInv_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[AGP_BlkInv_Products] ([ProductID])
GO

ALTER TABLE [dbo].[AGP_BlkInv_Tanks] CHECK CONSTRAINT 
[FK_AGP_BlkInv_Tanks_AGP_BlkInv_Products]
GO

CREATE TABLE [dbo].[AGP_BlkInv_Log](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TankID] [int] NOT NULL,
[Ticket] [int] NOT NULL,
[Gallons] [float] NOT NULL,
[PhyisicalInv] [bit] NOT NULL,
[Void] [bit] NOT NULL,
CONSTRAINT [PK_AGP_BlkInv_Log] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] ADD  CONSTRAINT  
[DF_AGP_BlkInv_Log_PhyisicalInv]  DEFAULT ((0)) FOR [PhyisicalInv]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] ADD  CONSTRAINT [DF_AGP_BlkInv_Log_Void]  
DEFAULT ((0)) FOR [Void]
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log]  WITH CHECK ADD  CONSTRAINT           
[FK_AGP_BlkInv_Log_AGP_BlkInv_Tanks] FOREIGN KEY([TankID])
REFERENCES [dbo].[AGP_BlkInv_Tanks] ([TankID])
GO

ALTER TABLE [dbo].[AGP_BlkInv_Log] CHECK CONSTRAINT                    
[FK_AGP_BlkInv_Log_AGP_BlkInv_Tanks]
GO

```

The product table is linked to the tank by the ProductID, and the tank table is linked to the log by the TankID

HMM... I cant post images so I will do my best to type out some sample data

```
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

```

The result that I would want to get back would be 

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

```

The query should ignore all entries before the last inventory entry and sum all the entries per tank including the last set of inventory entries.