how to combine 2 tables worker and holiday

Question

how to combine 2 tables worker and holiday

midan1

SSCertifiable

Points: 5100
More actions
December 20, 2007 at 2:22 am

#185172

how to
how to combine 2 tables but like this
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2
table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10
98765432 01/04/2007 19/04/2007 10
------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10
98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month
98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
TNX

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

To solve this, I think you'll need a table of dates. Search this site for Jeff Moden's tally table - this is just a table of sequential numbers, to which you can add a second column to create a date range to suit your requirement.

Please clarify for me - what does the number in the shift column represent?

John

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 2

Try these steps out:

1. Create a temporary table, with the following fields: Employee_No, Date and Shift.

2. Add the Employee_No, StartDate and Shift from the Holiday table, as a record into the temp table.

3. Next, add the Employee_No, EndDate and Shift as a record into the temp table.

4. Then insert the Employee_No and Shift along with the date for all the dates between the start and end dates (in a loop)

5. You'll have to repeat steps 2 - 4 for each record in the Holiday table (another loop here maybe)

6. Next perform a union operation on the first table and temp table, while ordering on the Employee_No and the date

Let me know if it works...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

No loop is necessary, especially a loop within a loop, which would kill performance if you have a lot of data. It should be possible to solve this with a set-based query.

John

midan1 SSCertifiable Points: 5100 More actions · Answer 4

wow complicated

friends

can someone please please

sohw me example how to do it

TNX

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Please answer my question about the shift column and I'll see what I can do.

John

midan1 SSCertifiable Points: 5100 More actions · Answer 6

John Mitchell (12/20/2007)
No loop is necessary, especially a loop within a loop, which would kill performance if you have a lot of data. It should be possible to solve this with a set-based query.
John

hi John

i have 150 employee

evry month

my company have a good server

TNX

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Also see topic here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94363

N 56°04'39.16"
E 12°55'05.25"

midan1 SSCertifiable Points: 5100 More actions · Answer 8

OK OK

it work but to convert it to a view OR function

this is the working code

----------------------------------------

SET NOCOUNT ON;

IF OBJECT_ID('dbo.Nums') IS NOT NULL

DROP TABLE dbo.Nums;

GO

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);

DECLARE @max-2 AS INT, @rc AS INT;

SET @max-2 = 1000;

SET @rc = 1;

INSERT INTO Nums VALUES(1);

WHILE @rc * 2 <= @max-2

BEGIN

INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;

SET @rc = @rc * 2;

END

INSERT INTO dbo.Nums

SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max-2;

;WITH CTE(empid, date, shift) AS

(

SELECT empid, start_date + n - 1, shift FROM holiday CROSS JOIN dbo.Nums WHERE n <= end_date - start_date + 1

UNION ALL

SELECT empid, basedate, shift FROM empbase

)

SELECT * FROM CTE ORDER BY date;