Sum Fares where the job date equals job date RRS feed

  • Question

  • User1568384800 posted

    I have this to retrieve a bunch of data. However, I've been trying to sum the Fares where job date is the same in a row. e.g. there are two 3rd July's one showing 1.50 and one showing 1.51 I want each row with those dates to show this summed e.g. 3.01.

    SELECT      JS.ScheduleStartDate AS JobDate, SD.JobNumber, SD.OutletPostcode, SD.JobName + ' ' + SD.CustomersRefNo + ', ' + SD.JobLocation AS JobDescription, 
                        JS.AmPmNotes, SD.SiteRefNo, CAST(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TotalTimeAtTheJob, '0:00:00')) / 60 AS VARCHAR(8)) 
                        + ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TotalTimeAtTheJob, '0:00:00')) % 60 AS VARCHAR(2)), 2) AS TimeAtTheJob, 
                        CAST(SUM(DATEDIFF(MINUTE, '0:00:00', ISNULL(T.TravelTime, '0:00:00'))) / 60 AS VARCHAR(8)) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(MINUTE, '0:00:00', 
                        ISNULL(T.TravelTime, '0:00:00'))) % 60 AS VARCHAR(2)), 2) AS TravelTime, ISNULL(SUM(T.OtherFares), 0) AS Fares, ISNULL(T.Parking, 0) AS Parking
    		FROM        dbo.JobSchedule AS JS INNER JOIN
                        dbo.JobDetail AS JD ON JS.JobDetailID = JD.JobDetailID INNER JOIN
                        dbo.SiteDetail AS SD ON JD.SiteDetailID = SD.SiteDetailID LEFT OUTER JOIN
                        dbo.JobTiming AS T ON JD.JobDetailID = T.JobDetailID
    		WHERE       (JS.UserId = @UserID) AND (JS.ScheduleStartDate BETWEEN @FromDate AND @ToDate) AND (SD.SiteRefNo <> - 4)
    		GROUP BY	T.TravelTime, JS.ScheduleStartDate, JD.JobNumber, SD.JobNumber, SD.OutletPostcode, JD.JobLocation, SD.CustomersRefNo, SD.JobLocation, SD.JobName, 
                        JS.AmPmNotes, SD.SiteRefNo, T.TotalTimeAtTheJob, T.Parking

    Much thanks for any help.

    Monday, August 14, 2017 2:46 PM

All replies

  • User77042963 posted

    Try to convert  JS.ScheduleStartDate from datetime to date in both select and group by clause:

     Cast(JS.ScheduleStartDate as date)
    Monday, August 14, 2017 4:00 PM
  • User1568384800 posted

    Unfortunately this makes no difference.

    Tuesday, August 15, 2017 7:42 AM
  • User77042963 posted

    Without seeing your table DDL and sample data, it is not easy to find your issue. If you can mock up a sample to represent your issue, it may help you to get an answer.

    Tuesday, August 15, 2017 1:21 PM
  • User347430248 posted

    Hi purplepint,

    you had asked,"Sum Fares where the job date equals job date".

    refer example below.



    SELECT date,sum(fare) AS Total
    Group by date
    Order by date asc




    Thursday, September 21, 2017 7:39 AM