locked
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.

    Table:

    Query:

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

    Output:

    Regards

    Deepak

    Thursday, September 21, 2017 7:39 AM