SQL Puzzle | The Gap Puzzle – V (3 Solutions :))

Tags

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


SQL Puzzle | The Gap Puzzle – V

The requirement is you have to write a SQL statements to achieve the desired result. Here we have to use T-SQL to find the sequencing of groups for each Name. The field LEVEL is unique but we may have gaps in the number list.

E.g.

1. The name “A” first sequence group is for LEVEL 1,2,3 – its sequence 2 is for LEVEL 11,15 and its sequence 3 is for LEVEL 22,23.

2. The “B” name sequence group 1 contains LEVEL 4,10 and its sequence 2 is for LEVEL 16.

3. For “C” we have only one sequence group, for LEVEL 20,21

For details please check out the sample input and the expected output below-

Sample Inputs

Name LEVEL
A 1
A 2
A 3
B 4
B 10
A 11
A 15
B 16
C 20
C 21
A 22
A 23

Expected Output

Name StartPosition EndPosition
A 1 3
A 11 15
B 4 10
A 22 23
B 16 16
C 20 21

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • CTEs are allowed.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--


CREATE TABLE TheGapPuzzle
(
   Name VARCHAR(1)
  ,LEVEL integer
)
GO

INSERT INTO TheGapPuzzle VALUES
('A',  1),
('A',  2),
('A',  3),
('B',  4),
('B',  10),
('A',  11),
('A',  15),
('B',  16),
('C',  20),
('C',  21),
('A',  22),
('A',  23)
GO

SELECT * FROM TheGapPuzzle
GO

--

Solution 1



/*
** Solution 1
*/

SELECT
Name
, MIN(LEVEL) StartPosition
, MAX(LEVEL) EndPosition
FROM
(
SELECT
Name
, LEVEL
, ROW_NUMBER() OVER(ORDER BY LEVEL) - ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1)) rnk
FROM THEGapPuzzle
)r
GROUP BY Name,rnk

--

Solution 2


--

/*
** Solution 2
*/

SELECT Name, StartPosition, EndPosition FROM
(
SELECT Name,LeadValue, LagValue,level StartPosition, CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN LEAD(level) OVER (ORDER BY ( select 1)) ELSE level END EndPosition
FROM
(

SELECT
name
,CASE (LAG(name) OVER (ORDER BY ( select 1 ))) WHEN name THEN 1 ELSE 0 END LagValue
,CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN 1 ELSE 0 END LeadValue
,level
FROM TheGapPuzzle

) tbl2
WHERE tbl2.LagValue = 0 OR tbl2.LeadValue = 0
) tbl3
WHERE LagValue=0


--

Solution 3


--

/*
** Solution 3
*/

SELECT
Name
,MIN(Level) StartPosition
,MAX(Level) EndPosition
FROM
(
SELECT Name,LEVEL , SUM(gap) OVER (ORDER BY LEVEL) grp From
(
SELECT
Name
,LEVEL
,CASE WHEN Name = Lag(Name,1,0) OVER (ORDER BY (SELECT 1)) THEN 0 ELSE 1 END gap
FROM TheGapPuzzle
)
DT
)t
GROUP BY Name,grp

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Pivot Puzzle – V [ 3 Solutions :) ]

Tags

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


SQL Puzzle | The Pivot Puzzle – V

The requirement is you have to write a SQL statements to achieve the desired result.

For details please check out the sample input and the expected output below-

Sample Inputs

Id SoldAt Amount
1 2014-01-05 15.00
2 2015-01-05 1.50
3 2015-05-05 2.00
4 2015-05-06 4.00
5 2015-08-07 8.00
6 2015-09-25 16.00
7 2016-02-05 12.00
8 2016-04-05 6.00
9 2016-04-08 18.00
10 2016-05-07 56.00
11 2016-05-07 16.00
12 2016-07-21 1232.00

Expected Output

Year Jan Feb Mar Q01 Apr May Jun Q02 Jul Aug Sep Q03 Oct Nov Dec Q04 YTD
2014 15.00 NULL NULL 15.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15.00
2015 1.50 NULL NULL 1.50 NULL 6.00 NULL 6.00 NULL 8.00 16.00 24.00 NULL NULL NULL NULL 31.50
2016 NULL 12.00 NULL 12.00 24.00 72.00 NULL 96.00 1232.00 NULL NULL 1232.00 NULL NULL NULL NULL 1340.00

Rules/Restrictions

  • The challenge is to do it with SQL statements.
  • CTEs, Temp Tables are not allowed.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE Sale 
(
	  Id INT
	, SaleOn DATE
	, Amount MONEY
)
GO

Insert Into Sale Values 
  (1, '20140105', $15)
, (2, '20150105', $1.5)
, (3, '20150505', $2)
, (4, '20150506', $4)
, (5, '20150807', $8)
, (6, '20150925', $16)
, (7, '20160205', $12)
, (8, '20160405', $6)
, (9, '20160408', $18)
, (10, '20160507', $56)
, (11, '20160507', $16)
, (12, '20160721', $1232);

GO

--

Solution 1


--

/*
** SOLUTION
*/

SELECT * FROM
(
       SELECT [Year], Amount, Period FROM
       (
             SELECT Year(SoldAt) [Year]
             ,Amount
             ,CAST(FORMAT(SoldAt,'MMM') AS VARCHAR(3)) M            
             ,'Q'+RIGHT('0'+CONVERT(VARCHAR(2), DATEPART(q,SoldAt)), 2) Q
             ,'YTD' Y 
           FROM Sale
       ) t1
       UNPIVOT (PERIOD FOR PeriodType In (M, Q, Y)) u
) t2
PIVOT (Sum(Amount) FOR PERIOD IN (Jan, Feb, Mar, Q01, Apr, May, Jun, Q02, Jul, Aug, Sep, Q03, Oct, Nov, Dec, Q04, YTD))p


--

Solution 2


--

/*
** SOLUTION
*/


select  [year] 
,sum(case [Month] when 'January' Then amount else null end) January
,sum(case [Month] when 'February' Then amount else null end) February
,sum(case [Month] when 'March' Then amount else null end) March
,sum(case when [Month] = 'March' OR [Month] = 'February' OR [Month] = 'January' Then amount else null end) Q01
,sum(case [Month] when 'April' Then amount else null end) April
,sum(case [Month] when 'May' Then amount else null end) May
,sum(case [Month] when 'June' Then amount else null end) June
,sum(case when [Month] = 'April' OR [Month] = 'May' OR [Month] = 'June' Then amount else null end) Q02
,sum(case [Month] when 'July' Then amount else null end) July
,sum(case [Month] when 'August' Then amount else null end) August
,sum(case [Month] when 'September' Then amount else null end) September
,sum(case when [Month] = 'July' OR [Month] = 'August' OR [Month] = 'September' Then amount else null end) Q03
,sum(case [Month] when 'October' Then amount else null end) October
,sum(case [Month] when 'November' Then amount else null end) November
,sum(case [Month] when 'December' Then amount else null end) December
,sum(case when [Month] = 'October' OR [Month] = 'November' OR [Month] = 'December' Then amount else null end) Q04
,sum(amount) YTD
from (
select year(soldat) [Year], DATENAME ( month , soldat )  [Month], convert(float,sum(amount)) Amount from sale group by year(soldat)
, DATENAME ( month , soldat )
) A group by [year]


--

Solution 3


--

/*
** SOLUTION
*/


SELECT 
        A.[YEAR] [YEAR],
       CAST(JAN AS FLOAT) Jan, 
        CAST(FEB AS FLOAT) Feb, 
        CAST(MAR AS FLOAT) Mar,
       CAST(Q1 AS FLOAT)  Q01, 
        CAST(APR AS FLOAT) Apr, 
        CAST(MAY AS FLOAT) May, 
        CAST(JUN AS FLOAT) Jun,
       CAST(Q2 AS FLOAT)  Q02, 
        CAST(JUL AS FLOAT) Jul, 
        CAST(AUG AS FLOAT) Aug, 
        CAST(SEP AS FLOAT) Sep,
       CAST(Q3 AS FLOAT)  Q03, 
        CAST(OCT AS FLOAT) Oct, 
        CAST(NOV AS FLOAT) Nov, 
        CAST([DEC] AS FLOAT) [Dec], 
        CAST(Q4 AS FLOAT)  Q04, 
        CAST(C.YTD AS FLOAT) YTD
FROM
(
       SELECT 
              [YEAR] [YEAR], JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]
       FROM
       (
              SELECT 
                     YEAR(SOLDAT) AS [YEAR], LEFT(DATENAME(MONTH,SOLDAT),3) AS [MONTH], AMOUNT 
              FROM 
                     SALE

       ) 
       AS SOURCETABLE
       PIVOT
       (
              SUM(AMOUNT) FOR [MONTH] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC])
       )
       AS PIVOTTABLE
) A

INNER JOIN 
(
       SELECT 
              [YEAR] [Year], [1] Q1,[2] Q2,[3] Q3,[4] Q4
       FROM
       (
              SELECT 
                     YEAR(SOLDAT) AS YEAR, DATEPART(QUARTER, SOLDAT) AS QUARTERNAME, SUM(AMOUNT) AS AMOUNT
              FROM 
                     SALE
              GROUP BY 
                     DATEPART(QUARTER, SOLDAT), YEAR(SOLDAT)      
       ) 
       AS 
              SOURCETABLE
       PIVOT
       (
              SUM(AMOUNT) FOR QUARTERNAME IN ([1],[2],[3],[4])
       ) 
       AS 
              PIVOTTABLE

) B ON A.YEAR = B.YEAR
INNER JOIN
(
       SELECT 
              SUM(AMOUNT) AS YTD,YEAR(SOLDAT) AS [YEAR]
       FROM 
              SALE
       GROUP BY 
              YEAR(SOLDAT)
) C ON B.YEAR = C.YEAR



