
Write a SQL Statement
- or -
Post a project like this1211
€10(approx. $10)
- Posted:
- Proposals: 4
- Remote
- #3402818
- Awarded
Description
Experience Level: Entry
I need somebody who write me a small SQL for a MS SQL View.
I have currently a table for a timecontrol tracking software.
CREATE TABLE [dbo].[workTimeRaw](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[day] [date] NULL,
[user] [nchar](100) NULL,
[pc] [nchar](100) NULL,
[transponder] [nchar](100) NULL,
[datetimeOfRecord] [datetime] NOT NULL,
[state] [smallint] NOT NULL
)
id = auto increment integer
day = the day of month for the record (maybe not neccessary because we can extract the day from the datetimeOfRecord field.)
user = the user ;)
pc and transponder you can ignore it. Its only for informations
datetime = the time of the record.
"state" means the type of the record:
1 - start work
2 - end work
I need a view who joins the datetime to two seperated colums ("start" and "end") based on day and user.
Example scheme:
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[day] [date] NULL,
[user] [nchar](100) NULL,
[pc] [nchar](100) NULL,
[transponder] [nchar](100) NULL,
[start] [datetime] NOT NULL,
[end] [datetime] NOT NULL,
)
Example Data:
id,tag,user,pc,transponder,datetime,state
92, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,1
93, 2021-09-22, user1,pc1,transponder1, 2021-09-22 16:54:54.260,2
94, 2021-09-22, user1,pc1,transponder1, 2021-09-23 07:45:49.957,1
95, 2021-09-22, user1,pc1,transponder1, 2021-09-23 10:42:15.673,2
Now I need:
id, tag, user, pc, transponder, start, end
1, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,2021-09-22 16:54:54.260
2, 2021-09-22, user1,pc1,transponder1, 2021-09-23 07:45:49.957,2021-09-23 10:42:15.673
(id is only for ordering, maybe we can order by date ? so we do not need it)
But it must be failsave.. vor example if a user forgot to start or stop the timerecord it must be "visible".
Example
id,tag,user,pc,transponder,datetime,state
92, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,1
93, 2021-09-22, user1,pc1,transponder1, 2021-09-22 16:54:54.260,2
94, 2021-09-22, user1,pc1,transponder1, 2021-09-23 10:42:15.673,2
He put two times end and forgot to start at 2021-09-23
So I need the following result:
id, tag, user, pc, transponder, start, end
1, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,2021-09-22 16:54:54.260
2, 2021-09-22, user1,pc1,transponder1, NULL, 2021-09-23 10:42:15.673
Also vice versa (start and end).
I have currently a table for a timecontrol tracking software.
CREATE TABLE [dbo].[workTimeRaw](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[day] [date] NULL,
[user] [nchar](100) NULL,
[pc] [nchar](100) NULL,
[transponder] [nchar](100) NULL,
[datetimeOfRecord] [datetime] NOT NULL,
[state] [smallint] NOT NULL
)
id = auto increment integer
day = the day of month for the record (maybe not neccessary because we can extract the day from the datetimeOfRecord field.)
user = the user ;)
pc and transponder you can ignore it. Its only for informations
datetime = the time of the record.
"state" means the type of the record:
1 - start work
2 - end work
I need a view who joins the datetime to two seperated colums ("start" and "end") based on day and user.
Example scheme:
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[day] [date] NULL,
[user] [nchar](100) NULL,
[pc] [nchar](100) NULL,
[transponder] [nchar](100) NULL,
[start] [datetime] NOT NULL,
[end] [datetime] NOT NULL,
)
Example Data:
id,tag,user,pc,transponder,datetime,state
92, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,1
93, 2021-09-22, user1,pc1,transponder1, 2021-09-22 16:54:54.260,2
94, 2021-09-22, user1,pc1,transponder1, 2021-09-23 07:45:49.957,1
95, 2021-09-22, user1,pc1,transponder1, 2021-09-23 10:42:15.673,2
Now I need:
id, tag, user, pc, transponder, start, end
1, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,2021-09-22 16:54:54.260
2, 2021-09-22, user1,pc1,transponder1, 2021-09-23 07:45:49.957,2021-09-23 10:42:15.673
(id is only for ordering, maybe we can order by date ? so we do not need it)
But it must be failsave.. vor example if a user forgot to start or stop the timerecord it must be "visible".
Example
id,tag,user,pc,transponder,datetime,state
92, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,1
93, 2021-09-22, user1,pc1,transponder1, 2021-09-22 16:54:54.260,2
94, 2021-09-22, user1,pc1,transponder1, 2021-09-23 10:42:15.673,2
He put two times end and forgot to start at 2021-09-23
So I need the following result:
id, tag, user, pc, transponder, start, end
1, 2021-09-22, user1,pc1,transponder1, 2021-09-22 13:51:46.860,2021-09-22 16:54:54.260
2, 2021-09-22, user1,pc1,transponder1, NULL, 2021-09-23 10:42:15.673
Also vice versa (start and end).

Michael M.
100% (3)Projects Completed
3
Freelancers worked with
2
Projects awarded
20%
Last project
3 Feb 2022
Germany
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies