Solved

help with simple T-SQL

Posted on 2014-01-25
6
264 Views
Last Modified: 2014-01-27
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
Comment
Question by:cheryl9063
6 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39809132
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
 
LVL 13

Expert Comment

by:magarity
ID: 39809561
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39809762
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39810126
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
 
LVL 31

Expert Comment

by:awking00
ID: 39812678
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39812695
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now