CODEWITHSUNDEEP
sqlsql-server
matt80
matt80

Reputation: 35

Looking to pick up all records from a table that belong to a client that meets specified criteria

I'm a fairly basic SQL Server Management Studio user, I know what I need to usually but this is new to me.

I have a current extract that picks out all rows from a table ci_periodicBillings where the rate is '165.56' or '-165.56' in the given time period. This is fine and is working but I have an additional requirement now to show all entries from ci_periodicBillings regardless of the rate AS LONG AS THE CLIENT HAS AT LEAST ONE where the rate is '165.56' or '-165.56'. so client A has 5 rows with different rates but 1 is '165.56' - i need to show all of the 5 rows client B has 7 rows with different rates and 0 meet the criteria - i need none of them.

I already have the below code to pick out the '156.56' or '-156.56' values but I need to add in the new requirement of showing all the rows belonging to each client.

Thanks for any help.

SELECT f.NAME                     AS Home, 
       c.forename, 
       c.surname, 
       c.resid, 
       Cast(c.admission AS DATE)  AS ADMISSION, 
       Cast(c.leavedate AS DATE)  AS LEAVE, 
       fm.fundingname, 
       p.description              AS ServiceType, 
       Cast(pb.billstart AS DATE) AS BILLSTART, 
       Cast(pb.endbill AS DATE)   AS ENDBILL, 
       a.accountcode, 
       a.accountname 
FROM   cs_facilities f 
       LEFT JOIN cs_clients c 
              ON f.guid = c.facility 
       LEFT JOIN ci_periodicbillings pb 
              ON c.guid = pb.client 
       LEFT JOIN cs_fundingmethods fm 
              ON pb.restype = fm.fundingid 
       LEFT JOIN ci_products p 
              ON pb.product = p.guid 
       LEFT JOIN cs_accounts a 
              ON pb.accountguid = a.account 
WHERE  ( ( billstart BETWEEN '2019-04-01' AND '2020-03-31' ) 
          OR ( endbill BETWEEN '2019-04-01' AND '2020-03-31' ) 
          OR ( billstart <= '2019-04-01' 
               AND endbill >= '2020-03-31' ) ) 
       AND ( pb.rate = '165.56' 
              OR pb.rate = '-165.56' ) 
ORDER  BY f.NAME, 
          c.surname ASC, 
          c.forename ASC

Upvotes: 0

Views: 80

Answers (1)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

remove the pb.rate ... part in your query and add the following just before the where clause

Inner JOIN ( Select * from ci_periodicbillings where pb.rate in('165.56','-165.56')  )  restrictedClients 
              ON c.guid = restrictedClients.client

This will join just the clients, who has '165.56' or '-165.56' as the rate in the bill.

Upvotes: 1

Related Questions