0

I have a SQL Server database and I am trying to pull specific data. I need a count of all the non-null columns in each row, a subtraction of one column from another, and data from other table columns (joins).

This is where I am, could someone please look at the code and tell me what I am doing wrong (ignore the hard-coded dates, they are there solely for testing)?

SELECT
  ((CASE WHEN TC.Time0 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time1 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time2 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time3 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time4 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time5 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time6 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time7 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time8 IS NOT NULL THEN 1 ELSE 0 END)
  + (CASE WHEN TC.Time9 IS NOT NULL THEN 1 ELSE 0 END)) AS [Time Punches]
  ,SUM(CASE WHEN TC.Odometer0 IS NOT NULL THEN 1 ELSE 0 END) AS MileageStart
  ,SUM(CASE WHEN TC.Odometer1 IS NOT NULL THEN 1 ELSE 0 END) AS MileageEnd
  ,SUM(CASE WHEN MileageEnd >= 0 THEN 1 ELSE 0 END) - 
        SUM(CASE WHEN MileageStart < 0 THEN 1 ELSE 0 END) AS [Total Miles]
  ,D.DriverID AS [Driver ID]
  ,W.FirstName +' '+W.LastName AS [Driver Name]
  ,TC.PunchDate AS [DATE]
FROM tblTimeClock TC WITH (NOLOCK)
INNER JOIN tblDrivers D WITH (NOLOCK)
ON D.DriverID = TC.PunchID
INNER JOIN tblWorker W WITH (NOLOCK)
ON W.WorkerID = D.DriverID
WHERE TC.PunchID IS NOT NULL
AND TC.PunchDate BETWEEN '2017-05-01' AND '2017-06-01'
ORDER BY TC.PunchDate

With the above I am getting this error:

> Column 'tblTimeClock.Time0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But I do not know how to include this in a GROUP BY clause - every time I try it causes other errors to pop up (different, depending on where I place the clause).

The reason I asked if someone can "tell me what I am doing wrong" is so that once I fix what is broken, I don't have to come back and say "help" again and again for each subsequent issue. I know the code is bad, this is why I need help.

Suzanne Thompson
  • 1
  • 1
  • You've not explained a problem or asked a question. (*Can you tell me what is wrong?* is not an answerable question.) Describe the problem you're having with the SQL you posted. – Ken White Jul 11 '17 at 16:47
  • If you want to get a count of non-null columns see https://stackoverflow.com/questions/18193365/count-of-non-null-columns-in-each-row – Sookie Singh Jul 11 '17 at 16:51
  • Updated to explain the problem and clarify the question asked. – Suzanne Thompson Jul 11 '17 at 17:44

1 Answers1

0

I figured it out on my own:

SELECT
    TC.PunchDate AS [Date]
    ,D.DriverID AS [Driver ID]
    ,W.FirstName +' '+W.LastName AS [Driver Name]
    ,((CASE WHEN TC.Time0 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time1 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time2 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time3 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time4 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time5 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time6 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time7 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time8 IS NOT NULL THEN 1 ELSE 0 END)
    + (CASE WHEN TC.Time9 IS NOT NULL THEN 1 ELSE 0 END)) AS [Time Punches]
    ,TC.Odometer0 AS [Starting Mileage]
    ,TC.Odometer1 AS [Ending Mileage]
    ,SUM(CASE WHEN TC.Odometer1 IS NOT NULL AND TC.Odometer1 >= 0 THEN TC.Odometer1 ELSE 0 END) -
    SUM(CASE WHEN TC.Odometer0 IS NOT NULL AND TC.Odometer0 >= 0 THEN TC.Odometer0 ELSE 0 END) AS [Total Miles]
        FROM tblTimeClock TC WITH (NOLOCK)
    INNER JOIN tblDrivers D WITH (NOLOCK)
        ON CAST(D.DriverID AS VARCHAR(50)) = TC.PunchID
    INNER JOIN tblWorker W WITH (NOLOCK)
        ON W.WorkerID = D.DriverID
    WHERE TC.PunchID IS NOT NULL
    AND TC.PunchDate BETWEEN @StartDate AND @EndDate
    GROUP BY TC.Time0, TC.Time1, TC.Time2, TC.Time3, TC.Time4, TC.Time5, TC.Time6, TC.Time7, TC.Time8, TC.Time9, TC.Odometer0,TC.Odometer1, D.DriverID, W.FirstName, W.LastName, TC.PunchDate
    ORDER BY TC.PunchDate
Suzanne Thompson
  • 1
  • 1