Solved

help with simple T-SQL

Posted on 2014-01-25
6
289 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 32

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 32

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 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