Essentially a database SQL query problem-CodePudding

The structure of the database time sheets A_t:
Id name slightly after the time,,,
1 zhang SAN 08:00:00 2020-04-01
1 zhang SAN 12:00:00 2020-04-01
Zhang SAN 2020-04-01 14:00:00 1
1 zhang SAN 18:00:00 2020-04-01
1 zhang SAN 08:00:00 2020-04-02
,,,
The 2020-04-01 2, dick, and harry 08:02:52
,,,
Through the SQL query and now need to get the following format:
Id name date morning/afternoon judge
1 zhang SAN's the 2020-04-01 on the morning of 4
1 zhang SAN at 4 2020-04-01
2 bill 2020-04-01 morning 0

Note: the first, second row 08:00:0-12:00:00 will be expressed as the morning in table 2, 4, minor errors such as 12:01:00 also judged 4)
The same afternoon 14:00 to 18:00 will be judged to be in the afternoon, 4

I know the date conversion function,
But I don't know how to make 2 rows of data into 1 row data, the method of (to the logical)

CodePudding user response:

Can't read your note, like what you have said is 8:00-12:00 on the morning of 4
World is 4 (should be in the afternoon, 4?)
14:00 to 18:00 is afternoon, 4
Question 1: what circumstances judge is zero, there are other number has not
2: no small error condition is, the system logic (such as 8:00-12:05 you need to set up is in the morning, 4, 5 minutes is the error you say, but must be into conditions)
3:12:00 - then judge what, 18:00-8:00 judge what

CodePudding user response:

The so-called two line become a line:

 DECLARE @ t TABLE (id INT the NOT NULL, [name] NVARCHAR (10) NOT NULL, [time] DATETIME NOT NULL) 
INSERT the @ t (id, [name], [time]) 
VALUES (1, 'zhang', '2020-04-01 08:00:00), (1,' zhang ', '2020-04-01 12:00:00), 
14:00:00 (1, 'zhang', '2020-04-01'), (1, 'zhang', '2020-04-01 18:00:00), (1,' zhang ', '2020-04-02 08:00:00'); 

WITH datas AS (SELECT *, ROW_NUMBER () OVER (PARTITION BY [id] ORDER BY [time]) AS seq FROM @ t) 
The SELECT, Anderson d 
A. [name], 
A. [time] beginTime, 
B.e ndTime 
The FROM datas a 
The OUTER APPLY (SELECT b.t ime AS endTime 
The FROM datas b 
WHERE Anderson, d=b.i d AND a.s eq=b.s eq 1) b 
WHERE a.s eq % 2=1;

At the back of the calculation is simple

CodePudding user response:

reference 1st floor nebuung response:

can't read your note, as you say 8:00-12:00 is on the morning of 4
World is 4 (should be in the afternoon, 4?)
14:00 to 18:00 is afternoon, 4
Question 1: what circumstances judge is zero, there are other number has not
2: no small error condition is, the system logic (such as 8:00-12:05 you need to set up is in the morning, 4, 5 minutes is the error you say, but must be into conditions)
3:12:00 - then what is the judge, 18:00-8:00 judge what

Hello, is in fact a calculation of attendance,
12:00 is an ideal data, are in fact 12:02, 12:05, I can adjust the setting error, this is not the key, mainly is two rows of data change 1 row I won't achieve this logic

CodePudding user response:

reference 1st floor nebuung response:

can't read your note, as you say 8:00-12:00 is on the morning of 4
World is 4 (should be in the afternoon, 4?)
14:00 to 18:00 is afternoon, 4
Question 1: what circumstances judge is zero, there are other number has not
2: no small error condition is, the system logic (such as 8:00-12:05 you need to set up is in the morning, 4, 5 minutes is the error you say, but must be into conditions)
3:12:00 - then what is the judge, 18:00-8:00 judge what

Then on the morning of 0 means morning data for the odd lines is 0, only when the even lines May 4

CodePudding user response:

Hello World reference 2 floor, response:

line two lines become a so-called:

 DECLARE @ t TABLE (id INT the NOT NULL, [name] NVARCHAR (10) NOT NULL, [time] DATETIME NOT NULL) 
INSERT the @ t (id, [name], [time]) 
VALUES (1, 'zhang', '2020-04-01 08:00:00), (1,' zhang ', '2020-04-01 12:00:00), 
14:00:00 (1, 'zhang', '2020-04-01'), (1, 'zhang', '2020-04-01 18:00:00), (1,' zhang ', '2020-04-02 08:00:00'); 

WITH datas AS (SELECT *, ROW_NUMBER () OVER (PARTITION BY [id] ORDER BY [time]) AS seq FROM @ t) 
The SELECT, Anderson d 
A. [name], 
A. [time] beginTime, 
B.e ndTime 
The FROM datas a 
The OUTER APPLY (SELECT b.t ime AS endTime 
The FROM datas b 
WHERE Anderson, d=b.i d AND a.s eq=b.s eq 1) b 
WHERE a.s eq % 2=1;

At the back of the calculation is simple.

Thanks for the answer. I study

CodePudding user response:

Hello World reference 2 floor, response:

line two lines become a so-called:

 DECLARE @ t TABLE (id INT the NOT NULL, [name] NVARCHAR (10) NOT NULL, [time] DATETIME NOT NULL) 
INSERT the @ t (id, [name], [time]) 
VALUES (1, 'zhang', '2020-04-01 08:00:00), (1,' zhang ', '2020-04-01 12:00:00), 
14:00:00 (1, 'zhang', '2020-04-01'), (1, 'zhang', '2020-04-01 18:00:00), (1,' zhang ', '2020-04-02 08:00:00'); 

WITH datas AS (SELECT *, ROW_NUMBER () OVER (PARTITION BY [id] ORDER BY [time]) AS seq FROM @ t) 
The SELECT, Anderson d 
A. [name], 
A. [time] beginTime, 
B.e ndTime 
The FROM datas a 
The OUTER APPLY (SELECT b.t ime AS endTime 
The FROM datas b 
WHERE Anderson, d=b.i d AND a.s eq=b.s eq 1) b 
WHERE a.s eq % 2=1;

At the back of the calculation is simple.

 DECLARE @ t TABLE (id INT the NOT NULL, [name] NVARCHAR (10) NOT NULL, [time] DATETIME NOT NULL) 
INSERT the @ t (id, [name], [time]) 
VALUES (1, 'zhang', '2020-04-01 08:00:00), (1,' zhang ', '2020-04-01 12:00:00), 
14:00:00 (1, 'zhang', '2020-04-01'), (1, 'zhang', '2020-04-01 18:00:00), (1,' zhang ', '2020-04-02 08:00:00), (1,' zhang ', '2020-04-03 08:00:00'); 

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull