CODEWITHSUNDEEP
sql-serveraggregate-functions
Robinson
Robinson

Reputation: 10122

SQL aggregate query with bit-field

I have an SQL table that looks like this:

ID          uniqueidentifier
ID_Task     uniqueidentifier
ID_Employee uniqueidentifier
Comment     nvarchar(256)
Complete    int
Date        datetime
Days        decimal(6, 2)
Locked      bit
Created     datetime
Edited      datetime
Deleted     bit

What I want to do is add up all of the "Days", for each Date, for a given ID_Employee. I managed to work out a query that looks like this:

SELECT  CAST([Note].Date AS DATE) As Date,
        SUM([Note].Days) AS Total
        FROM
        Note
        WHERE
        [Note].ID_Employee = N'E6A0E609-F8B2-4B48-A17C-4A4E117A4077'
        GROUP BY        
        CAST(Note.Date AS DATE)

This gives me a result set of the total "Days" for each unique "Date", such as:

2013-06-20  1.00
2013-06-21  0.75

, showing employee E6A0E609-F8B2-4B48-A17C-4A4E117A4077 worked 1 man-day on the 20th and 3/4 of a man-day on the 21st. There can be any number of individual records for any day of the year, as a given employee might have worked different amounts of time on different ID_Tasks.

Now what I want to do is add another field to the result set, that's "false" if any one of the records had "Locked" set to 1, else "true" if they were all 0. In simple language the question I'm wanting to ask the database is, "for all Date in the table, tell me the total Days worked and whether all records for that day have been Locked, for a given Employee". So what I want the result set to show is:

2013-06-20  1.00    TRUE
2013-06-21  0.75    FALSE

Meaning the employee has signed off his work for the 20th (TRUE, all records have 1 in the Locked bit-field), and one or more records not yet signed off for the 21st, meaning one or more records for this employee on the 21st have a 0 in the Locked bit-field).

Did that make sense? Anyway, I can't get my head around the best way of doing this.

Any SQL genius lurking who can assist?

EDIT:

OK, thanks to the ideas I got from the answers below, I added the CASE line to the query. Basically it says, "if the lowest number in Locked for any record on this date is zero, then not all the records have been signed off, else they all have been".

SELECT  CAST([Note].Date AS DATE) As Date,
        SUM([Note].Days) AS Total,
        CASE MIN(CONVERT(INT, Locked)) WHEN 0 THEN 0 ELSE 1 END AS Locked
        FROM
        Note
        WHERE
        [Note].ID_Employee = N'E6A0E609-F8B2-4B48-A17C-4A4E117A4077'
        GROUP BY        
        CAST(Note.Date AS DATE)

Upvotes: 0

Views: 1112

Answers (1)

Vlad G.
Vlad G.

Reputation: 2147

SELECT  CAST([Note].Date AS DATE) As Date,
        SUM([Note].Days) AS Totalб
        CASE WHEN MIN(Locked) = 0 THEN 'false' ELSE 'true' END AS SignedOff
FROM
        Note
WHERE
        [Note].ID_Employee = N'E6A0E609-F8B2-4B48-A17C-4A4E117A4077'
        GROUP BY        
        CAST(Note.Date AS DATE)

Upvotes: 1

Related Questions