/* All gear that didn't make it back from a show within date range, include warehouse status of the booking */
DECLARE @StartDate AS VARCHAR(20)
DECLARE @EndDate AS VARCHAR(20)
SET @StartDate = '2024-03-30'
SET @EndDate = '2024-07-01'
select t.booking_no [Booking #],
B.showname [Show],
CONVERT(varchar,B.dDate,107) as [Out Date],
CONVERT(varchar,B.rDate,107) as [Expected Return Date],
t.product_code [Product],
I.trans_type_v41 [trans type],
CASE
WHEN t.stock_number = 65535 THEN ''
ELSE CAST(t.stock_number as varchar(MAX))
END as [Stock #],
isnull(a.ASSET_CODE,'') [Barcode],
isnull(a.DESCRIPTION,'') [Description] ,
CASE A.iDisposalType
WHEN 0 then 'Active'
WHEN 1 then 'Sold'
WHEN 2 then 'Lost'
WHEN 3 then 'Stolen'
When 4 then 'Written off'
ELSE '' END as [Asset Status],
sum(t.qtycheckedOut) [QTY OUT],
sum(t.Qtyreturned) [QTY Returned],
CAST(sum(i.price) as decimal(9,2)) as [Rental Price],
CAST(sum(m.retail_price) as decimal(9,2)) as [Retail Price],
CASE
WHEN B.status = 0 THEN 'Booked'
WHEN B.status = 1 THEN 'Checked Out'
WHEN B.status = 2 THEN 'Returned'
ELSE ''
END AS [Booking Status],
isnull(S.Salesperson_name,'') as [SalesPerson],
isnull(P.Salesperson_name,'') as [Project Manager]
from tblassetran T
Left outer join tblbookings b on b.booking_no = t.booking_no
left outer join tblAsset01 A on A.PRODUCT_COde = t.product_code and a.STOCK_NUMBER = t.stock_number
left outer join tblitemtran I on i.SubRentalLinkID = t.ItemTranID and i.booking_no_v32 = t.booking_no
left outer join tblinvmas m on m.product_code = t.product_code
left outer join tblSalesper S on b.Salesperson = s.salesperson_code
left outer join tblSalesper P on b.ProjectManager = P.salesperson_code
where ((t.ActOutDate > @StartDate AND t.ActInDate = '1980-01-01' AND b.rdate <= @EndDate ) AND ((T.qtycheckedOut <> T.Qtyreturned and i.trans_type_v41 = 0) )
OR
((t.ActOutDate > @StartDate AND t.ActInDate = '1980-01-01' AND b.rdate <= @EndDate ) AND (I.Bit_field_v41 & 0x08) <> 0 ) )
Group by t.booking_no,b.showName,b.dDate,b.rdate,t.product_code,i.trans_type_v41,t.stock_number,a.ASSET_CODE,a.DESCRIPTION,a.iDisposalType,
b.status,s.Salesperson_name,P.Salesperson_name
order by b.ddate,b.rdate,t.booking_no