April 29, 2016 at 1:24 am
Using While Loop i want to insert the values into single record like this
Empid empname D1 D2 D3...D30
1 asd 2
Loop Executes second time
Empid empname D1 D2 D3...D30
1 asd 2 2
Loop Executes third time
Empid empname D1 D2 D3...D30
1 asd 2 2 1
Like this loop should update single record for the single employee... Thanks in advance
April 29, 2016 at 2:37 am
Please explain why you are doing this.
Also, you first talk about inserts, then later updates as though they are the same thing. They are not.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2016 at 2:48 am
Yes, it does seem a strange requirement. But why would you want to use a loop, anyway?
INSERT INTO MyTable (Empid,empname,D1,D2, D3,...,D30)
VALUES (1,'asd',2,2,1,...,<Val30>)
John
April 29, 2016 at 2:56 am
Consider my count is 30
using while loop i am decrement it..till it reaches zero
count =30
then output should be
Empid empname D1 D2 D3...D30
1 asd 2
count =29
Empid empname D1 D2 D3...D30
1 asd 2 2
Count=28
Empid empname D1 D2 D3...D30
1 asd 2 2 1
My code is like
WHILE @Cnt>0
BEGIN
INSERT INTO #Newtemp1(EmpID,Working_Days,MainTeamID,D1,D2...Help me in this insert statement
SET @Cnt=@Cnt-1;
END;
END
April 29, 2016 at 6:16 am
ganapathy.arvindan (4/29/2016)
Consider my count is 30using while loop i am decrement it..till it reaches zero
count =30
then output should be
Empid empname D1 D2 D3...D30
1 asd 2
count =29
Empid empname D1 D2 D3...D30
1 asd 2 2
Count=28
Empid empname D1 D2 D3...D30
1 asd 2 2 1
My code is like
WHILE @Cnt>0
BEGIN
INSERT INTO #Newtemp1(EmpID,Working_Days,MainTeamID,D1,D2...Help me in this insert statement
SET @Cnt=@Cnt-1;
END;
END
WHILE loops are slow. Most people here will refuse to give you a slow solution when much faster alternatives are available. But unless we can understand what you are trying to do, we cannot do that either.
Tell us what the source is and what the desired end result is. Use code to do this rather than trying to waffle round it in English.
Use the link in my signature to understand how best to present your question so that people can understand it and provide you with working code as a solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2016 at 7:51 am
You say you're going to a single record, but are you actually putting everything into one column? If so, why. That's not what a relational database management system is designed to do. Cramming everything into a single column has tons of bad implications, limitations, performance issues, etc. I'm with everyone else. Why are you doing what you're doing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2016 at 5:55 pm
Yeah, loop is a bad idea.
I think the data represents a month (even though he's limited it to 30 )...my guess is he's trying to pivot the data
E.g.
--assuming data is for a given month
IF OBJECT_ID('tempdb..#WorkDays') IS NOT NULL DROP TABLE #WorkDays
CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date )
INSERT INTO #WorkDays ( EmpId, EmpName, MainTeamId, WorkDate )
VALUES
( 1, 'ASD', 2, '2016-05-01' ),
( 1, 'ASD', 2, '2016-05-01' ),
( 1, 'ASD', 2, '2016-05-02' )
SELECT *
FROM
(
SELECT T.EmpId, T.EmpName, T.MainTeamId, Calc.WorkDateDay
FROM #WorkDays T
CROSS APPLY ( SELECT DATEPART( day, T.WorkDate ) AS WorkDateDay ) AS Calc
) D
PIVOT ( COUNT( WorkDateDay ) FOR WorkDateDay IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31] ) ) PVT
May 2, 2016 at 4:24 am
Hi, Thanks for ur reply
My requirement is like i have two tables one is Shiftscheduler (captures only when Shift and shifttype is 2 and 4 respectively) and another table is employee (captures if shift and shift type is 1 and 2 respectively)...step 1 i have to check the shift type 1 or 2..based on that i have to choose the tables then update the temp table based on the shift
Sample
Input (Choose it from date to to date)
Date Empid Shift Shifttype
1/1/2014 2424 2 4
1/2/2014 2424 2 4
1/3/2014 2424 2 4
1/4/2014 2424 1 2
Output like
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype
2424 2 4 2 4 2 4 1 2
Please help
May 2, 2016 at 4:52 am
You are asking for a PIVOT here. This might help: http://www.sqlservercentral.com/articles/T-SQL/63681/
May 3, 2016 at 2:44 am
Sample
Input (Choose it from date to to date)
Date Empid Shift Shifttype
1/1/2014 1424 2 4
1/2/2014 1424 2 4
1/3/2014 1424 2 4
1/4/2014 1424 1 2
Output like
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype
2424 2 4 2 4 2 4 1 2
It should process on daily basis..please suggest me any loop will do or any better sugesstion
USE [process]
GO
/****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Emp_Shift3]
@FromDate datetime,
@EndDate datetime
AS
BEGIN
CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date )
INSERT INTO #WorkDays ( EmpId, EmpName, MainTeamId, WorkDate )
SELECT [Emp_Code],Shift,ShiftType,CreatedOn FROM ShiftScheduler WHERE EndDate BETWEEN @FromDate and @EndDate;
SELECT *
FROM
(
SELECT
FROM #WorkDays T
CROSS APPLY ( ) AS Calc
) D
PIVOT ( COUNT( WorkDateDay ) FOR WorkDateDay IN ( [d1eff],[d1shifttype] [d2eff],[d2shift],[d2shifttype], [d3Eff],[d3shift],[d3shifttype], [d4eff],[d4shift],
[d4shifttype], [d5Eff],[d5shift],[d5shifttype], [d6Eff],[d6shift],[d6shifttype],[d7],[d7shift],[d7shifttype],[d8Eff],[d8shift],[d8shifttype],
[d9Eff],[d9shift],[d9shifttype], [d10Eff],[d10shift],[d10shifttype],[d11Eff],[d11shift],[d11shifttype], [d12Eff],[d12shift],[d12shifttype],
[d13Eff],[d13shift],[d13shifttype],[d14Eff],[d14shift],[d14shifttype],[d15Eff],[d15shift],[d15shifttype],[d16Eff],[d16shift],[d16shifttype],
[d17Eff],[d17shift],[d17shifttype],[d18Eff],[d18shift],[d18shifttype] ,[d19Eff],[d19shift],[d19shifttype] ,[d20Eff],[d20shift],[d20shifttype],
[d21Eff],[d21shift],[d21shifttype],[d22Eff],[d22shift],[d22shifttype],[d23Eff],[d23shift],[d23shifttype],[d24Eff],[d24shift],[d24shifttype],[d25Eff],
[d25shift],[d25shifttype],[d26Eff],[d26shift],[d26shifttype] ,[d27Eff],[d27shift],[d27shifttype],[d28Eff],[d28shift],[d28shifttype],[d29Eff],[d29shift],
[d29shifttype],[d30Eff],[d30shift],[d30shifttype],[d31Eff],[d31shift],[d31shifttype] )
) PVT
END
Please help me in getting this pivot done
May 3, 2016 at 3:38 am
Something like this...
DECLARE@ShiftScheduler TABLE
(
[Date]DATE,
EmpidINT,
[Shift]TINYINT,
[ShiftType]TINYINT
)
INSERT@ShiftScheduler
SELECT'20140101', 1424, 2, 4 UNION ALL
SELECT'20140102', 1424, 2, 4 UNION ALL
SELECT'20140103', 1424, 2, 4 UNION ALL
SELECT'20140104', 1424, 1, 2 UNION ALL
SELECT'20140101', 1425, 1, 2
SELECTEmpid,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [Shift] ELSE NULL END ) AS D1Shift,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [ShiftType] ELSE NULL END ) AS D1ShiftType,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [Shift] ELSE NULL END ) AS D2Shift,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [ShiftType] ELSE NULL END ) AS D2ShiftType,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [Shift] ELSE NULL END ) AS D3Shift,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [ShiftType] ELSE NULL END ) AS D3ShiftType,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [Shift] ELSE NULL END ) AS D4Shift,
MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [ShiftType] ELSE NULL END ) AS D4ShiftType
FROM@ShiftScheduler
WHERE[Date] BETWEEN '20140101' AND '20140104'
GROUP BY Empid
The link given by Steve explains the method used to solve this common problem
I have given the same below for your reference
http://www.sqlservercentral.com/articles/T-SQL/63681/
You would have received an answer much earlier had you followed the steps mentioned in the link in my signature.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 9, 2016 at 1:09 am
USE [process]
GO
/****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Emp_Shift3]
@FromDate datetime,
@EndDate datetime
AS
BEGIN
CREATE TABLE #WorkDays(empid1 INT,shift1 INT,shifttype1 INT)
INSERT INTO #WorkDays(empid1,shift1,shifttype1)
SELECT SS.Shift,SS.ShiftType,e.empid FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND e.empid=635
--SS.Emp_Code=e.empcode
SELECT *
FROM #WorkDays
PIVOT (MIN(empid1)
FOR [shifttype1] IN ([1],[2],[3],[4],[5],[6])
)
AS PVT
END
Output i am getting is
shift1123456
1NULLNULLNULLNULLNULLNULL
4NULLNULLNULLNULLNULLNULL
Expected is
empid1d1d1 d2 d2 d3 d4
635 Shift shifttype Shift shifttype Shift shifttype
636 Shift shifttype Shift shifttype Shift shifttype
Note Shift and shifttype are values from the temptables #WorkDays either shift and shift type either 1 or 2 and 2 and 4 respectively
Please help
May 9, 2016 at 2:50 am
Find the employee is in which shift and shifttype
If he is shift 1 shifttype 1then the record comes from employee table
if he is shift 2 shifttype 4 then the record comes from shiftscheduler table
For example :
I want to know the details empid : 3321 From date 1/1/2015 to 1/10/2015
1/1/2015 -- shift 1 shifttype 1
1/2/2015 -- shift 1 shifttype 1
1/3/2015 -- shift 1 shifttype 1
1/4/2015 -- shift 1 shifttype 1
Still this time record comes from employee table
now shift changes
1/5/2015 --shift 2 shifttype 4
1/6/2015 --shift 2 shifttype 4
Above 2 records comes from shiftscheduler table
Again shift changes
1/7/2015 -- shift 1 shifttype 1
1/8/2015 -- shift 1 shifttype 1
Above 2 records comes from employee table
Again Shift changes
1/9/2015 --shift 2 shifttype 4
1/10/2015 --shift 2 shifttype 4
Above 2 records comes from shiftscheduler table
My expected output is
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype d5shift d5shifttype d6shift d6shifttype d7shift d7shifttype d8shift d8shifttype d9shift d9shifttype
3321 1 1 1 1 1 1 1 1 2 4 2 4 1 1 1 1 2 4
d1shift d1shifttype comes from 1/1/2015
d2shift d2shifttype comes from 1/2/2015
d3shift d3shifttyp comes from 1/3/2015
d4shift d4shifttype comes from 1/4/2015
d5shift d5shifttype comes from 1/5/2015
May 9, 2016 at 4:18 am
USE [process]
GO
/****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Emp_Shift3]
@FromDate datetime,
@EndDate datetime
AS
BEGIN
----CREATE TABLE #Newtemp(ID INT,Emp_Code VARCHAR(50),Shift INT,ShiftType INT,FromDate datetime,EndDate datetime)
--CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date,shift INT,shifttype int )
----INSERT INTO #Newtemp(ID,Emp_Code,Shift,ShiftType,FromDate,EndDate)
----SELECT [ID],[Emp_Code],Shift,ShiftType,FromDate,EndDate FROM ShiftScheduler WHERE EndDate BETWEEN @FromDate and @EndDate;
--INSERT INTO #WorkDays ( EmpId,shift,shifttype )
--SELECT e.empid as [EMID],SS.Shift,SS.ShiftType FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND SS.Emp_Code=e.empcode;
CREATE TABLE #WorkDays(empid1 INT,shift1 INT,shifttype1 INT)
INSERT INTO #WorkDays(empid1,shift1,shifttype1)
SELECT e.empid,SS.Shift,SS.ShiftType FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND e.empid=635
--SS.Emp_Code=e.empcode
SELECT *
FROM #WorkDays
PIVOT (MIN(Shift1)
FOR [shift1] IN ([1],[2],[3],[4],[5],[6],[7])
) AS PVT0
END
Output is
empid1shifttype11234567
635112NULLNULLNULLNULLNULL
6354NULL2NULLNULLNULLNULLNULL
Not actual
May 9, 2016 at 4:23 am
Try this...
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply