CODEWITHSUNDEEP
sql-serverjoin
user3060430
user3060430

Reputation: 131

Nested SQL select statement in SQL Server with aggregate function

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;

Current Result

Upvotes: 0

Views: 52

Answers (2)

Paul Williams
Paul Williams

Reputation: 17040

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;

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

You need to add the filter to the main query:

and g.ApprovedbyCustomer in (0,1)

Upvotes: 0

Related Questions