Compare 3 tables and calculate working days

Question

Compare 3 tables and calculate working days

GA_SQL

Hall of Fame

Points: 3166
More actions
May 23, 2016 at 9:23 pm

#316728

http://forums.asp.net/p/2095821/6053759.aspx?p=True&t=635996316531744463

Viewing 15 posts - 1 through 15 (of 22 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

you are more likely to get a respone if you post your question here, along with scripts that create tables, insert sample data and the results you expect from the sample data

this is a good idea of how to go about it,

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GA_SQL Hall of Fame Points: 3166 More actions · Answer 2

Code is not clear here..mostly the output sample is not in clear format

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

ganapathy.arvindan (5/24/2016)
Code is not clear here..mostly the output sample is not in clear format

when you post you will see at the lefthand side there is list of "IFCode Shortcuts" such as code=sql - /code (in square brackets) ..... if you look carefully these are in pairs

to use these to post TSQL code for example , highlight the relevant text that is TSQL and click on the code=sql - /code "pair"...you will see the code brackets surrounding your highlighted text.

Alternatively...click the code pair...they will automatically appear in your post, and then paste between them

Have a play to see how each pair effects the formatting by previewing your post (Click preview button at bottom of page)....you dont have to post it to see results

ps...always use the "url pair" for links to other webpages / urls

To get your results as below, I now use

http://www.tablesgenerator.com/text_tables with unicode option...I cut and paste from SSMS/Word/Excel/notepad etc and posted below as "code plain" pair

alternatively create a temp table in SQL and populate with your results and post the TSQL

as an example

+---------------------------------------------------------------+

¦ FromDate ¦ ToDate ¦ ¦ ¦ ¦

¦------------+------------+---------+---------+-----------------¦

¦ 4/29/2016 ¦ 4/29/2016 ¦ 67.8518 ¦ 87.7578 ¦ 77.3171 ¦

¦------------+------------+---------+---------+-----------------¦

¦ Etc ¦ etc ¦ ¦ ¦ ¦

¦------------+------------+---------+---------+-----------------¦

¦ 4/3/2016 ¦ 4/3/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 4/2/2016 ¦ 4/2/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 4/1/2016 ¦ 4/1/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 3/31/2016 ¦ 3/31/2016 ¦ 67.8518 ¦ 87.8349 ¦ 77.2492 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 3/30/2016 ¦ 3/30/2016 ¦ 67.7186 ¦ 88.3236 ¦ 76.6710 ¦

¦------------+------------+---------+---------+-----------------¦

¦ etc ¦ Etc ¦ ¦ ¦ ¦

¦------------+------------+---------+---------+-----------------¦

¦ 3/2/2016 ¦ 3/2/2016 ¦ 67.7186 ¦ 92.1489 ¦ 73.4882 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 3/1/2016 ¦ 3/1/2016 ¦ 67.7186 ¦ 91.540 ¦ 73.9771 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 2/29/2016 ¦ 2/29/2016 ¦ 67.7186 ¦ 91.8442 ¦ 73.7320 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 2/28/2016 ¦ 2/28/2016 ¦ 65.0915 ¦ 90.7194 ¦ 71.7504 ¦

¦------------+------------+---------+---------+-----------------¦

¦ etc ¦ etc ¦ ¦ ¦ ¦

¦------------+------------+---------+---------+-----------------¦

¦ 2/1/2016 ¦ 2/1/2016 ¦ 65.0915 ¦ 92.0217 ¦ 70.7349 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/31/2016 ¦ 1/31/2016 ¦ 65.0915 ¦ 91.5751 ¦ 71.0799 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/30/2016 ¦ 1/30/2016 ¦ 66.1551 ¦ 91.6254 ¦ 72.2017 ¦

¦------------+------------+---------+---------+-----------------¦

¦ etc ¦ Etc ¦ ¦ ¦ ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/6/2016 ¦ 1/6/2016 ¦ 66.1551 ¦ 92.9714 ¦ 71.1564 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/5/2016 ¦ 1/5/2016 ¦ 66.1551 ¦ 92.9714 ¦ 71.1564 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/4/2016 ¦ 1/4/2016 ¦ 66.1551 ¦ 91.6003 ¦ 72.2215 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/3/2016 ¦ 1/3/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/2/2016 ¦ 1/2/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 1/1/2016 ¦ 1/1/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦

¦------------+------------+---------+---------+-----------------¦

¦ 12/31/2015 ¦ 12/31/2015 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦

+---------------------------------------------------------------+

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GA_SQL Hall of Fame Points: 3166 More actions · Answer 4

0 Points

4 Posts

Compare 3 tables and calculate working days

May 24, 2016 03:20 AM|LINK

Dayshift

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20 D21 D22 D23 D24 D25 D26 D27 D28 D29 D30 D31

HL W W W W W W WO W W W W W WO WO HL W W W W WO WO W W W W W W WO W W

Nighshift

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20 D21 D22 D23 D24 D25 D26 D27 D28 D29 D30 D31

WO W W W W W WO WO W W W W W WO WO W W W W W WO WO W W W W W WO WO W W

NightShiftIH

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20 D21 D22 D23 D24 D25 D26 D27 D28 D29 D30 D31

HL W W W W W WO WO W W W W W WO WO HL W W W W WO WO W W W W W WO WO W W

W- Working Day WO- Week Off HL-Holiday

D1- 01/01/2015 to D31 31/01/2015

Another table Table_shift

consider an employee is in the following shift types

D1 to D7 in Dayshift D9 to D13 NightShiftIH D16 to D20 Day shift d21 to D31 nighshift

Now i want to compare Table_shift table with other tables

if he is dayshift compare and calculate working days

if he is nightshiftIH compare and calculate working days

if he is nightshift compare and calculate working days

Some Statements

USE [DB1]

GO

/****** Object: Table [dbo].[dayshift] Script Date: 05/24/2016 08:52:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[dayshift](

[D1] [varchar](3) NULL,

[D2] [varchar](3) NULL,

[D3] [varchar](3) NULL,

[D4] [varchar](3) NULL,

[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,

[D7] [varchar](3) NULL,

[D8] [varchar](3) NULL,

[D9] [varchar](3) NULL,

[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,

[D12] [varchar](3) NULL,

[D13] [varchar](3) NULL,

[D14] [varchar](3) NULL,

[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,

[D17] [varchar](3) NULL,

[D18] [varchar](3) NULL,

[D19] [varchar](3) NULL,

[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,

[D22] [varchar](3) NULL,

[D23] [varchar](3) NULL,

[D24] [varchar](3) NULL,

[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,

[D27] [varchar](3) NULL,

[D28] [varchar](3) NULL,

[D29] [varchar](3) NULL,

[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [DB1]

GO

/****** Object: Table [dbo].[NightShift] Script Date: 05/24/2016 08:53:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[NightShift](

[D1] [varchar](3) NULL,

[D2] [varchar](3) NULL,

[D3] [varchar](3) NULL,

[D4] [varchar](3) NULL,

[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,

[D7] [varchar](3) NULL,

[D8] [varchar](3) NULL,

[D9] [varchar](3) NULL,

[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,

[D12] [varchar](3) NULL,

[D13] [varchar](3) NULL,

[D14] [varchar](3) NULL,

[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,

[D17] [varchar](3) NULL,

[D18] [varchar](3) NULL,

[D19] [varchar](3) NULL,

[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,

[D22] [varchar](3) NULL,

[D23] [varchar](3) NULL,

[D24] [varchar](3) NULL,

[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,

[D27] [varchar](3) NULL,

[D28] [varchar](3) NULL,

[D29] [varchar](3) NULL,

[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [DB1]

GO

/****** Object: Table [dbo].[NightShiftIH] Script Date: 05/24/2016 08:53:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[NightShiftIH](

[D1] [varchar](3) NULL,

[D2] [varchar](3) NULL,

[D3] [varchar](3) NULL,

[D4] [varchar](3) NULL,

[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,

[D7] [varchar](3) NULL,

[D8] [varchar](3) NULL,

[D9] [varchar](3) NULL,

[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,

[D12] [varchar](3) NULL,

[D13] [varchar](3) NULL,

[D14] [varchar](3) NULL,

[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,

[D17] [varchar](3) NULL,

[D18] [varchar](3) NULL,

[D19] [varchar](3) NULL,

[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,

[D22] [varchar](3) NULL,

[D23] [varchar](3) NULL,

[D24] [varchar](3) NULL,

[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,

[D27] [varchar](3) NULL,

[D28] [varchar](3) NULL,

[D29] [varchar](3) NULL,

[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

ganapathy.arvindan (5/29/2016)
Another table Table_shift
consider an employee is in the following shift types
D1 to D7 in Dayshift D9 to D13 NightShiftIH D16 to D20 Day shift d21 to D31 nighshift
Now i want to compare Table_shift table with other tables
if he is dayshift compare and calculate working days
if he is nightshiftIH compare and calculate working days
if he is nightshift compare and calculate working days

I think you may be missing giving us details of "Table_shift" data....??

I cant see anything in your table scripts that identifies an "employee"?

can you please post your expected results.....

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GA_SQL Hall of Fame Points: 3166 More actions · Answer 6

Expected results are consider

employee is in dayshift from d1 to d7 refer to dayshift table

d1 - Holiday(HL) d2 to d7- working days

d8 - Weekoff

employee is in nightshiftIH from d9 to d15 refer to NightshiftIH Table

d9 to d13 working days

d14,d15 Week off

employee is in dayshift from d16 to d20 refer to dayshift table

d16 is an holiday

d17 to d20 working days

employee is in nightshiftIH from d21 to d31 refer to NightshiftIH Table

d21,d22 weekoff

d23to d27 working days

d28,d29 week off

d30,d31 working days

Total working days :21

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

I am sorry, but I cannot help you.

for what its worth here is some code to set up the data in SSMS....based on on your previous posts

USE [tempdb]

GO

IF OBJECT_ID('tempdb..dayshift') IS NOT NULL DROP TABLE dayshift;

IF OBJECT_ID('tempdb..nightshift') IS NOT NULL DROP TABLE nightshift;

IF OBJECT_ID('tempdb..nightshiftIH') IS NOT NULL DROP TABLE nightshiftIH;

CREATE TABLE [dbo].[dayshift](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

CREATE TABLE [dbo].[NightShift](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

CREATE TABLE [dbo].[NightShiftIH](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

INSERT [dbo].[dayshift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W')

GO

INSERT [dbo].[NightShift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')

GO

INSERT [dbo].[NightShiftIH] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')

GO

SELECT * FROM dayshift

SELECT * FROM nightshift

SELECT * FROM NightShiftIH

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GA_SQL Hall of Fame Points: 3166 More actions · Answer 8

GA_SQL

Hall of Fame

Points: 3166

May 29, 2016 at 11:26 am

#1881948

Employee tableformat

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

ganapathy.arvindan (5/29/2016)
Employee tableformat

ok....care to explain what you mean by "Employee tableformat"

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GA_SQL Hall of Fame Points: 3166 More actions · Answer 10

trying to add a image on employee table format but not able to

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

ganapathy.arvindan (5/29/2016)
trying to add a image on employee table format but not able to

please read this

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/%5B/url%5D%5B/b%5D

"images" dont help us replicate your data

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

ganapathy.arvindan (5/29/2016)
trying to add a image on employee table format but not able to

Correct. That's because you're trying to reference an image from your C:Drive and this web site can't see that.

It wouldn't do any good anyway. We need the CREATE TABLE statement and we need some readily consumable data for the employee table.

Also, please see the first link under "Helpful Links" in my signature line below. JLS was kind enough to build test data from your previous posts. It would be nice if you would help us help you.

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GA_SQL Hall of Fame Points: 3166 More actions · Answer 13

CREATE TABLE EMPLOYEE(empid1 INT,[D1] VARCHAR(25),[d2] VARCHAR(25),d3 VARCHAR(25),d4 VARCHAR(25),d5 VARCHAR(25),d6 VARCHAR(25),d7 VARCHAR(25),d8 VARCHAR(25),d9 VARCHAR(25),d10 VARCHAR(25),d11 VARCHAR(25),d12 VARCHAR(25),d13 VARCHAR(25),d14 VARCHAR(25),d15 VARCHAR(25),d16 VARCHAR(25),d17 VARCHAR(25),d18 VARCHAR(25),d19 VARCHAR(25),d20 VARCHAR(25),d21 VARCHAR(25),d22 VARCHAR(25),d23 VARCHAR(25),d24 VARCHAR(25),d25 VARCHAR(25),d26 VARCHAR(25),d27 VARCHAR(25),d28 VARCHAR(25),d29 VARCHAR(25),d30 VARCHAR(25),d31 VARCHAR(25))

INSERT INTO EMPLOYEE VALUES(290,'DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','DAYSHIFT','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH','NIGHTSHIFT-IH')

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

I am wondering ....based on some of your previous threads.....are these "tables" not real tables but pivoted views of your raw data?

In my opinion, to get the results that you require with the data you have presented is going to be very messy and probably needs a rethink.

if you are adamant that this is the way you wish to proceed, then my first thought would be to unpivot these tables.

anyways....here is all the set up data, put into one statement, that delivers all your sample data... I hope you take note and please in future provide as such....makes it so much easier for others to help you...

USE [tempdb]

GO

IF OBJECT_ID('tempdb..dayshift') IS NOT NULL DROP TABLE dayshift;

IF OBJECT_ID('tempdb..nightshift') IS NOT NULL DROP TABLE nightshift;

IF OBJECT_ID('tempdb..nightshiftIH') IS NOT NULL DROP TABLE nightshiftIH;

IF OBJECT_ID('tempdb..employee') IS NOT NULL DROP TABLE employee;

CREATE TABLE [dbo].[dayshift](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

CREATE TABLE [dbo].[NightShift](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

CREATE TABLE [dbo].[NightShiftIH](

[D1] [varchar](3) NULL,[D2] [varchar](3) NULL,[D3] [varchar](3) NULL,[D4] [varchar](3) NULL,[D5] [varchar](3) NULL,

[D6] [varchar](3) NULL,[D7] [varchar](3) NULL,[D8] [varchar](3) NULL,[D9] [varchar](3) NULL,[D10] [varchar](3) NULL,

[D11] [varchar](3) NULL,[D12] [varchar](3) NULL,[D13] [varchar](3) NULL,[D14] [varchar](3) NULL,[D15] [varchar](3) NULL,

[D16] [varchar](3) NULL,[D17] [varchar](3) NULL,[D18] [varchar](3) NULL,[D19] [varchar](3) NULL,[D20] [varchar](3) NULL,

[D21] [varchar](3) NULL,[D22] [varchar](3) NULL,[D23] [varchar](3) NULL,[D24] [varchar](3) NULL,[D25] [varchar](3) NULL,

[D26] [varchar](3) NULL,[D27] [varchar](3) NULL,[D28] [varchar](3) NULL,[D29] [varchar](3) NULL,[D30] [varchar](3) NULL,

[D31] [varchar](3) NULL

)

INSERT [dbo].[dayshift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'W', N'WO', N'W', N'W')

GO

INSERT [dbo].[NightShift] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')

GO

INSERT [dbo].[NightShiftIH] ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23], [D24], [D25], [D26], [D27], [D28], [D29], [D30], [D31])

VALUES (N'HL', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'HL', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W', N'W', N'W', N'W', N'WO', N'WO', N'W', N'W')

GO

CREATE TABLE EMPLOYEE

(empid1 INT,

d1 VARCHAR(25), d2 VARCHAR(25), d3 VARCHAR(25), d4 VARCHAR(25),

d5 VARCHAR(25), d6 VARCHAR(25), d7 VARCHAR(25), d8 VARCHAR(25),

d9 VARCHAR(25), d10 VARCHAR(25), d11 VARCHAR(25), d12 VARCHAR(25),

d13 VARCHAR(25), d14 VARCHAR(25), d15 VARCHAR(25), d16 VARCHAR(25),

d17 VARCHAR(25), d18 VARCHAR(25), d19 VARCHAR(25), d20 VARCHAR(25),

d21 VARCHAR(25), d22 VARCHAR(25), d23 VARCHAR(25), d24 VARCHAR(25),

d25 VARCHAR(25), d26 VARCHAR(25), d27 VARCHAR(25), d28 VARCHAR(25),

d29 VARCHAR(25), d30 VARCHAR(25), d31 VARCHAR(25)

);

INSERT INTO EMPLOYEE

VALUES

(290, 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT',

'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH',

'NIGHTSHIFT-IH', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'DAYSHIFT', 'NIGHTSHIFT-IH',

'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH',

'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH', 'NIGHTSHIFT-IH'

);

SELECT * FROM dayshift

SELECT * FROM nightshift

SELECT * FROM NightShiftIH

SELECT * FROM employee

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day