Daily Archives: March 23, 2015

T-SQL Query | [ The Company Code Puzzle ]

23 Monday Mar 2015

Posted by in SQL Concepts, SQL SERVER, T SQL Puzzles

≈ 5 Comments

Tags

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


T-SQL Query | [ The Company Code Puzzle ]  – In this puzzle we have to change values of A, B and CompanyCode to NULL if repeating values are there. Please check out the sample input and expected output for details.

Sample Input

A B C CompanyCode GL
1 1 1 AA 1
1 1 2 AA 2
1 1 3 AA 3
1 1 4 AA 4
2 2 1 BB 1
2 2 2 BB 2

Expected Output

A B C CompanyCode GL
1 1 1 AA 1
NULL NULL 2 NULL 2
NULL NULL 3 NULL 3
NULL NULL 4 NULL 4
2 2 1 BB 1
NULL NULL 2 NULL 2

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

Script

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


--Create table

CREATE TABLE [dbo].[TheCompanyCode]
(
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[CompanyCode] [varchar](100) NULL,
[GL] [varchar](100) NULL
)
GO

--Insert Data

INSERT INTO TheCompanyCode(A,B,C,CompanyCode,GL)
VALUES
(1,    1,     1,     'AA',  001),
(1,    1,     2,     'AA',  002),
(1,    1,     3,     'AA',  003),
(1,    1,     4,     'AA',  004),
(2,    2,     1,     'BB',  001),
(2,    2,     2,     'BB',  002)

--Verify Data

SELECT * FROM TheCompanyCode

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



;WITH CTE AS
(
       SELECT A,B,C,CompanyCode,GL,
       ROW_NUMBER() OVER (PARTITION BY A,B,CompanyCode ORDER BY %%Physloc%%) rrnk
       FROM TheCompanyCode
)
SELECT 
        CASE WHEN rrnk = 1 THEN A ELSE NULL END A
       ,CASE WHEN rrnk = 1 THEN B ELSE NULL END B
       ,C
       ,CASE WHEN rrnk = 1 THEN CompanyCode ELSE NULL END CompanyCode
       ,GL
FROM CTE






--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Fill Missing Value Puzzle ]

23 Monday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 14 Comments

Tags

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


T-SQL Query | [ Fill Missing Value Puzzle ] – In this puzzle we have to fill the missing values in the sample input table . Please check out the sample input and expected output for details.

Sample Input

Value ayValue
A 1
23
21
22
B 34
31
89
C 222
10

Expected Output

Value ayValue
A 1
A 23
A 21
A 22
B 34
B 31
B 89
C 222
C 10

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

Script

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


--Create Table

CREATE TABLE [dbo].[MissingValue]
(
[Value] [varchar](1) NULL,
[ayValue] [int] NULL
)
GO

--Insert Data

INSERT INTO [dbo].[MissingValue](Value,ayValue)
VALUES
('A',  1),
('',   23),
('',   21),
('',   22),
('B',  34),
('',   31),
('',   89),
('C',  222),
('', 10)

--Verify Data

SELECT Value,ayValue FROM [MissingValue]

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



;WITH CTE2 AS 
(
       SELECT p.Value,p.ayValue
	   ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rrnk FROM [dbo].[MissingValue] p 
) 
SELECT
       c.Value,c.ayValue,c.rrnk,
       (
          SELECT d.Value from CTE2 d WHERE d.rrnk = (Select max(e.rrnk) from CTE2 e 
		  Where e.rrnk <= c.rrnk and (e.value != '' ))
       ) NewValue
FROM CTE2 c



--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Two Decimal Puzzle ] – (4 Solutions)

23 Monday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The Two Decimal Puzzle ] – In this puzzle we have to round the values up to decimal places and if values after decimal is zero then don’t show zero values. E.g for 100.119 rounded value will be 100.12 and for 100.001 the rounded value should be 100 . Please check out the sample input and expected output for details

Sample Input

Val
100.119
1.119
111.119
81.115
90.114
100.110
100.000
100.001
100.019

Expected Output

Val
100.12
1.12
111.12
81.12
90.11
100.11
100
100
100.02

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

Script

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


--Create Table


CREATE TABLE [dbo].[TwoDecimal]
(
[Val] Money
)
GO


--Insert Data

INSERT INTO
[TwoDecimal]
VALUES
(100.119),
(1.119),
(111.119),
(81.115),
(90.114),
(100.110),
(100.000),
(100.001),
(100.019)

--Verify Data

SELECT * FROM [TwoDecimal]

Solution 1 


--

/*
** Solution 1
*/
SELECT [Val], CAST(CAST(Val AS DECIMAL(6,2)) AS FLOAT) [FormattedValue] FROM TwoDecimal 


--

Solution 2 


--

/*
** Solution 2
*/
SELECT [Val], CAST(FORMAT(Val , 'N2') AS FLOAT) [FormattedValue] FROM TwoDecimal 


--

Solution 3 


--

/*
** Solution 3
*/
SELECT [Val], CAST(ROUND(val,2) AS FLOAT) [FormattedValue] FROM TwoDecimal 


--

Solution 4 


--

/*
** Solution 4 
*/
SELECT [Val], CONVERT(DOUBLE PRECISION, ROUND(val,2)) [FormattedValue] FROM TwoDecimal


--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Delete Duplicate Value Puzzle ]

23 Monday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

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


T-SQL Query | [ Delete Duplicate Value Puzzle ] – In this puzzle we have to delete the duplicate values from the input table. Please check out the sample input and expected output for details.

Sample Input

ID
1
2
1

Expected Output

ID
1
2

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

Script

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


--CREATE TABLE
CREATE TABLE DeleteDup
(
ID INT
)
Go

--Insert Data
INSERT INTO DeleteDup VALUES(1),(2),(1)

--Verify Data
SELECT ID FROM DeleteDup

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



WITH CTE AS
(
       SELECT *, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY ID ) RNK FROM DeleteDup    
)
DELETE FROM CTE WHERE RNK > 1




--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Multiple Comma Separated Values Puzzle]

23 Monday Mar 2015

Posted by in T SQL Puzzles

≈ 1 Comment

Tags

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


T-SQL Query | [ Multiple Comma Separated Values Puzzle]  – In this puzzle we have to remove the comma separated value with the single value. Please check out the sample input and expected output for details.

Sample Input

ID Name
1 a,b,c,d,e
2 a,b
3 c,d
4 e
5 f

Expected Output

ID Value
1 A
1 B
1 C
1 D
1 E
2 A
2 B
3 C
3 D
4 E
5 F

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

Script

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


--Create Table
CREATE TABLE testMultipleValues
(
ID int
,Name varchar(100)
)
GO

--Insert Data
INSERT INTO testMultipleValues(ID,Name)
VALUES
(1,'a,b,c,d,e'),
(2,'a,b'),
(3,'c,d'),
(4,'e'),
(5,'f')

--Verify Data
SELECT ID, Name FROM testMultipleValues

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT ID,t.value('.', 'VARCHAR(10)') Value FROM 
(	
	SELECT ID, CAST('<A>'+ REPLACE(Name,',','</A><A>')+ '</A>' AS XML) AS x FROM testMultipleValues
) O
CROSS APPLY x.nodes('/A')x(t)

--

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

Keep Learning

http://MSBISkills.com