September 13, 2017 at 7:54 pm
Can anyone help with a query based on several tables and different criteria based on a status. Background - Tables are joined to get contact info and balance of account. Within that we have a status of Free, Reduced, and Paid. In the example below I am excluding Free (F) and Reduced (R) because I don't want the records of (F) or (R) if they are below the threshold of $10. I only want the records of those if they are a negative balance. So in essence the final result would be if your account is not (F) or (R) and balance is =>10 then give result and if your status is (F) or (R) and balance is =>-0.01 give result. I'm a novice at this so sorry if I have not explained well.
SELECT DISTINCT SUBSTRING(hh.phone, 2,3) + SUBSTRING(hh.phone, 6,3) + SUBSTRING(hh.phone, 10,4) AS Phone, firstName, lastName,balance
FROM Person p
INNER JOIN byers.dbo.[Identity] i ON p.currentIdentityID = i.[identityID]
INNER JOIN householdMember hm ON p.personID = hm.personID
INNER JOIN household hh ON hm.householdID = hh.householdID
INNER JOIN v_POSAccountDetail b on p.personID =b.PersonID
INNER JOIN POSEligibility E on p.personID = e.PersonID
where p.staffNumber is null
and b.balance < '10.00'
and e.eligibility <> ('F')
and e.eligibility <> ('R')
September 14, 2017 at 1:45 am
have you tried adding :and ( ( b.balance > 10
OR b.balance < 0 )
and e.eligibility in ('F', 'R') )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 14, 2017 at 6:54 am
jdhayes - Wednesday, September 13, 2017 7:54 PMCan anyone help with a query based on several tables and different criteria based on a status. Background - Tables are joined to get contact info and balance of account. Within that we have a status of Free, Reduced, and Paid. In the example below I am excluding Free (F) and Reduced (R) because I don't want the records of (F) or (R) if they are below the threshold of $10. I only want the records of those if they are a negative balance. So in essence the final result would be if your account is not (F) or (R) and balance is =>10 then give result and if your status is (F) or (R) and balance is =>-0.01 give result. I'm a novice at this so sorry if I have not explained well.SELECT DISTINCT SUBSTRING(hh.phone, 2,3) + SUBSTRING(hh.phone, 6,3) + SUBSTRING(hh.phone, 10,4) AS Phone, firstName, lastName,balance
FROM Person p
INNER JOIN byers.dbo.[Identity] i ON p.currentIdentityID = i.[identityID]
INNER JOIN householdMember hm ON p.personID = hm.personID
INNER JOIN household hh ON hm.householdID = hh.householdID
INNER JOIN v_POSAccountDetail b on p.personID =b.PersonID
INNER JOIN POSEligibility E on p.personID = e.PersonID
where p.staffNumber is null
and b.balance < '10.00'
and e.eligibility <> ('F')
and e.eligibility <> ('R')
I'm pretty sure ALZDBA didn't quite get it right. Try this instead:SELECT DISTINCT SUBSTRING(hh.phone, 2, 3) + SUBSTRING(hh.phone, 6, 3) + SUBSTRING(hh.phone, 10, 4) AS Phone, firstName, lastName, balance
FROM Person AS p
INNER JOIN byers.dbo.[Identity] AS i
ON p.currentIdentityID = i.[identityID]
INNER JOIN householdMember AS hm
ON p.personID = hm.personID
INNER JOIN household AS hh
ON hm.householdID = hh.householdID
INNER JOIN v_POSAccountDetail AS b
ON p.personID =b.PersonID
INNER JOIN POSEligibility AS E
ON p.personID = e.PersonID
WHERE p.staffNumber IS NULL
AND (
(b.balance >= 10.00 AND e.eligibility NOT IN ('F','R')
OR
(b.balance < 0. AND e.eligibility IN ('F', 'R'))
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 14, 2017 at 12:57 pm
Thanks you guys. I took the solution and was able to modify how I was using the query to get what I needed. Thank you again!
September 14, 2017 at 3:00 pm
Glad I could help. Always appreciate if you'll mark the posts that solve your problem as the fix. You can mark more than one.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply