September 21, 2018 at 12:16 pm
Hi,
Need help with showing the difference between the two periods when the Employee and Product is same. Currently for e.g. i have shown only 2 periods. I want to show the differences of personal id column. If the data is exactly same between the two period for personal id column then it wont show any row in output. If data is less or more or have different value in PersonalId then it should show. Please help. Thanks.
CREATE TABLE mytable(
Employee VARCHAR(1) NOT NULL
,Period VARCHAR(7) NOT NULL
,personalid VARCHAR(7) NOT NULL
,Product VARCHAR(5) NOT NULL
);
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','C1223','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','D1222','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','E1263','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','Y6736','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','C1223','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','D1222','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','E1263','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','T8457','Type1');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','643T','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','878Y','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','8577U','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-07','T8756','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','643T','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','878Y','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','8577U','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('A','2018-08','T8756','Type2');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-07','643T','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-07','878Y','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-07','8577U','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-07','T8756','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-07','T6667','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-08','643T','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-08','878Y','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-08','8577U','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('B','2018-08','T8756','Type3');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-07','643T12','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-07','878Y33','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-07','8577U76','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-08','643T','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-08','878Y33','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-08','8577U','Type4');
INSERT INTO mytable(Employee,Period,personalid,Product) VALUES ('C','2018-08','T8756','Type4');
September 21, 2018 at 1:23 pm
if there are more than 2 periods, then this will need to be modified to handle that part. I changed tablename to #mytable
drop table if exists #Results
create table #Results (
Employee VARCHAR(1) NOT NULL
,Period VARCHAR(7) NOT NULL
,personalid VARCHAR(7) NOT NULL
,Product VARCHAR(5) NOT NULL)
insert into #Results (Employee, personalid, Product, Period)
select Employee, PersonalID, Product, '2018-07' period
from #myTable
where period = '2018-07'
except
select Employee, PersonalID, Product, '2018-07' period
from #myTable
where period = '2018-08'
insert into #Results (Employee, personalid, Product, Period)
select Employee, PersonalID, Product, '2018-08' period
from #myTable
where period = '2018-08'
except
select Employee, PersonalID, Product, '2018-08' period
from #myTable
where period = '2018-07'
select Employee, Period,
STUFF(
( SELECT ',' + PersonalID
FROM #Results t2
WHERE t1.Employee = t2.Employee
and t1.period = t2.period
and t1.product = t2.product
FOR XML PATH('')
),
1,
1,''
) AS t,
Product
from #Results t1
group by Employee, Period, Product
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2018 at 11:17 am
This was removed by the editor as SPAM
September 24, 2018 at 9:12 am
Let's try this:CREATE TABLE #MyTable (
Employee VARCHAR(1) NOT NULL,
[Period] VARCHAR(7) NOT NULL,
personalid VARCHAR(7) NOT NULL,
Product VARCHAR(5) NOT NULL
);
INSERT INTO #MyTable (Employee, [Period], personalid, Product)
VALUES ('A','2018-07','C1223','Type1'),
('A','2018-07','D1222','Type1'),
('A','2018-07','E1263','Type1'),
('A','2018-07','Y6736','Type1'),
('A','2018-08','C1223','Type1'),
('A','2018-08','D1222','Type1'),
('A','2018-08','E1263','Type1'),
('A','2018-08','T8457','Type1'),
('A','2018-07','643T','Type2'),
('A','2018-07','878Y','Type2'),
('A','2018-07','8577U','Type2'),
('A','2018-07','T8756','Type2'),
('A','2018-08','643T','Type2'),
('A','2018-08','878Y','Type2'),
('A','2018-08','8577U','Type2'),
('A','2018-08','T8756','Type2'),
('B','2018-07','643T','Type3'),
('B','2018-07','878Y','Type3'),
('B','2018-07','8577U','Type3'),
('B','2018-07','T8756','Type3'),
('B','2018-07','T6667','Type3'),
('B','2018-08','643T','Type3'),
('B','2018-08','878Y','Type3'),
('B','2018-08','8577U','Type3'),
('B','2018-08','T8756','Type3'),
('C','2018-07','643T12','Type4'),
('C','2018-07','878Y33','Type4'),
('C','2018-07','8577U76','Type4'),
('C','2018-08','643T','Type4'),
('C','2018-08','878Y33','Type4'),
('C','2018-08','8577U','Type4'),
('C','2018-08','T8756','Type4');
SELECT
MT.Employee,
MT.[Period],
MT.personalid
--COUNT(*) AS Row_Count
FROM #MyTable AS MT
WHERE NOT EXISTS (
SELECT 1
FROM #MyTable AS MT2
WHERE MT2.Employee = MT.Employee
AND MT2.personalid = MT.personalid
AND MT2.[Period] <> MT.[Period]
)
ORDER BY
MT.Employee,
MT.[Period];
DROP TABLE #MyTable;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 24, 2018 at 10:22 am
Thanks.both worked.
September 24, 2018 at 3:56 pm
Papil - Monday, September 24, 2018 10:22 AMThanks.both worked.
You're welcome. FYI, the code I provided will handle any number of periods.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply