I have a work table for our drivers routes that will show the selected route and any additional days that customer is serviced. The route is created in a work table and then the additional service dates are updated with a query. Using Access I could do it with this statement:
UPDATE (dbo_tblWrkRouteSheet INNER JOIN (dbo_ServiceSchedule INNER JOIN dbo_Service ON dbo_ServiceSchedule.ServiceUID = dbo_Service.ServiceUID) ON dbo_tblWrkRouteSheet.serviceid = dbo_Service.ServiceID) INNER JOIN dbo_Site ON (dbo_Site.ServiceLocation = dbo_Service.ServiceLocation) AND (dbo_tblWrkRouteSheet.siteid = dbo_Site.SiteID) SET dbo_tblWrkRouteSheet.monrte = IIf([dbo_Serviceschedule].[dayofweek]=1,'Mo',[monrte]), dbo_tblWrkRouteSheet.turte = IIf([dbo_Serviceschedule].[dayofweek]=2,'Tu',[turte]), dbo_tblWrkRouteSheet.wedrte = IIf([dbo_Serviceschedule].[dayofweek]=3,'We',[wedrte]), dbo_tblWrkRouteSheet.thurte = IIf([dbo_Serviceschedule].[dayofweek]=4,'Th',[thurte]), dbo_tblWrkRouteSheet.frirte = IIf([dbo_Serviceschedule].[dayofweek]=5,'Fr',[frirte]), dbo_tblWrkRouteSheet.satrte = IIf([dbo_Serviceschedule].[dayofweek]=6,'Sa',[satrte]), dbo_tblWrkRouteSheet.sunrte = IIf([dbo_Serviceschedule].[dayofweek]=7,'Su',[sunrte])
WHERE (((dbo_ServiceSchedule.EndDate) Is Null));
I'm now converting the program to VB and am trying to build the appropriate SQL statement and have been trying it in SQL Server 2012 Query Analyzer but I'm not getting the expected results. With the following Query:
Select monrte, turte, wedrte, thurte,frirte from tblwrkroutesheet
If a customer is on a Monday/Thursday Route the result I should get is
But instead I'm getting
the table tblwrkroutesheet contains the details for one specific route/day and the table serviceschedule contains all the days of service so I want to update the tblwrkroutesheet table with the days that the customer is serviced. What I'm really looking for that I can't find is something like:
update tblwrkroutesheet set
Select Case ServiceSchedule.dayofweek
Case '1' tblwrkroutesheet.monrte = 'mo'
Case '2' tblwrkroutesheet.turte = 'tu'
Case '3' tblwrkroutesheet.wedrte = 'we'
That way only one of the update statements are being processed. I haven't found a way to do that and my current SQL code is:
update wrk set
wrk.monrte = case when sch.dayofweek = '1' then 'mo' else wrk.monrte end,
wrk.turte = case when sch.dayofweek = '2' then 'tu' else wrk.turte end,
wrk.wedrte = case when sch.dayofweek = '3' then 'we' else wrk.wedrte end,
wrk.thurte = case when sch.dayofweek = '4' then 'th' else wrk.thurte end,
wrk.frirte = case when sch.dayofweek = '5' then 'fr' else wrk.frirte end
from tblwrkroutesheet wrk
inner join [twrscav].dbo.service sv on wrk.serviceid = sv.serviceid
inner join [twrscav].dbo.serviceschedule sch on sch.serviceuid = sv.serviceuid
inner join [twrscav].dbo.site si on (sv.servicelocation = si.servicelocation) and (si.siteid = wrk.siteid)
where sch.enddate is null
This code though seems to overwrite other days that may have already been updated. Any idea's on how I can update different columns in tblWrkRouteSheet based on the value in serviceschedule.dayofweek?
By the way I will be running the Querey against datbases on SQL Server 2012 and SQL Server 2005 so it needs to work with both SQL versions