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