1

The code I am using is here

CREATE TABLE timecard (
   id INTEGER PRIMARY KEY autoincrement,
   clock_in REAL not null,
   clock_out REAL,
   lunch integer,
   pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021-02-15 08:00"),julianday("2021-02-15 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021-02-16 07:23"),julianday("2021-02-16 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021-02-17 07:16"),julianday("2021-02-17 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021-02-18 07:54"),julianday("2021-02-18 15:48"),1,"WORK");

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as "IN",
       time(clock_out) as "OUT",
       CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
            when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
       END as "total_time",
       sum("total_time")
FROM timecard
GROUP BY "weekofyear";

This is the output of the command.

weekofyear  IN          OUT         total_time  sum("total_time")
----------  ----------  ----------  ----------  -----------------
07          08:00:00    16:00:00    8.0         0.0

I am trying to get the total hours for the week of year. Not sure as to what I am doing wrong. I am new to the case statement in SQL and SQL in general.

Thanks for your time

1

You can't reference a column alias within the same SELECT clause as it was created in. In other words sum("total_time") is referencing something that doesn't exist yet. You'll either need to copy your previous CASE statement into your SUM() function call like this:

SUM
(
    CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
         when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
    END
)

Or use something like a subquery to generate the total_time column first, then apply the SUM() function on top of that column from your subquery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.