Tags

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


SQL Puzzle | Get Count and Numbers Puzzle

In this puzzle you have to find total count and their numbers based on the Id values. Please check out the sample input values and sample expected output below.

Sample Input

Id Dates
123 2017-01-11 00:00:00.000
123 2017-01-10 00:00:00.000
123 2017-01-08 00:00:00.000
122 2017-01-08 00:00:00.000

Expected Output

Id Dates Count#
122 2017-01-08 00:00:00.000 1 / 1
123 2017-01-08 00:00:00.000 1 / 3
123 2017-01-10 00:00:00.000 2 / 3
123 2017-01-11 00:00:00.000 3 / 3

Script

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

--

CREATE TABLE Total
(
	 Id INT
	,Dates DATETIME
)
GO

INSERT INTO Total VALUES
(123 ,'1/11/2017'),   
(123 ,'1/10/2017'),   
(123 ,'1/8/2017'),
(122 ,'1/8/2017')
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 *,CONCAT( ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Dates)  , ' / ', COUNT(*) OVER (PARTITION BY Id)) [Count#]
FROM Total 

--

Solution 2


--

SELECT *,CAST( ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Dates) AS VARCHAR(5)) + ' / ' +
		 CAST( COUNT(*) OVER (PARTITION BY Id) AS VARCHAR(5)) [Count#]
FROM Total 

--

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

Advertisements