Hi All,

Im using this Query im getting all the details  as below but i want only the start and end date with one row..any query ?

select distinct UCASE(m.NAME) as NAME, m.USER_FULLNAME,m.USER, m.OS_NAME, m.CS_MANUFACTURER, m.CS_MODEL,w.SERVICE_LEVEL_DESCRIPTION, w.SERVICE_TAG, w.START_DATE,w.END_DATE ,DATEDIFF(Now(), w.START_DATE) as DayCount
from MACHINE as m, DELL_WARRANTY as w 
where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG
order by w.START_DATE DESC

SERVICE_TAG SERVICE_LEVEL_DESCRIPTION   START_DATE END_DATE NAME
15FW4XX Parts Only Warranty 2/3/12 13:00 2/4/13 12:59  DTP0111
15FW4XX Parts Only Warranty 2/3/10 13:00 2/4/12 12:59  DTP0111
15FW4XX Next Business Day response 2/3/12 13:00 2/4/13 12:59  DTP0111
15FW4XX Next Business Day response 2/3/10 13:00 2/4/12 12:59  DTP0111
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
You need to select just the Max dates. Here is my warranty report:
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE,M.USER_LOGGED AS LAST_LOGGED_IN_USER, DW.SERVICE_LEVEL_CODE, 
DW.SERVICE_LEVEL_DESCRIPTION, MAX(DW.END_DATE) AS EXPIRATION_DATE 
FROM DELL_WARRANTY DW JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG) 
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG) 
WHERE M.CS_MANUFACTURER LIKE '%dell%' 
AND M.BIOS_SERIAL_NUMBER!='' 
AND DA.DISABLED != 1 
This includes a few different fields but should demonstrate the concept you need to apply.

Answered 08/18/2016 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity