how to make this sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi all,

Help plz

I have a table like follows:

EmpID	01-Apr-2011	02-Apr-2011	03-Apr-2011	04-Apr-2011	05-Apr-2011

1	Shift1	Shift2	Shift3	Shift4	Shift5
2	Shift1	Shift2	Shift3	Shift4	Shift5
3	Shift1	Shift2	Shift3	Shift4	Shift5
4	Shift1	Shift2	Shift3	Shift4	Shift5
5	Shift1	Shift2	Shift3	Shift4	Shift5

The dates in above table are actual field names.

I have created another temporary table like follows:

EmpID	Dates	        

1	1-Apr-2011	
1	2-Apr-2011	
1	3-Apr-2011	
1	4-Apr-2011	
1	5-Apr-2011	
2	5-Apr-2011	
2	4-Apr-2011	
2	3-Apr-2011	
2	2-Apr-2011	
2	1-Apr-2011	
3	1-Apr-2011	
3	2-Apr-2011	
3	3-Apr-2011	
3	4-Apr-2011	
3	5-Apr-2011	
4	5-Apr-2011	
4	4-Apr-2011	
4	3-Apr-2011	
4	2-Apr-2011	
4	1-Apr-2011	
5	1-Apr-2011	
5	2-Apr-2011	
5	3-Apr-2011	
5	4-Apr-2011	
5	5-Apr-2011

I am able to generate above records but I also need to generate the shift as well using the first table like follows:

EmpID	Dates	        Shift

1	1-Apr-2011	Shift1
1	2-Apr-2011	Shift2
1	3-Apr-2011	Shift3
1	4-Apr-2011	Shift4
1	5-Apr-2011	Shift5
2	5-Apr-2011	Shift1
2	4-Apr-2011	Shift2
2	3-Apr-2011	Shift3
2	2-Apr-2011	Shift4
2	1-Apr-2011	Shift5
3	1-Apr-2011	Shift1
3	2-Apr-2011	Shift2
3	3-Apr-2011	Shift3
3	4-Apr-2011	Shift4
3	5-Apr-2011	Shift5
4	5-Apr-2011	Shift1
4	4-Apr-2011	Shift2
4	3-Apr-2011	Shift3
4	2-Apr-2011	Shift4
4	1-Apr-2011	Shift5
5	1-Apr-2011	Shift1
5	2-Apr-2011	Shift2
5	3-Apr-2011	Shift3
5	4-Apr-2011	Shift4
5	5-Apr-2011	Shift5

does any body know how can i achieve this.

Regards,
Gopal

Posted 6-May-11 2:03am

gopalgupta

Updated 6-May-11 5:56am

pankajupadhyay29

v2

Add a Solution

3 solutions

Solution 3

Making it more dynamic...there might be few thing we can eliminate....rite now its just a working idea
CREATE TABLE dbo.EmpDate_Shift
(
EMPID INT,
[01-Apr-2011] VARCHAR(50),
[02-Apr-2011] VARCHAR(50),
[03-Apr-2011] VARCHAR(50),
[04-Apr-2011] VARCHAR(50),
[05-Apr-2011] VARCHAR(50),
[06-Apr-2011] VARCHAR(50)
)

INSERT INTO EmpDate_Shift
SELECT 1,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 2,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 3,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 4,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'
UNION ALL SELECT 5,'Shift1','Shift2','Shift3','Shift4','Shift5','Shift6'

CREATE TABLE dbo.EmpDate
(
EMPID INT,
Dates VARCHAR(50)
)

INSERT INTO EmpDate
SELECT 1, '1-Apr-2011'
UNION ALL SELECT 1, '2-Apr-2011'
UNION ALL SELECT 1, '3-Apr-2011'
UNION ALL SELECT 1, '4-Apr-2011'
UNION ALL SELECT 1, '5-Apr-2011'
UNION ALL SELECT 1, '6-Apr-2011'
UNION ALL SELECT 2, '1-Apr-2011'
UNION ALL SELECT 2, '2-Apr-2011'
UNION ALL SELECT 2, '3-Apr-2011'
UNION ALL SELECT 2, '4-Apr-2011'
UNION ALL SELECT 2, '5-Apr-2011'
UNION ALL SELECT 2, '6-Apr-2011'
UNION ALL SELECT 3, '1-Apr-2011'
UNION ALL SELECT 3, '2-Apr-2011'
UNION ALL SELECT 3, '3-Apr-2011'
UNION ALL SELECT 3, '4-Apr-2011'
UNION ALL SELECT 3, '5-Apr-2011'
UNION ALL SELECT 3, '6-Apr-2011'
UNION ALL SELECT 4, '1-Apr-2011'
UNION ALL SELECT 4, '2-Apr-2011'
UNION ALL SELECT 4, '3-Apr-2011'
UNION ALL SELECT 4, '4-Apr-2011'
UNION ALL SELECT 4, '5-Apr-2011'
UNION ALL SELECT 4, '6-Apr-2011'
UNION ALL SELECT 5, '1-Apr-2011'
UNION ALL SELECT 5, '2-Apr-2011'
UNION ALL SELECT 5, '3-Apr-2011'
UNION ALL SELECT 5, '4-Apr-2011'
UNION ALL SELECT 5, '5-Apr-2011'
UNION ALL SELECT 5, '6-Apr-2011'

SELECT Name As Date INTO EmpDateCol
FROM syscolumns WHERE id IN (SELECT object_id FROM sys.tables WHERE name = 'EmpDate_Shift')
AND ISDate(Name) = 1

CREATE TABLE dbo.EmpDateShift
(
EmpId INT,
Dates VARCHAR(25),
Shift VARCHAR(25)
)
--SELECT * FROM EmpDateCol
--SELECT * FROM EmpDate
--SELECT * FROM EmpDate_Shift

DECLARE @Sql VARCHAR(8000), @Val VARCHAR(50)
SET @Sql = ''

DECLARE cur_Date CURSOR FOR SELECT [Date] FROM EmpDateCol
OPEN cur_Date
FETCH NEXT FROM cur_Date INTO @Val

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Sql = @Sql + 'SELECT B.EmpID,'''+ @Val + ''',C.[' + @Val + '] FROM EmpDateCol A INNER JOIN Fankarian..EmpDate B ON CONVERT(Date,A.Date) = CONVERT(Date,B.Dates)
INNER JOIN EmpDate_Shift C ON C.Empid = B.EmpID WHERE CONVERT(Date,B.Dates) = CONVERT(Date,''' + @Val + ''') UNION ALL' + CHAR(13)

--INSERT INTO EmpDateShift
--EXEC (@Sql)

FETCH NEXT FROM cur_Date INTO @Val
END

CLOSE cur_Date
DEALLOCATe cur_Date

SET @Sql = LEFT(@Sql,LEN(@Sql)-10)

INSERT INTO EmpDateShift
EXEC (@Sql)

SELECT * FROM EmpDateShift ORDER BY EmpID, Dates

DROP TABLE EmpDateShift
DROP TABLE EmpDate_Shift
DROP TABLE EmpDate
DROP TABLE EmpDateCol

Posted 10-May-11 7:36am

mhwasim

Solution 2

You can solve this using Unpivot for details see this[^]

this will fast.

Posted 6-May-11 6:06am

pankajupadhyay29

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Umair Feroze · Accepted Answer · 2011-05-06T05:56:00

For your query let me say table 1 of yours "Emp" and temp table as "EmpDet". Then try the following query:

SQL

SELECT B.*,
        CASE
            WHEN B.Dates = '1-Apr-2011' THEN A.[01-Apr-2011]
            WHEN B.Dates = '2-Apr-2011' THEN A.[02-Apr-2011]
            WHEN B.Dates = '3-Apr-2011' THEN A.[03-Apr-2011]
            WHEN B.Dates = '4-Apr-2011' THEN A.[04-Apr-2011]
            WHEN B.Dates = '5-Apr-2011' THEN A.[05-Apr-2011]
        END
FROM Emp A
JOIN EmpDet B ON A.EMPId = B.EMPID

Hope this answer your question

how to make this sql query

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

how to make this sql query

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us