Warranty details help
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
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.