--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Order By Puzzle (Solution Time :))

Tags

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


SQL Puzzle | The Order By Puzzle

The requirement is you have to write a single SQL statement to achieve the desired result.

For details please check out the sample input and the expected output below-

Sample Inputs

val
0
-10
NULL
NULL
NULL
1
5
-7

Expected Output

val
-10
-7
0
1
5
NULL
NULL
NULL

Rules/Restrictions

  • The challenge is to do it in a single SQL statement.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE Orders (ID INT)
GO

INSERT INTO Orders (ID) VALUES
(0),
(-10),
(NULL),
(NULL),
(NULL),
(1),
(5),
(-7)
GO

--

Solution 1


--

SELECT Id FROM Orders ORDER BY id * -1 DESC


--

Solution 2


--

Select * from Orders order by Case when ID is null then 1 else 0 end, ID


--

Solution 3


--

select * from orders order by isnull(ID,999999999) asc


--

Solution 4


--Not a single select but I like the logic, thats why I put it here..

SELECT A.ID FROM ORDERS A, (SELECT MAX(ID) AS ID FROM ORDERS) B 
ORDER BY ISNULL(A.ID,B.ID)



--

Solution 5


--

select * from Orders order by  Case When ID is Null Then  ASCII('Z') Else ID End


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Update one table and Insert in another table using single T-SQL statement at same time (Multiple Solutions :))

Tags

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


SQL Puzzle | Update one table & Insert in another table using single T-SQL statement at same time

The requirement is whenever we update Val column from Updates table we need to insert an entry in the INSERTS table. The challenge is to do it in a single SQL statement.

For details please check out the sample input and the expected output below-

Sample Inputs

UPDATES table

ID VAL
1 First
2 Second

Inserts table will be empty in the beginning.

Expected Output

E.g. We have updated val column for Id = 1 from ‘First’ to ‘Four’. After the operation the expected data in respective tables will be-

UPDATES table

ID VAL
1 Four
2 Second

INSERTS Table

UpdatedAt NewValue
8/17/2016 11:49 Four

Rules/Restrictions

  • The challenge is to do it in a single SQL statement.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE Updates
(
       ID TINYINT
       ,VAL VARCHAR(10)
)
GO

CREATE TABLE INSERTS
(
       UpdatedAt DATETIME
       ,NewValue  VARCHAR(10)     
)
GO


INSERT INTO Updates VALUES
(1,'First'),
(2,'Second')
GO

--

Solution 1


--

/*
** SOLUTION 1 
*/

INSERT INTO INSERTS
SELECT * FROM 
(
  UPDATE a
  SET a.Val = 'Four'  
  OUTPUT GETDATE(),Inserted.Val FROM UPDATES a WHERE ID = 1
)t(a,b)


--

Solution 2


--

/*
** SOLUTION 2
*/

UPDATE  Updates 
SET VAL='Four'
OUTPUT GETDATE(), Inserted.Val INTO INSERTS 
WHERE ID = 1


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Get File Size using T-SQL

Tags

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


SQL Puzzle | Get File Size using T-SQL

This is a really cool puzzle. Here you to accept a file location as a parameter and provide the size of the file.

For details please check out the sample input and the expected output below-

Sample Inputs

DECLARE @FileLocation AS VARCHAR(120) = ‘C\a.CSV’

Expected Output

SizeInBytes
XX.00

Note – XX will be size of the your csv file

Rules/Restrictions

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

Script | use below script to create table & insert some sample data


--

DECLARE @FileLocation VARCHAR(120) = 'E:\pawan.csv'

--

Solution 1


--

DECLARE @FileLocation VARCHAR(120) = 'E:\a.csv'

SET @FileLocation = 'dir "' + @FileLocation +'"'

IF OBJECT_ID('tempdb..#tempFilePaths') IS NOT NULL
DROP TABLE #tempFilePaths

CREATE TABLE #tempFilePaths (Files VARCHAR(500))
 
INSERT INTO #tempFilePaths
EXEC XP_CMDSHELL @FileLocation

SELECT CAST(LTRIM(RTRIM(SUBSTRING(Files,CHARINDEX('File(s)',(Files)) + LEN('File(s)'),  CHARINDEX('bytes',(Files))-1-(CHARINDEX('File(s)',(Files)) 
	  + LEN('File(s)'))))) AS DECIMAL(13,2)) SizeInBytes
FROM #tempFilePaths
WHERE Files LIKE '%File%Bytes%'

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Follow

Get every new post delivered to your Inbox.

Join 500 other followers