Nested SQL select statement in SQL Server with aggregate function

Question

I have a query where first table Project will be fetched as a list. So every project row should have a unique ProjectID.

Now the second table Contract should get one row for each project. Although there are multiple rows for some projects in the Contract table. But I have to get only one row based on the select count condition I have applied in the below query.

Right now my code is returning two rows and I want only the second row not first row. Logic is basically only Approve 0 & 1 will be picked which cannot be more than one row together. But value 2 is multiple and destroying my result set. But for multiple projects of different ProjectId is should return multiple results.

SELECT  
        a.ProjectId
        ,a.Title
        ,a.CreationDate
        ,a.Status
        ,DATEDIFF(second, CAST(SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30') AS DATETIME), BidEndDateTime) / 3600 As BidTimeLeft
        ,(SELECT COUNT(*) FROM Bidding b WHERE a.ProjectId = b.ProjectId) AS BidsCount
        ,(SELECT e.CompanyName FROM Bidding b inner join Partner e on b.PartnerId = e.PartnerId WHERE a.ProjectId = b.ProjectId and b.BidAccepted = 1) AS BidSelected
        --,h.CompanyName
        --Contact table column ApprovedbyCustomer will only have one 0 or 1 for same project, Partner can only create contract either for the first time or if it is rejected by Customer
        ,(SELECT COUNT(*) FROM Contract x WHERE a.ProjectId = x.ProjectId and x.ApprovedbyCustomer in (0,1) ) AS ContractCount
        ,g.ContractId
        --,(SELECT c.ContractId FROM Contract c WHERE a.ProjectId = c.ProjectId and c.ApprovedbyCustomer in (1,2)) AS ContractId
        ,g.ProjectValue
        , g.Duration
        , (CASE g.DurationType WHEN 1 THEN 'Days' WHEN 2 THEN 'Weeks' ELSE 'Months' END) As DurationType
        , g.StartDate
        , g.EndDate
        , g.ApprovedByCustomer

        --0 - No Action, 1- Accepted, 2- Send Back
        ,(SELECT COUNT(*) FROM PaymentRequest e WHERE a.ProjectId = e.ProjectId) AS PaymentCount
FROM
    Project a
LEFT JOIN
    Contract g ON a.ProjectId = g.ProjectId 
-- LEFT JOIN Partner h ON g.PartnerId = h.PartnerId
WHERE 
    a.CustomerId = 11111;

Paul Williams · Accepted Answer · 2020-03-03 14:43:35Z

Try a subquery with ROW_NUMBER() OVER (PARTITION BY a.ProjectId ORDER BY g.ApprovedByCustomer) AS RowNum. In the outer query, add the criteria WHERE RowNum = 1:

SELECT *
FROM
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY a.ProjectId ORDER BY g.ApprovedByCustomer) AS RowNum
        ,a.ProjectId
        ,a.Title
        ,a.CreationDate
        ,a.Status
        ,DATEDIFF(second, CAST(SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30') AS DATETIME), BidEndDateTime) / 3600 As BidTimeLeft
        ,(SELECT COUNT(*) FROM Bidding b WHERE a.ProjectId = b.ProjectId) AS BidsCount
        ,(SELECT e.CompanyName FROM Bidding b inner join Partner e on b.PartnerId = e.PartnerId WHERE a.ProjectId = b.ProjectId and b.BidAccepted = 1) AS BidSelected
        --,h.CompanyName
        --Contact table column ApprovedbyCustomer will only have one 0 or 1 for same project, Partner can only create contract either for the first time or if it is rejected by Customer
        ,(SELECT COUNT(*) FROM Contract x WHERE a.ProjectId = x.ProjectId and x.ApprovedbyCustomer in (0,1) ) AS ContractCount
        ,g.ContractId
        --,(SELECT c.ContractId FROM Contract c WHERE a.ProjectId = c.ProjectId and c.ApprovedbyCustomer in (1,2)) AS ContractId
        ,g.ProjectValue
        , g.Duration
        , (CASE g.DurationType WHEN 1 THEN 'Days' WHEN 2 THEN 'Weeks' ELSE 'Months' END) As DurationType
        , g.StartDate
        , g.EndDate
        , g.ApprovedByCustomer

        --0 - No Action, 1- Accepted, 2- Send Back
        ,(SELECT COUNT(*) FROM PaymentRequest e WHERE a.ProjectId = e.ProjectId) AS 
        PaymentCount
    FROM
        Project a
    LEFT JOIN
        Contract g ON a.ProjectId = g.ProjectId 
    -- LEFT JOIN Partner h ON g.PartnerId = h.PartnerId
    WHERE 
        a.CustomerId = 11111
) OrderedProjects
WHERE RowNum = 1;

PeterHe · Accepted Answer · 2020-03-03 14:14:26Z

0

You need to add the filter to the main query:

and g.ApprovedbyCustomer in (0,1)

answered Mar 3, 2020 at 14:14

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

3 Comments

user3060430 Over a year ago

I want multiple ProjectId's and when it is 2 then also I need the result 2 but when there are two Contracts (1 & 2) or (0&2) then it should give only 1 or 0 not 2. Hope I am clear

user3060430 Over a year ago

Is there a way I can get the column of Contract table without using left join "LEFT JOIN Contract g ON a.ProjectId = g.ProjectId"?

PeterHe Over a year ago

Sorry, don't understand what you are trying to achieve. Please post a sample sqlfiddle

Collectives™ on Stack Overflow

Nested SQL select statement in SQL Server with aggregate function

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related