Daily Archives: May 4, 2015

T-SQL Query | [ The Multiple Join Single Query Puzzle ]

04 Monday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The Multiple Join Single Query Puzzle ]

Puzzle Statement

  1. We have a table named Orders1 , dates and parts.
  2. You have join all these tables and return part information , date and count.
  3. Count is how many orders we have for this partid on this idate
  4. For details please check out the sample input and expected output

Orders

Sample Input

Parts

partid
1
2

Dates

idate
01-01-2008
02-01-2008

Orders1 Table

partid idate
1 01-01-2008
1 02-01-2008
2 01-01-2008

Expected Output

partid idate counts
1 01-01-2008 1
1 02-01-2008 1
2 01-01-2008 1
2 02-01-2008 0

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • No temp tables , No Ctes , No sub selects are allowed. Only Single Select required
  • 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 Dates(idate datetime)
insert into dates select '1/1/2008'
insert into dates select '1/2/2008'

create table parts(partid int)
insert into parts select 1
insert into parts select 2

create table orders1(partid int,idate datetime)
insert into orders1 select 1,'1/1/2008'
insert into orders1 select 1,'1/2/2008'
insert into orders1 select 2,'1/1/2008'

—

Update May 14 | Solution


--

/************   SOLUTION 1    | Deepak Sharma     ****************/



SELECT p.partid,d.idate,  COUNT(o.idate) Counts FROM parts p cross join dates d left join orders1 o ON d.idate = o.idate and p.partid = o.partid
GROUP BY p.partid, d.idate ORDER BY 1


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

SELECT p.partid, d.idate,COUNT(o.partid) OVER (PARTITION BY o.partid , o.idate ORDER BY (SELECT NULL)) counts FROM orders1 o
FULL OUTER JOIN Dates d CROSS JOIN parts p ON d.idate = o.idate AND p.partid = o.partid
ORDER BY p.partid




--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The Bitwise AND Puzzle ]

04 Monday May 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The Bitwise AND Puzzle ]

Puzzle Statement

1. We have a table named BitwiseAnd.
2. Here we have Id and Value columns, now iwe have group by column Id and Bitwise AND all the values in that group Id.
3. For Id = 1 there are three values so we have calculate the Bitwise And like 10 & 1 & 13 ; Output for this is 2
4. For details please check out the sample input and expected output

Bitwise

Sample Input

Id Value
1 10
1 3
1 14
3 13
4 1
4 12

Expected Output

Id Bitwise
1 2
3 13
4 0

 

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • 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 BitwiseAnd 
(
	 Id INT
	,Value INT
)
GO

INSERT INTO BitwiseAnd 
VALUES 
(1,10),
(1,3),
(1,14),
(3,13),
(4,1),
(4,12)

—

Update May 14 | Solution


--

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



;WITH CTE AS
(
   SELECT * , ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY %%Physloc%% ) rnk FROM BitwiseAnd
)
,CTE1 AS
(
   SELECT Id , Value final , rnk FROM CTE WHERE rnk = 1
   UNION ALL
   SELECT c2.Id , c2.final & c1.Value final , c2.rnk + 1 FROM CTE1 c2 INNER JOIN CTE c1 ON c1.Id = c2.Id AND c1.rnk = c2.rnk 
)
SELECT x.Id , Final Bitwise
FROM CTE1 x
WHERE rnk = (SELECT MAX(rnk) FROM CTE1 y WHERE x.Id = y.Id )
ORDER BY Id




--

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

Keep Learning

http://MSBISkills.com