daily pastebin goal
27%
SHARE
TWEET

HI

a guest May 16th, 2018 88 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ```SELECT        UJ.JobID, UJ.UserID, UJ.DriverID, UJ.VehicleID, UJ.SubscriberServiceID, UJ.ProviderID, U.SubscriberID, UJ.TrackingTypeID, UJ.Date, UJ.State, UJ.AppointmentTime, UJ.DateCreated, FROM_UA.Name AS FromAddress,
  2.                          FROM_UA.Postcode AS From_PostCode, TO_UA.Name AS ToAddress, TO_UA.Postcode AS To_PostCode, SE.Name AS ServiceName, S.Name AS SubscriberName, P.Name AS ProviderName, V.RegistrationNumber,
  3.                          V.VehicleMake, V.VehicleModel, V.VehicleColour, ISNULL(V.WheelChairAccessible, 0) AS WheelChairAccessible, DRIVER.Forenames AS DriverFornames, DRIVER.Surname AS DriverSurname, TT.TrackingType, UJ.JourneyTime,
  4.                          UJ.JourneyDistance, UJ.JourneyDuration, UJ.PickUpTime, CASE WHEN UJ.TrackingTypeID = 6 THEN CASE WHEN JT.StartTime IS NOT NULL THEN JT.StartTime ELSE UJ.AppointmentTime END ELSE NULL
  5.                          END AS CompletedTime, CASE WHEN (CASE WHEN UJ.TrackingTypeID = 6 THEN CASE WHEN JT.StartTime IS NOT NULL THEN JT.StartTime ELSE UJ.AppointmentTime END ELSE UJ.AppointmentTime END)
  6.                          <= UJ.AppointmentTime THEN 1 ELSE 0 END AS CompletedOnTime, ABS(UJ.UnitID) AS UnitID, UJ.FromAddress AS FromUserAddressID, UJ.ToAddress AS ToUserAddressID, UJ.RequiresWheelChairVehicle,
  7.                          CASE WHEN UJ.[State] = 3 THEN 1 ELSE 0 END AS Cancelled, PA.Forenames + ' ' + PA.Surname AS PatientName, UJ.JobChainID, UJ.ReferrerID, CASE WHEN UJ.ReferrerID > 0 THEN 1 ELSE 0 END AS CreatedByReferrer,
  8.                          UJ.ContractID, UJ.PickUpOrder, UJ.DestinationOrder, CASE WHEN JTEnRoute.JobID IS NOT NULL AND JTEnRoute.TrackingFile IS NOT NULL THEN 1 ELSE 0 END AS TrackingDataAvailable, SE.ServiceID AS UJServiceID,
  9.                          UJ.CancelledReason, UJ.CancelledByUserID, CU.Forenames + ' ' + CU.Surname AS CancelledBy, CASE WHEN UJ.UserID = UJ.CancelledByUserID THEN 1 ELSE 0 END AS CancelledByPatient, UJ.CancelledDate,
  10.                          UJ.CancellationReasonID, CR.CancellationReason, UJ.JobTypeID, RJT.JobType, UJ.Notes, UJ.VisitDuration, UJ.VisitTimescale, V.CallSign, UJ.BlockBookingScheduleID,
  11.                          CASE WHEN UJ.ToAddress < 0 THEN 1 ELSE 0 END AS JourneyToUnit, ISNULL(JTStart.StartTime, UJ.AppointmentTime) AS StartTime, 0 AS IsMultiJob, UJ.BuyerID
  12. FROM            dbo.UserJobs AS UJ INNER JOIN
  13.                          dbo.[User] AS PA ON UJ.UserID = PA.UserID INNER JOIN
  14.                          dbo.vwUserAddresses AS FROM_UA ON UJ.FromAddress = FROM_UA.UserAddressID AND UJ.UserID = FROM_UA.UserID INNER JOIN
  15.                          dbo.vwUserAddresses AS TO_UA ON UJ.ToAddress = TO_UA.UserAddressID AND UJ.UserID = TO_UA.UserID LEFT OUTER JOIN
  16.                          dbo.Provider AS P ON P.ProviderID = UJ.ProviderID LEFT OUTER JOIN
  17.                          dbo.Vehicle AS V ON V.VehicleID = UJ.VehicleID LEFT OUTER JOIN
  18.                          dbo.[User] AS DRIVER ON UJ.DriverID = DRIVER.UserID LEFT OUTER JOIN
  19.                          dbo.ref_TrackingType AS TT ON TT.TrackingTypeID = UJ.TrackingTypeID LEFT OUTER JOIN
  20.                              (SELECT        JobTrackingID, JobID, TrackingTypeID, TrackingData, TrackingFile, DateCreated, JobChainID, TotalDistance, TotalDuration, TotalTime, StartTime, EndTime, Notes, CreatedByID, CreatedByDriver
  21.                                FROM            dbo.Job_Tracking
  22.                                WHERE        (TrackingTypeID = 5)) AS JT ON UJ.JobID = JT.JobID LEFT OUTER JOIN
  23.                              (SELECT        JobTrackingID, JobID, TrackingTypeID, TrackingData, TrackingFile, DateCreated, JobChainID, TotalDistance, TotalDuration, TotalTime, StartTime, EndTime, Notes, CreatedByID, CreatedByDriver
  24.                                FROM            dbo.Job_Tracking
  25.                                WHERE        (TrackingTypeID = 4)) AS JTEnRoute ON UJ.JobID = JTEnRoute.JobID LEFT OUTER JOIN
  26.                              (SELECT        JobTrackingID, JobID, TrackingTypeID, TrackingData, TrackingFile, DateCreated, JobChainID, TotalDistance, TotalDuration, TotalTime, StartTime, EndTime, Notes, CreatedByID, CreatedByDriver
  27.                                FROM            dbo.Job_Tracking
  28.                                WHERE        (TrackingTypeID = 2)) AS JTStart ON UJ.JobID = JTStart.JobID INNER JOIN
  29.                          dbo.Unit AS U ON UJ.UnitID = U.UnitID INNER JOIN
  30.                          dbo.Subscriber AS S ON U.SubscriberID = S.SubscriberID INNER JOIN
  31.                          dbo.Service AS SE ON U.ServiceID = SE.ServiceID LEFT OUTER JOIN
  32.                          dbo.[User] AS CU ON UJ.CancelledByUserID = CU.UserID LEFT OUTER JOIN
  33.                          dbo.ref_CancellationReason AS CR ON UJ.CancellationReasonID = CR.CancellationReasonID INNER JOIN
  34.                          dbo.ref_JobType AS RJT ON UJ.JobTypeID = RJT.JobTypeID```
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top