SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error The...
Would an alien lifeform be able to achieve space travel if lacking in vision?
Is an up-to-date browser secure on an out-of-date OS?
What aspect of planet Earth must be changed to prevent the industrial revolution?
Why can I use a list index as an indexing variable in a for loop?
Was credit for the black hole image misappropriated?
What happens to a Warlock's expended Spell Slots when they gain a Level?
Could an empire control the whole planet with today's comunication methods?
Why are PDP-7-style microprogrammed instructions out of vogue?
60's-70's movie: home appliances revolting against the owners
Why not take a picture of a closer black hole?
Why doesn't a hydraulic lever violate conservation of energy?
What information about me do stores get via my credit card?
"is" operation returns false even though two objects have same id
Is this wall load bearing? Blueprints and photos attached
Student Loan from years ago pops up and is taking my salary
Working through the single responsibility principle (SRP) in Python when calls are expensive
How many cones with angle theta can I pack into the unit sphere?
Why can't wing-mounted spoilers be used to steepen approaches?
Simulating Exploding Dice
"... to apply for a visa" or "... and applied for a visa"?
Do working physicists consider Newtonian mechanics to be "falsified"?
Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?
Is there a writing software that you can sort scenes like slides in PowerPoint?
Single author papers against my advisor's will?
SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error
The 2019 Stack Overflow Developer Survey Results Are In
Unicorn Meta Zoo #1: Why another podcast?
Announcing the arrival of Valued Associate #679: Cesar Manara
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.
|---------------------|------------------|------------------------|
| empid | action | log_date |
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T07:00:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T07:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T08:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T11:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T12:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T12:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T08:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T09:15:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T10:00:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T11:45:00.000Z|
|---------------------|------------------|------------------------|
Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.
SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
FROM Activity
WHERE action = 'ON') AS s
JOIN (SELECT empid
FROM Activity
WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
FROM Activity
LIMIT 1
WHERE empid = e.empid
AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);
What seems to be the problem? I want the query result to look something like this:
|---------------------|------------------|------------------------|
| empid | date | work_minutes |
|---------------------|------------------|------------------------|
| a1 | 2019-01-01 | 100 minutes |
|---------------------|------------------|------------------------|
| b2 | 2019-01-01 | 150 minutes |
|---------------------|------------------|------------------------|
(work_minutes values above are random)
Any help will be greatly appreciated.
Thank you!
sql-server mysql postgresql
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.
|---------------------|------------------|------------------------|
| empid | action | log_date |
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T07:00:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T07:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T08:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T11:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T12:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T12:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T08:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T09:15:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T10:00:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T11:45:00.000Z|
|---------------------|------------------|------------------------|
Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.
SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
FROM Activity
WHERE action = 'ON') AS s
JOIN (SELECT empid
FROM Activity
WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
FROM Activity
LIMIT 1
WHERE empid = e.empid
AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);
What seems to be the problem? I want the query result to look something like this:
|---------------------|------------------|------------------------|
| empid | date | work_minutes |
|---------------------|------------------|------------------------|
| a1 | 2019-01-01 | 100 minutes |
|---------------------|------------------|------------------------|
| b2 | 2019-01-01 | 150 minutes |
|---------------------|------------------|------------------------|
(work_minutes values above are random)
Any help will be greatly appreciated.
Thank you!
sql-server mysql postgresql
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago
add a comment |
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.
|---------------------|------------------|------------------------|
| empid | action | log_date |
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T07:00:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T07:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T08:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T11:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T12:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T12:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T08:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T09:15:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T10:00:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T11:45:00.000Z|
|---------------------|------------------|------------------------|
Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.
SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
FROM Activity
WHERE action = 'ON') AS s
JOIN (SELECT empid
FROM Activity
WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
FROM Activity
LIMIT 1
WHERE empid = e.empid
AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);
What seems to be the problem? I want the query result to look something like this:
|---------------------|------------------|------------------------|
| empid | date | work_minutes |
|---------------------|------------------|------------------------|
| a1 | 2019-01-01 | 100 minutes |
|---------------------|------------------|------------------------|
| b2 | 2019-01-01 | 150 minutes |
|---------------------|------------------|------------------------|
(work_minutes values above are random)
Any help will be greatly appreciated.
Thank you!
sql-server mysql postgresql
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.
|---------------------|------------------|------------------------|
| empid | action | log_date |
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T07:00:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T07:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T08:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T11:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | ON |2019-01-01T12:30:00.000Z|
|---------------------|------------------|------------------------|
| a1 | OFF |2019-01-01T12:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T08:45:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T09:15:00.000Z|
|---------------------|------------------|------------------------|
| a2 | ON |2019-01-01T10:00:00.000Z|
|---------------------|------------------|------------------------|
| a2 | OFF |2019-01-01T11:45:00.000Z|
|---------------------|------------------|------------------------|
Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.
SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
FROM Activity
WHERE action = 'ON') AS s
JOIN (SELECT empid
FROM Activity
WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
FROM Activity
LIMIT 1
WHERE empid = e.empid
AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);
What seems to be the problem? I want the query result to look something like this:
|---------------------|------------------|------------------------|
| empid | date | work_minutes |
|---------------------|------------------|------------------------|
| a1 | 2019-01-01 | 100 minutes |
|---------------------|------------------|------------------------|
| b2 | 2019-01-01 | 150 minutes |
|---------------------|------------------|------------------------|
(work_minutes values above are random)
Any help will be greatly appreciated.
Thank you!
sql-server mysql postgresql
sql-server mysql postgresql
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
asked 2 mins ago
kunichi_kimurakunichi_kimura
1
1
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
kunichi_kimura is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago
add a comment |
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
kunichi_kimura is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234704%2fsql-beginner-trying-to-calculate-total-time-in-minutes-need-help-with-where-syn%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
kunichi_kimura is a new contributor. Be nice, and check out our Code of Conduct.
kunichi_kimura is a new contributor. Be nice, and check out our Code of Conduct.
kunichi_kimura is a new contributor. Be nice, and check out our Code of Conduct.
kunichi_kimura is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234704%2fsql-beginner-trying-to-calculate-total-time-in-minutes-need-help-with-where-syn%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You have tagged Sql Server, MySQL and PostgreSQL - which one are you actually using?
– Mr.Brownstone
57 secs ago