help with simple T-SQL
from the attached test data I need to select the PatID, name,apptDate,CallDate and funding. if the person has more than one type of funding I only want it to show 'credit card'. I have done this several ways and I want to see what the Experts would do. The real table will have about 24,000 rows with one index on TID. The result should have one row per Pat_ID.
TID LastName FirstName Funding ApptDate CallDate PatID
1 smith cheryl cash 1/2/13 12:00 AM 12/26/13 12:00 AM 200
2 smith cheryl check 1/2/13 12:00 AM 12/26/13 12:00 AM 200
3 smith cheryl creditcard 1/2/13 12:00 AM 12/26/13 12:00 AM 200
4 efird katie creditcard 1/1/13 12:00 AM 12/27/13 12:00 AM 5000
5 jones tim creditcard 12/1/13 12:00 AM 11/27/13 12:00 AM 25
6 rogers sam cash 10/1/13 12:00 AM 10/1/13 12:00 AM 150
7 Peterson Mark cash 10/1/13 12:00 AM 10/1/13 12:00 AM 175
8 Peterson Mark cash 9/1/13 12:00 AM 8/20/13 12:00 AM 175
9 Peterson Mark creditcard 9/1/13 12:00 AM 8/25/13 12:00 AM 175
10 Walls Rick creditcard 8/1/13 12:00 AM 7/25/13 12:00 AM 220
11 Walls Rick check 8/1/13 12:00 AM 7/20/13 12:00 AM 220
12 Johnson Mike check 8/1/13 12:00 AM 7/20/13 12:00 AM 555
13 Sees betty cash 8/1/13 12:00 AM 7/20/13 12:00 AM 2233
TID LastName FirstName Funding ApptDate CallDate PatID
1 smith cheryl cash 1/2/13 12:00 AM 12/26/13 12:00 AM 200
2 smith cheryl check 1/2/13 12:00 AM 12/26/13 12:00 AM 200
3 smith cheryl creditcard 1/2/13 12:00 AM 12/26/13 12:00 AM 200
4 efird katie creditcard 1/1/13 12:00 AM 12/27/13 12:00 AM 5000
5 jones tim creditcard 12/1/13 12:00 AM 11/27/13 12:00 AM 25
6 rogers sam cash 10/1/13 12:00 AM 10/1/13 12:00 AM 150
7 Peterson Mark cash 10/1/13 12:00 AM 10/1/13 12:00 AM 175
8 Peterson Mark cash 9/1/13 12:00 AM 8/20/13 12:00 AM 175
9 Peterson Mark creditcard 9/1/13 12:00 AM 8/25/13 12:00 AM 175
10 Walls Rick creditcard 8/1/13 12:00 AM 7/25/13 12:00 AM 220
11 Walls Rick check 8/1/13 12:00 AM 7/20/13 12:00 AM 220
12 Johnson Mike check 8/1/13 12:00 AM 7/20/13 12:00 AM 555
13 Sees betty cash 8/1/13 12:00 AM 7/20/13 12:00 AM 2233
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The rules seem incomplete; what about an account that has only cash and check? As worded, your instructions say to label that as 'credit card' and that's what David's query provides, but that's the sort of assumption that is first to crash headlong on production data.
Hi Magarity,
Hopefully not a crash as produces the result asked for, but definitely could be an unexpected result by some parties.
I remember the discussion by Joe Celko about gender, that internationally there are 4 values. Lets see if memory is correct. (And they are coded, and I can't remember the codes)
Male/Female/Unknown/Lawful
That is, Lawful Person is the value you give companies and trusts and so on. They are a legal entity, and may fulfil many criteria for a person in your system, but they don't have a gender. (ie investments must be owned by a person or company or trust. So client gender for a trust then must be Lawful Person.
All that to say this: It is strictly incorrect to return credit card for multiple types. You could in this case rank the types cash/eftpos/cheque/credit card in order of ease of business, and return the most difficult one, or add a value that is 'multiple payment types'.
HTH
David
Hopefully not a crash as produces the result asked for, but definitely could be an unexpected result by some parties.
I remember the discussion by Joe Celko about gender, that internationally there are 4 values. Lets see if memory is correct. (And they are coded, and I can't remember the codes)
Male/Female/Unknown/Lawful
That is, Lawful Person is the value you give companies and trusts and so on. They are a legal entity, and may fulfil many criteria for a person in your system, but they don't have a gender. (ie investments must be owned by a person or company or trust. So client gender for a trust then must be Lawful Person.
All that to say this: It is strictly incorrect to return credit card for multiple types. You could in this case rank the types cash/eftpos/cheque/credit card in order of ease of business, and return the most difficult one, or add a value that is 'multiple payment types'.
HTH
David
I modified your request to show the credit card, if there was one, or the first funding type available (if there wasn't a credit card) based on a sort order.
SELECT v.*, p.SortOrder
INTO #tmp
FROM (VALUES
(1,'smith','cheryl','cash','2013-01-02','2013-12-26',200),
(2,'smith','cheryl','check','2013-01-02','2013-12-26',200),
(3,'smith','cheryl','creditcard','2013-01-02','2013-12-26',200),
(4,'efird','katie','creditcard','2013-01-01','2013-12-27',5000),
(5,'jones','tim','creditcard','2013-12-01','2013-11-27',25),
(6,'rogers','sam','cash','2013-10-01','2013-10-01',150),
(7,'Peterson','Mark','cash','2013-10-01','2013-10-01',175),
(8,'Peterson','Mark','cash','2013-09-01','2013-08-20',175),
(9,'Peterson','Mark','creditcard','2013-09-01','2013-08-25',175),
(10,'Walls','Rick','creditcard','2013-08-01','2013-07-25',220),
(11,'Walls','Rick','check','2013-08-01','2013-07-20',220),
(12,'Johnson','Mike','check','2013-08-01','2013-07-20',555),
(13,'Sees','betty','cash','2013-08-01','2013-07-20',2233)) v(TID, LastName, FirstName, Funding, ApptDate, CallDate, PatID)
INNER JOIN (VALUES('creditcard',1),('cash',2),('check',3)) p(Funding, SortOrder) ON v.Funding = p.Funding
SELECT t.*
FROM ( SELECT t1.PatID, COUNT(1) as NbrRows FROM #tmp t1 GROUP BY t1.PatID ) ftc
INNER JOIN #tmp t ON ftc.PatID = t.PatID
WHERE ftc.NbrRows = 1 OR (ftc.NbrRows > 1 AND t.SortOrder = 1)
The questions remain, can there be more than one funding type per patid, notn of which are creditcard and, if so, what would the selection critieria be? Also, can there be funding types other than cash, check and creditcard?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.