Number the DATE TIME
1001 2019-09-01 00:00:00. 000 08:00
1001 2019-09-01 00:00:00. 12:00 000
000 1001 2019-09-01 00:00:00. 13:30
1001 2019-09-01 00:00:00. 000 17:30pm
1001 2019-09-02 00:00:00. 000 08:00
1001 2019-09-02 00:00:00. 12:00 000
000 1001 2019-09-02 00:00:00. 13:30
1001 2019-09-02 00:00:00. 000 17:30pm
000 1002 2019-09-02 00:00:00. 13:30
1002 2019-09-02 00:00:00. 000 17:30pm
.
Now need to query results are as follows;
1001 2019-09-01 00:00:00. 000 08:00, 12:00, 13:30, 17:30pm
1001 2019-09-02 00:00:00. 000 08:00, 12:00, 13:30, 17:30pm
000 1002 2019-09-02 00:00:00. 13:30, 17:30pm
* * * * * note the SQL version 2000, cannot be used FOR XML PATH query (project conditions do not allow cannot use higher database), so don't recommend FOR XML PATH
Now to use the method is:
Establish function:
The CREATE FUNCTION [dbo] [GRQDSumNEW] (@ MYNumber varchar (16), @ MYDate datetime)
RETURNS a varchar (8000)
AS
The BEGIN
DECLARE @ values varchar (8000)
The SET @ values='
SELECT @ values=@ values + ', '+ substring (Time, 0, 6) FROM a_ysdate
WHERE number=@ MYNumber and Date=@ MYDate order by Time
RETURN STUFF (@ values, 1, 1, ' ')
END
-- -- --
In the calling function into a temporary table query
SELECT the Number, the Date, (dbo. GRQDSumNEW (Number, Date)) AS NewTime the FROM a_ysdate
WHERE Date>='2019-09-01' and DATE
But this method is of low efficiency and a_ysdate table data is huge, a month down the amount of data that might be 50-100 w, query a month of data takes a long time,
Do you have what good method can be implemented;
* * * * * note the SQL version 2000, cannot be used FOR querying XML PATH;
CodePudding user response:
Temporary table with variables, if the update, with can't use it with a temporary table
CodePudding user response:
- you should be attendance data, then clock of time there should be a rule
- you can according to the time period to determine the effective joining together again
- this is not perfect, but the query only once your a_ysdate data table, speed should be improve
- the substring and replace the following code is to remove the comma
The CREATE TABLE # T
(
Number varchar (10),
[DATE] DATE,
[TIME] TIME
)
INSERT INTO # T VALUES (' 1001 ', '2019-09-01 00:00:00. 000', '08:00')
INSERT INTO # T VALUES (' 1001 ', '2019-09-01 00:00:00. 000', '12:00)
INSERT INTO # T VALUES (' 1001 ', '2019-09-01 00:00:00. 000', 'and')
INSERT INTO # T VALUES (' 1001 ', '2019-09-01 00:00:00. 000', 'the same')
INSERT INTO # T VALUES (' 1001 ', '2019-09-02 00:00:00. 000', '08:00')
INSERT INTO # T VALUES (' 1001 ', '2019-09-02 00:00:00. 000', '12:00)
INSERT INTO # T VALUES (' 1001 ', '2019-09-02 00:00:00. 000', 'and')
INSERT INTO # T VALUES (' 1001 ', '2019-09-02 00:00:00. 000', 'the same')
INSERT INTO # T VALUES (' 1002 ', '2019-09-02 00:00:00. 000', 'and')
INSERT INTO # T VALUES (' 1002 ', '2019-09-02 00:00:00. 000', 'the same')
A SELECT Number of [Date],
The substring (
The replace (
The replace (
The replace (
', '+ MAX (CASE WHEN [time] & gt; AND [time]='07:00' & lt; '09:00 THEN left (5) [time], ELSE' END) +
', '+ MAX (CASE WHEN [time] & gt;='11' AND [time] <'13:00 THEN left (5) [time], ELSE' END) +
', '+ MAX (CASE WHEN [time] & gt;='13:00 AND [time] <'passion' THEN left (5) [time], ELSE 'END) +
', '+ MAX (CASE WHEN [time] & gt;='ticket' AND [time] <'18:30 THEN left (5) [time], ELSE' END)
, 'and,' and ', ')
, 'and,' and ', ')
, 'and,' and ', ')
, 2100, 0)
NewTime the FROM # T
WHERE [Date] & gt;='2019-09-01' and [DATE]