Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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
0
cheryl9063
Asked:
cheryl9063
2 Solutions
 
David ToddSenior DBACommented:
Hi,

I think this data set is fairly  simplistic, and assumes that the normalisation is all okay. That is, we are grouping on PatID, and that there are no name discrepancies.

Try this

Regards
  David

use ExpertsExchange
go

if object_id( N'tempdb..#Appointment', N'U' ) is not null 
	drop table #Appointment;
	
create table #Appointment(
	TID int 
	, LastName varchar( 20 )
	, FirstName varchar( 20 )
	, Funding varchar( 20 )
	, ApptDate datetime
	, CallDate datetime
	, PatID int
	)
;

set dateformat mdy

insert #Appointment( TID, LastName, FirstName, Funding, ApptDate, CallDate, PatID )
values
(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 )
;

set dateformat dmy
	
select *
from #Appointment
;

select
	PatID
	, max( LastName )
	, max( FirstName )
	, max( Funding )
	, max( ApptDate )
from #Appointment
group by
	PatID
order by
	PatID
;

-- Add a few rows to mix up the Funding
-- And using a 'better' date format
insert #Appointment( TID, LastName, FirstName, Funding, ApptDate, CallDate, PatID )
values
(14,      'Sees',      'betty',      'check',      '2013-10-01',      '2013-10-01',      2233)
, (15,      'Sees',      'betty',      'check',      '2013-10-01',      '2013-10-01',      2233)
;

-- Note that Betty Sees displays check where according the requirements it should be creditcard
select
	PatID
	, max( LastName )
	, max( FirstName )
	, max( Funding )
	, max( ApptDate )
from #Appointment
group by
	PatID
order by
	PatID
;

select
	PatID
	, max( LastName )
	, max( FirstName )
	, case	
		when count( Funding ) > 1 then 'creditcard'
		else max( Funding )
	end
	, max( ApptDate )
from #Appointment
group by
	PatID
order by
	PatID
;

Open in new window

0
 
magarityCommented:
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.
0
 
David ToddSenior DBACommented:
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 Person.

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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Lawrence BarnesCommented:
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)

Open in new window

0
 
awking00Commented:
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?
0
 
awking00Commented:
If the answers to those questions are no, then -
select tid, lastname, firstname, funding, apptdate, calldate, patid from
(select tid, lastname, firstname, funding, apptdate, calldate, patid,
 row_number() over (partition by patid order by funding desc) rn
 from testdata) as x
where x.rn = 1;
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now