CODEWITHSUNDEEP
sql-server-2008countinner-joinwindow-functions
LoneXcoder
LoneXcoder

Reputation: 2163

sql inner Join with Count() and windowing functions

;WITH n AS  
( 
  SELECT problemID, StationName, problemCode, ProblemCreateDate, probCount, 
    c = COUNT(*) OVER (PARTITION BY StationName, problemCode), 
    rn = ROW_NUMBER() OVER  
    ( 
      PARTITION BY StationName, problemCode ORDER BY ProblemCreateDate DESC, problemID DESC 
    ) 
  FROM dbo.tblProblems
) 
 SELECT problemID, StationName, problemCode, ProblemCreateDate, c 
 FROM n WHERE rn = 1;

there is another table named tblCustomers , with a column isAssistent Type(bit)

I tried doing inner join but it's too complicated for me and I get an error when trying to apply the filter of inner join with 

tblCustomers where tblCustomers.isAssistent =1

I will be very grateful and happy to know how to write the correct syntax

ReEdit

inner join tblCustomers on tblProblems.CustID = tblCustomers.custID

errors , with one of my last tries

Msg 4104, Level 16, State 1, Line 14 The multi-part identifier "tblProblems.CustID" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "tblproblems.problemID" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "tblproblems.custID" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "tblproblems.StationName" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "tblproblems.problemCode" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "tblproblems.ProblemCreateDate" could not be bound.

this is the wild guess i made :

;WITH n AS  
( 
  SELECT tblCustomers.*,tblproblems.problemID, tblproblems.StationName, tblproblems.problemCode, tblproblems.ProblemCreateDate, tblproblems.probCount, 
    c = COUNT(*) OVER (PARTITION BY tblproblems.StationName, tblproblems.problemCode), 
    rn = ROW_NUMBER() OVER  
    ( 
      PARTITION BY tblproblems.StationName, tblproblems.problemCode ORDER BY tblproblems.ProblemCreateDate DESC, tblproblems.problemID DESC 
    ) 
  FROM dbo.tblProblems
  inner join tblCustomers on tblProblems.CustID = tblCustomers.custID
) 
SELECT tblCustomers.*, tblproblems.problemID, tblproblems.custID, tblproblems.StationName, tblproblems.problemCode, tblproblems.ProblemCreateDate, c 
FROM n 
inner join tblCustomers on tblProblems.CustID = tblCustomers.custID
WHERE rn = 1;

the purpose was to have select result of tblProblems only when tblCustomers.isAssistent=1

Upvotes: 0

Views: 1172

Answers (1)

rs.
rs.

Reputation: 27467

Your query outside with is wrong, it should be

SELECT tblCustomers.*, n.problemID, n.custID, 
           n.StationName, n.problemCode, n.ProblemCreateDate, c 
FROM n 
inner join tblCustomers on n.CustID = tblCustomers.custID
WHERE rn = 1;

And why are you joining tblcustomers again?

You can just do:

;WITH n AS  

( 
  SELECT tblCustomers.*,tblproblems.problemID, tblproblems.StationName, 
         tblproblems.problemCode, tblproblems.ProblemCreateDate,
         tblproblems.probCount, 
    c = COUNT(*) OVER 
        (PARTITION BY tblproblems.StationName, tblproblems.problemCode), 
    rn = ROW_NUMBER() OVER  
    ( 
      PARTITION BY tblproblems.StationName, tblproblems.problemCode
      ORDER BY tblproblems.ProblemCreateDate DESC, tblproblems.problemID DESC 
    ) 
  FROM dbo.tblProblems
  inner join tblCustomers on tblProblems.CustID = tblCustomers.custID
  WHERE tblCustomers.isAssistent =1

) 
SELECT n.* FROM n where rn = 1

If you need only problems data

;WITH n AS  

( 
  SELECT tblproblems.problemID, tblproblems.StationName, 
         tblproblems.problemCode, tblproblems.ProblemCreateDate,
         tblproblems.probCount, 
    c = COUNT(*) OVER 
        (PARTITION BY tblproblems.StationName, tblproblems.problemCode), 
    rn = ROW_NUMBER() OVER  
    ( 
      PARTITION BY tblproblems.StationName, tblproblems.problemCode
      ORDER BY tblproblems.ProblemCreateDate DESC, tblproblems.problemID DESC 
    ) 
  FROM dbo.tblProblems
  inner join tblCustomers on tblProblems.CustID = tblCustomers.custID
  WHERE tblCustomers.isAssistent =1

) 
SELECT n.* FROM n where rn = 1

Upvotes: 1

Related Questions