Need Simple Query in Sql server

Question

my Criteria:

I have a distributor (Parent Table), who has many retailer (Child Table). I need last joined retailer name.

i.e

Distributor List  |  Total No. Retailer    |     Last Joined Retailer Name

my Query was :

select distName,  
       count(retailerName) as TotalRetailer,  
       max(retailerName) as lastPosted,  
       max(lastjoinRetail) as lastJoindate 
from distributor d
  right outer join retailer r on d.distNo = r.retailNo
                             and r.status = 0 
                             and d.status = 0
group by distName.....

I am not getting last joined "Retailer Name"?.

score 0 · Answer 1 · answered Aug 24 '16 at 08:02

select [Distributor_Name]
    , [Total_No._Retailer]
    , [Last date]
    , (select name from tbl_Retailer where [Last date] = CreatedDate) AS [Last Joined Retailer Name]
from
(
    select
        d.Name AS 'Distributor_Name'
        ,count(R.name) AS 'Total_No._Retailer'
        ,max(R.CreatedDate) as 'Last date'
    from tbl_Distributor AS D
    inner join tbl_Retailer AS R on D.id = R.DistributorId
    Group by D.Name
) as T
order by [Distributor_Name]

possible mismatch column names

thanks for replying my query, return's Error where sub-query returned mor e than one query?.... — Suttipasanga, Aug 25 '16 at 11:11

score 0 · Answer 2 · answered Aug 24 '16 at 08:07

Based on your question, I created some sample tables and was able to get the requested output. See if this helps.

DECLARE @distributor TABLE 
  ( 
     id INT,NAME VARCHAR(100) 
  ) 

INSERT INTO @distributor 
VALUES      (1,'D1'), 
            (2,'D2') 

DECLARE @retailer TABLE 
  ( 
     id INT,NAME VARCHAR(100),distid INT,joindate DATE 
  ) 

INSERT INTO @retailer 
VALUES      (1,'R1',1,'08/01/2016'), 
            (2,'R2',1,'08/02/2016'), 
            (3,'R3',1,'08/03/2016'), 
            (4,'R4',2,'08/01/2016') 

SELECT DISTINCT a.NAME,First_value(b.NAME) 
                         OVER( 
                           partition BY a.id 
                           ORDER BY b.joindate DESC ) last_retail_name, 
                First_value(b.joindate) 
                                OVER( 
                                  partition BY a.id 
                                  ORDER BY b.joindate DESC ) last_retail_date 
FROM   @distributor a 
       INNER JOIN @retailer b 
               ON a.id = b.distid

Thanks for my Query replies, here i have a Problem, I am getting the display only for distributor who has retailer. if the distributor does'nt have retailer those records are not displayed??..... — Suttipasanga, Aug 24 '16 at 18:09
Change inner join into left join to get even the distributors who do not have retailer — Muthukumar, Aug 24 '16 at 19:32

Need Simple Query in Sql server

2 Answers2