Daily Archives: March 22, 2015

T-SQL Query | Find Duplicate Puzzle

22 Sunday Mar 2015

Posted by in T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [SQL | Find Duplicate Puzzle ] – In the puzzle we have to find the duplicate values from a duplicate table where duplicate values are more than 1. Please check the sample input and expected output for details.

Sample Input

ID EmpName EmpDate
1 Pawan 05-01-2014
1 Pawan 05-03-2014
1 Pawan 05-02-2014
4 Manisha 05-07-2014
5 Sharlee 05-09-2014
6 Barry 05-02-2014
7 Jyoti 05-04-2014
7 Jyoti 05-05-2014

Expected Output

EmpName DuplicateCount
Pawan 3
Jyoti 2

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table 
CREATE TABLE TESTDuplicateCount ( ID INT ,EmpName VARCHAR(100) 
,EmpDate DATETIME ) 
GO 

--Insert Data INSERT INTO TESTDuplicateCount(ID,EmpName,EmpDate) 
VALUES 
(1,'Pawan','2014-01-05'),
(2,'Pawan','2014-03-05'), 
(3,'Pawan','2014-02-05'), 
(4,'Manisha','2014-07-05'), 
(5,'Sharlee','2014-09-05'), 
(6,'Barry','2014-02-05'), 
(7,'Jyoti','2014-04-05'), 
(8,'Jyoti','2014-05-05') 

--Check data 
SELECT ID,EmpName,EmpDate FROM TESTDuplicateCount

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT EmpName , COUNT(*) DuplicateCount FROM TESTDuplicateCount
GROUP BY EmpName 
HAVING COUNT(*) > 1
ORDER BY EmpName DESC

--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | Gold Rate Puzzle

22 Sunday Mar 2015

Posted by in T SQL Puzzles

≈ 2 Comments

Tags

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


T-SQL Query | [SQL | Gold Rate Puzzle] – In the puzzle we have gold rate changing all the time. We have to find the start date, End Date and the gold rate at that duration. If the gold rate is changed then only we have create a new row. Please check the sample input and the expected output.

Sample Input

dt Rate
18-09-2014 27000
19-09-2014 27000
20-09-2014 31000
21-09-2014 31000
22-09-2014 31000
23-09-2014 32000
24-09-2014 31000
25-09-2014 32000
26-09-2014 27000

Expected Output

StartDate EndDate Rate
18-09-2014 19-09-2014 27000
20-09-2014 22-09-2014 31000
23-09-2014 23-09-2014 32000
24-09-2014 24-09-2014 31000
25-09-2014 25-09-2014 32000
26-09-2014 26-09-2014 27000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE [dbo].[testGoldRateChange]
(
[dt] [datetime] NULL,
[Rate] [int] NULL
)
GO

--Insert Data
INSERT INTO [dbo].[testGoldRateChange](dt,Rate)
VALUES
('2014-09-18 06:25:19.897', 27000),
('2014-09-19 06:25:19.897', 27000),
('2014-09-20 06:25:19.897', 31000),
('2014-09-21 06:25:19.897', 31000),
('2014-09-22 06:25:19.897', 31000),
('2014-09-23 06:25:19.897', 32000),
('2014-09-24 06:25:19.897', 31000),
('2014-09-25 06:25:19.897', 32000),
('2014-09-26 06:25:19.897', 27000)

--Check data
SELECT dt,Rate FROM [dbo].[testGoldRateChange]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

; WITH CTE1 AS ( SELECT dt , Rate , ROW_NUMBER() OVER (ORDER BY dt) Rnk  FROM testGoldRateChange )
,CTE2 AS 
(
SELECT *,  CASE WHEN
              Rate = ( SELECT Rate from CTE1 c3 WHERE c3.rnk =  ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk ))  
              THEN 0 ELSE 1 END Identifier,
            SUM(CASE WHEN
              Rate = ( SELECT Rate from CTE1 c3 WHERE c3.rnk =  ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk ))  
              THEN 0 ELSE 1 END) OVER (ORDER BY rnk ) cols                   
                       FROM CTE1 c2
)
SELECT MIN(dt) StartDate , MAX(dt) EndDate , MAX(Rate) Rate FROM CTE2 GROUP BY cols


---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------


; WITH CTE1 AS 
( 
       SELECT dt , Rate , ROW_NUMBER() OVER (ORDER BY dt) Rnk  FROM testGoldRateChange 
)
,CTE2 AS 
(
       SELECT *,  CASE WHEN Rate = lag(Rate) over(order by rnk) THEN 0 ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) over(order by rnk) Grouper FROM CTE2 c2
)
SELECT MIN(dt) StartDate , MAX(dt) EndDate , MAX(Rate) Rate FROM CTE3 GROUP BY Grouper




--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [Remove ALL Zero Puzzle]

22 Sunday Mar 2015

Posted by in T SQL Puzzles

≈ 9 Comments

Tags

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


T-SQL Query | [Remove ALL Zero Puzzle] – If all the columns having zero value then don’t show that row. Please check out the sample input and expected output. In this case we have to remove the 5th row while selecting data.

Sample Input

A B C D
0 0 0 1
0 0 1 0
0 1 0 0
1 0 0 0
0 0 0 0
1 1 1 0

Expected Output

A B C D
0 0 0 1
0 0 1 0
0 1 0 0
1 0 0 0
1 1 1 0

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE [dbo].[TestMultipleZero]
(
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[D] [int] NULL
)
GO

--Insert Data
INSERT INTO [dbo].[TestMultipleZero](A,B,C,D)
VALUES (0,0,0,1),(0,0,1,0),(0,1,0,0),(1,0,0,0),(0,0,0,0),(1,1,1,0)

--Check data
SELECT A,B,C,D FROM [dbo].[TestMultipleZero]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM TestMultipleZero
WHERE A != 0 OR B != 0 OR C != 0 OR D != 0


--

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

Keep Learning

http://MSBISkills.com