ASSETS ASSIGNED TO AN ACCOUNTABLE PERSON

select (First_Name + ' ' + Last_Name) "Accountable Person", I4201 "Asset No.", i4248 "Mfg", i4203 "Model", i4263 "Description", i4206 "Serial No.", i4243 "Project", I4244 "Home Location", format(c2301, 'MM/dd/yyyy') "Cal Date", I4210 "Calibrated", i4299 "Active-Inactive",

(CASE 
WHEN DATEDIFF(day, GETDATE(), C2303) > 7 THEN '<span class="codegreen">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) between 0 and 7 THEN '<span class="codeyellow">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) < 0 THEN '<span class="codered">' + format(C2303, 'MM/dd/yyyy') + '</span>'
ELSE format(C2303, 'MM/dd/yyyy')
END) "Due Date" 

from mt.inventory, mt.calibration, mt.mrg4202 where mt.inventory.mtag = mt.calibration.mtag and "merge" = i4202 and
c2339 = 1 and i4202 = '006016' 

--- Added the part below for Jackie Only
---and I4202 = '001490'

INSTRUMENTS CHECKED Out

select I4201 "Instrument", i4248 "Mfg", i4203 "Model", i4263 "Description", i4206 "Serial #", C2303 "Cal Due Date", 
L2824 "Check Out Date" from mt.inventory, Mt.location, mt.calibration where Mt.inventory.mtag = mt.location.mtag and mt.inventory.mtag = mt.calibration.mtag 
and c2339 = 1 and L2815 = 1 and L2825 is null and L2801 like '%005436%'

RECALL FOR THE NEXT 30 DAYS

select I4201 "Asset No.", i4248 "Mfg", i4203 "Model", i4263 "Description", i4206 "Serial No.", i4243 "Project", I4244 "Home Location", format(c2301, 'MM/dd/yyyy') "Cal Date", 

(CASE 
WHEN DATEDIFF(day, GETDATE(), C2303) > 7 THEN '<span class="codegreen">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) between 0 and 7 THEN '<span class="codeyellow">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) < 0 THEN '<span class="codered">' + format(C2303, 'MM/dd/yyyy') + '</span>'
ELSE format(C2303, 'MM/dd/yyyy')
END) "Due Date", Email_Address "email" 

from mt.inventory, mt.calibration, mt.mrg4202 where mt.inventory.mtag = mt.calibration.mtag and "merge" = i4202 and
c2339 = 1 and i4225 = 'AVAIL' and i4299 = 'A' and C2303 <= (GETDATE() + 30) and i4210 = 'YES' 

--- Added the part below for Jackie Only
---and I4202 = '001490'

RECALL TEST

select I4201 "Asset No.", i4248 "Mfg", i4203 "Model", i4263 "Description", i4206 "Serial No.", i4243 "Project", I4244 "Home Location", format(c2301, 'MM/dd/yyyy') "Cal Date", 

(CASE 
WHEN DATEDIFF(day, GETDATE(), C2303) > 7 THEN '<span style="background-color:green;color:white;">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) between 0 and 7 THEN '<span style="background-color:yellow;color:black;">' + format(C2303, 'MM/dd/yyyy') + '</span>'
WHEN DATEDIFF(day, GETDATE(), C2303) < 0 THEN '<span style="background-color:red;color:white;">' + format(C2303, 'MM/dd/yyyy') + '</span>'
ELSE format(C2303, 'MM/dd/yyyy')
END) "Due Date", Email_Address "email" 

from mt.inventory, mt.calibration, mt.mrg4202 where mt.inventory.mtag = mt.calibration.mtag and "merge" = i4202 and
c2339 = 1 and i4225 = 'AVAIL' and i4299 = 'A' and C2303 <= (GETDATE() + 30) and i4210 = 'YES' 

--- Added the part below for Jackie Only
and I4202 = '001490'