Solved

help with simple T-SQL

Posted on 2014-01-25
6
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 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