July 21, 2009 at 5:57 pm
Hi Guys,
I am trying to generate vehicle service type due and date due for next service. I got three types of services
Service A - 30Days
Service B - 60Days
Service c - 90Days
Whenever user define the date it has to populate the service type due and the date for the next service.
Note: tbl need to hold count (e.g. Day1, 2, 3, 4...30 ) to identify the service type for each vehicle when user define specific date.
Below is the query I wrote to populate service type due, I need some assitance to develop exact dates. This query only generates service type due for veh but not the count on the days when user define.
tbl
VehID VehType ADays BDays CDays
SELECT CAST (VIT.[Veh ID]AS VARCHAR(10)) VehID
--,(VT.[ADays]) ADays
--,(VT.[BDays]) BDays
--,(VT.[CDays]) CDays
-- ,DaysSinceLastService = datediff(day, max(jc.date), GETDATE()) -- cast(null as integer)
,case
when datediff(day, max(jc.date), GETDATE()) >= (VT.[ADays]) AND datediff(day, max(jc.date), GETDATE()) < (VT.[BDays]) then 'A Service'
when datediff(day, max(jc.date), GETDATE()) >= (VT.[BDays]) then 'B Service'
else 'C Service'
end as ServiceTypeDue
FROM [dbo].[Vehicle Information Table] VIT
INNER JOIN [dbo].[VehTypeSerIntervals] VT
ON VT.[VehType]= VIT.[Veh Type]
LEFT JOIN [dbo].[Job Card Details] JC
ON JC. [Veh ID]= VIT. [Veh ID]
GROUP BY (VIT.[Veh ID])
,(VT.[ADays])
,(VT.[BDays])
,(VT.[CDays])
SAMPLE:
VehID ServiceTypeDue
001A Service
005A Service
007A Service
009A Service
010A Service
012A Service
014A Service
016B Service
017A Service
018B Service
IF you guys need more info let me know..
Thanks,
Dreamslogic
July 22, 2009 at 1:03 pm
Any chance you can post some DDL, DML and expected output so we can run queries against it?
July 22, 2009 at 1:13 pm
You might want to explain a little more what you want as well as the DDL and samples. Show the output samples as well.
If you need a number of days, you might need to use DATEDIFF in the SELECT list as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply