May 30, 2014 at 12:11 am
I am trying to pull in warranty expiry dates that have been entered as strings and then perform a calculation as to which ones are expired, about to expire within 90 days or will expire in more than 90 days. I have got the query to a point where I have converted it to a datetime field (though I'm sure I used the right value to just give me the date, its giving me date and time) and have it printing out the machine name and the warranty for that machine.
SELECT
machNameTab.machName
,convert(date,auditRsltManualFieldValues.fieldValue,103) AS warranty
FROM
machNameTab
INNER JOIN auditRsltManualFieldValues
ON machNameTab.agentGuid = auditRsltManualFieldValues.agentGuid
WHERE
machNameTab.groupName = N'root.company'
AND auditRsltManualFieldValues.fieldNameFK = 958472722796011
What I need to do next is change this so instead of just spitting out all the dates as 'Warranty', I want it to give me the fields 'Expired', 'Expiring' (expiry date is within 90 days of the current date) and 90Days (expiry date is over 90 days from the current date)
May 30, 2014 at 11:28 am
Not 100% sure what output you want: the warranty date in a separate column based on its status or a status column itself, like below.
SELECT
machNameTab.machName
,warranty
,CASE WHEN warranty <= GETDATE() THEN 'Expired'
WHEN warranty <= DATEADD(DAY, 90, GETDATE()) THEN 'Expiring'
ELSE '90Days' END AS warranty_status
FROM
machNameTab
INNER JOIN auditRsltManualFieldValues
ON machNameTab.agentGuid = auditRsltManualFieldValues.agentGuid
CROSS APPLY (
SELECT convert(date,auditRsltManualFieldValues.fieldValue,103) AS warranty
) AS ca1
WHERE
machNameTab.groupName = N'root.company'
AND auditRsltManualFieldValues.fieldNameFK = 958472722796011
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply