Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Inserted and Updated Count Puzzle

In this puzzle you have to find out Names of people with how many records they have inserted and how many records they have updated. For more details please check the sample input and expected output.

Sample Input

RId InsertedBy UpdatedBy
1 Pawan Sharlee
2 Diwan Naga
3 Kishan Pawan
4 Chiku Kishan
5 Avtaar Sharlee
6 Ramesh Pawan
7 Kapil Vaibhav
8 Ishu Avtaar
9 Avtaar Sharlee

Expected Output

Nam InsertedCount UpdatedCount
Avtaar 2 1
Chiku 1 0
Diwan 1 0
Ishu 1 0
Kapil 1 0
Kishan 1 1
Naga 0 1
Pawan 1 2
Ramesh 1 0
Sharlee 0 3
Vaibhav 0 1

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE LogData
(
	 RId INT
	,InsertedBy VARCHAR(30)
	,UpdatedBy VARCHAR(30)
)
GO

INSERT INTO LogData VALUES 
(1,'Pawan','Sharlee'),
(2,'Diwan','Naga'),
(3,'Kishan','Pawan'),
(4,'Chiku','Kishan'),
(5,'Avtaar','Sharlee'),
(6,'Ramesh','Pawan'),
(7,'Kapil','Vaibhav'),
(8,'Ishu','Avtaar'),
(9,'Avtaar','Sharlee')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT Nam , InsertedCount , UpdatedCount
FROM 
(
	SELECT InsertedBy Nam FROM LogData UNION SELECT UpdatedBy FROM LogData
)l
OUTER APPLY 
(	
	SELECT COUNT(*) InsertedCount 
	FROM LogData WHERE LogData.InsertedBy = l.Nam
)t
OUTER APPLY 
(	
	SELECT COUNT(*) UpdatedCount 
	FROM LogData WHERE LogData.UpdatedBy = l.Nam
)t1


--

Output-1

--                            

Nam                            InsertedCount UpdatedCount
------------------------------ ------------- ------------
Avtaar                         2             1
Chiku                          1             0
Diwan                          1             0
Ishu                           1             0
Kapil                          1             0
Kishan                         1             1
Naga                           0             1
Pawan                          1             2
Ramesh                         1             0
Sharlee                        0             3
Vaibhav                        0             1

(11 rows affected)

                                   
--

Solution – 2

--


Select ISNULL(InsertedBy,UpdatedBy) Nam , ISNULL(i,0) Insertedcount , ISNULL(u,0) updatedcount from 
(Select Insertedby ,Count(Insertedby) i   FROM 
logdata Group by InsertedBy) a
FULL JOIN
(Select Updatedby ,Count(updatedby) u  FROM 
logdata Group by updatedby)b on a.InsertedBy = b.UpdatedBy


--

Output-2

--                            

Nam                            Insertedcount updatedcount
------------------------------ ------------- ------------
Avtaar                         2             1
Chiku                          1             0
Diwan                          1             0
Ishu                           1             0
Kapil                          1             0
Kishan                         1             1
Naga                           0             1
Pawan                          1             2
Ramesh                         1             0
Sharlee                        0             3
Vaibhav                        0             1

(11 rows affected)

                                   
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MsbiSkills.com