November 11, 2009 at 9:04 am
I am trying to pull out the individual account number in a table but need the rest of the fields as well and there is a certain constraint that needs to be followed. I need the distinct ClientAcctNumber and distinct ClientNumber per record.
Here is what I have tried but I am new to all this and self taught so I am having issues. Any help is much appreciated.
select distinct ClientNumber, ClientAcctNumber, period, FinancialClass,
ListDate, InitialBalance, CurrentBalance, PaymentDate, PayType, AmtApplied,
NumberOfPayments, Cancel, DateCancelled, AmtCancelled, BalanceAfterTrans,
ServiceDate, Disposition, InsuranceName
from TMP9999
But I need the distinct to basically be a concatenation of ClientNumber and ClientAcctNumber. That is what will make the record distinct.
November 11, 2009 at 9:09 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 9:18 am
Sorry about that first time here. Give me a few minutes and Ill have that information up.
November 11, 2009 at 10:14 am
CREATE TABLE tmp9999
(
ClientNumber VARCHAR
ClientAcctNumber VARCHAR
Period VARCHAR
FinancialClass VARCHAR
Listdate DATETIME
InitialBalance MONEY
CurrentBalance MONEY
PaymentDate DATETIME
PayType VARCHAR
AmtAppliedMONEY
NumberOfPayments INT
Cancel VARCHAR
DateCancelled DATETIME
AmtCancelled MONEY
BalanceAfterTrans MONEY
ServiceDate DATETIME
Disposition VARCHAR
InsuranceName VARCHAR
)
Insert into tmp9999
(ClientNumber, ClientAcctNumber, period, FinancialClass,
ListDate, InitialBalance, CurrentBalance, PaymentDate, PayType,
AmtApplied,NumberOfPayments, Cancel, DateCancelled, AmtCancelled,
BalanceAfterTrans,ServiceDate, Disposition, InsuranceName)
Select 6035IN,101148117,13,8/21/2009,4149.75,4149.75,,,0,0,,,0,0,4/9/2008,3LN1,UNION ALL
Select 6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,3253.69,0,HRT,5/21/2009,4149.75,3533.15,4/9/2008,9999,MEDICARE,UNION ALL
select 6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,616.6,0,HRT,5/21/2009,4149.75,4149.75,4/9/2008,9999,MEDICARE,UNION ALL
select 6035IN,101244488,13,6/9/2009,711,0,,,0,0,DUP,6/12/2009,711,0,5/26/2008,9999,UNION ALL
Select 6035BD,101244488,,10/24/2008,711,0,,,0,0,HRT,3/2/2009,711,0,5/26/2008,9999,UNION ALL
Select 6035IN,101422296,13,7/30/2009,360.75,0,,,0,0,DUP,8/17/2009,360.75,0,8/27/2008,9999,UNION ALL
Select 6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,DP,37.59,1,,,0,323.16,8/27/2008,3FUP,UNION ALL
Select 6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP,UNION ALL
Select 6035IN,101500850,13,10/15/2009,277,0,,,0,0,DUP,10/29/2009,277,0,10/7/2008,9999,UNION ALL
Select 6035BD,101500850,,3/10/2009,277,277,,,0,0,,,0,0,10/7/2008,3700,UNION ALL
Select 6062BD,5050896,99,6/8/2009,744.45,0,,,0,0,HRT,9/15/2009,744.45,0,5/4/2008,9999,UNION ALL
Select 6062IN,5050896,,5/27/2008,744.45,0,,,0,0,CPC,9/24/2008,744.45,0,5/4/2008,9999,UNION ALL
Select 6062IN,5591018,13,10/19/2009,271.2,271.2,,,0,0,,,0,0,10/9/2008,3NEW,UNION ALL
Select 6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,DP,484.77,1,HRT,7/27/2009,271.2,437.14,10/9/2008,9999,UNION ALL
Select 6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,CRJ,165.94,1,HRT,7/27/2009,271.2,271.2,10/9/2008,9999,UNION ALL
Select 6062MC,5628031,12,10/6/2009,50,50,,,0,0,,,0,0,10/20/2008,3LTR,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,DP,485.13,1,HRT,8/31/2009,50,1440.97,10/29/2008,9999,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,CRJ,1321.14,1,HRT,8/31/2009,50,119.83,10/29/2008,9999,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/24/2009,CRJ,69.83,1,HRT,8/31/2009,50,50,10/29/2008,9999,UNION ALL
Select 6062IN,5632977,13,10/20/2009,75,0,,,0,0,DUP,10/29/2009,75,0,10/21/2008,9999,UNION ALL
Select 6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,CRJ,1505.15,1,HRT,10/26/2009,75,6856.77,10/21/2008,9999, UNION ALL
Select 6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,DP,6781.77,1,HRT,10/26/2009,75,75,10/21/2008,9999,
Here is a sample of the current output sorted by ClientAcctNumber
6062BD,5050896,99,6/8/2009,744.45,0,,,0,0,HRT,9/15/2009,744.45,0,5/4/2008,9999,
6062IN,5050896,,5/27/2008,744.45,0,,,0,0,CPC,9/24/2008,744.45,0,5/4/2008,9999,
6062IN,5591018,13,10/19/2009,271.2,271.2,,,0,0,,,0,0,10/9/2008,3NEW,
6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,DP,484.77,1,HRT,7/27/2009,271.2,437.14,10/9/2008,9999,
6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,CRJ,165.94,1,HRT,7/27/2009,271.2,271.2,10/9/2008,9999,
6062MC,5628031,12,10/6/2009,50,50,,,0,0,,,0,0,10/20/2008,3LTR,
6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,DP,485.13,1,HRT,8/31/2009,50,1440.97,10/29/2008,9999,
6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,CRJ,1321.14,1,HRT,8/31/2009,50,119.83,10/29/2008,9999,
6062BD,5628031,,3/24/2009,1926.1,0,6/24/2009,CRJ,69.83,1,HRT,8/31/2009,50,50,10/29/2008,9999,
6062IN,5632977,13,10/20/2009,75,0,,,0,0,DUP,10/29/2009,75,0,10/21/2008,9999,
6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,CRJ,1505.15,1,HRT,10/26/2009,75,6856.77,10/21/2008,9999,
6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,DP,6781.77,1,HRT,10/26/2009,75,75,10/21/2008,9999,
6035IN,101148117,13,8/21/2009,4149.75,4149.75,,,0,0,,,0,0,4/9/2008,3LN1,
6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,3253.69,0,HRT,5/21/2009,4149.75,3533.15,4/9/2008,9999,MEDICARE
6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,616.6,0,HRT,5/21/2009,4149.75,4149.75,4/9/2008,9999,MEDICARE
6035IN,101244488,13,6/9/2009,711,0,,,0,0,DUP,6/12/2009,711,0,5/26/2008,9999,
6035BD,101244488,,10/24/2008,711,0,,,0,0,HRT,3/2/2009,711,0,5/26/2008,9999,
6035IN,101422296,13,7/30/2009,360.75,0,,,0,0,DUP,8/17/2009,360.75,0,8/27/2008,9999,
6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,DP,37.59,1,,,0,323.16,8/27/2008,3FUP,
6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP,
6035IN,101500850,13,10/15/2009,277,0,,,0,0,DUP,10/29/2009,277,0,10/7/2008,9999,
6035BD,101500850,,3/10/2009,277,277,,,0,0,,,0,0,10/7/2008,3700,
Here is an example of the output that I am looking for
6062BD,5050896,99,6/8/2009,744.45,0,,,0,0,HRT,9/15/2009,744.45,0,5/4/2008,9999,
6062IN,5050896,,5/27/2008,744.45,0,,,0,0,CPC,9/24/2008,744.45,0,5/4/2008,9999,
6062IN,5591018,13,10/19/2009,271.2,271.2,,,0,0,,,0,0,10/9/2008,3NEW,
6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,CRJ,165.94,1,HRT,7/27/2009,271.2,271.2,10/9/2008,9999,
6062MC,5628031,12,10/6/2009,50,50,,,0,0,,,0,0,10/20/2008,3LTR,
6062BD,5628031,,3/24/2009,1926.1,0,6/24/2009,CRJ,69.83,1,HRT,8/31/2009,50,50,10/29/2008,9999,
6062IN,5632977,13,10/20/2009,75,0,,,0,0,DUP,10/29/2009,75,0,10/21/2008,9999,
6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,DP,6781.77,1,HRT,10/26/2009,75,75,10/21/2008,9999,
6035IN,101148117,13,8/21/2009,4149.75,4149.75,,,0,0,,,0,0,4/9/2008,3LN1,
6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,616.6,0,HRT,5/21/2009,4149.75,4149.75,4/9/2008,9999,MEDICARE
6035IN,101244488,13,6/9/2009,711,0,,,0,0,DUP,6/12/2009,711,0,5/26/2008,9999,
6035BD,101244488,,10/24/2008,711,0,,,0,0,HRT,3/2/2009,711,0,5/26/2008,9999,
6035IN,101422296,13,7/30/2009,360.75,0,,,0,0,DUP,8/17/2009,360.75,0,8/27/2008,9999,
6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP,
6035IN,101500850,13,10/15/2009,277,0,,,0,0,DUP,10/29/2009,277,0,10/7/2008,9999,
6035BD,101500850,,3/10/2009,277,277,,,0,0,,,0,0,10/7/2008,3700,
I hope this clarifies things for everyone out there. If there is anything you may need to help please let me know.
Thanks
November 11, 2009 at 11:06 am
There are two rows with ClientNumber, ClientAccNumber 6035BD,101422296. What determines which of the rows you want in the output?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 11:11 am
Thats where I thought I could use Distinct to determine to keep only one of those records. For the process I am currently working on I only need one. So what would determine it to me is ClientNumber and ClientAcctNumber being the distinct determinant but I dont know how to do that. Hope this helps.
November 11, 2009 at 11:15 am
6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,DP,37.59,1,,,0,323.16,8/27/2008,3FUP,
6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP,
In these 2 records, your desired ouptut needs only
" 6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP"
What is the basis for this record to be identified. I see only few column values are differing at both records. Can you explain it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 11, 2009 at 11:22 am
I am going to have to do some sums of the balances and and total accounts later on and do not need to count duplicate records. For instance I will need to know how many accounts with ClientNumber 6035BD have a disposition of ('31PP','3100','31CC','31AP','3700') and what is the total sum of the Currentbalance for those results.
November 11, 2009 at 11:34 am
Ok, now I'm really confused. If you're doing sums later, surely you need all the rows? If not, which of the 'duplicate' rows do you want to keep and which do you want to discard? Or does it not matter which you get back?
I realise you need one row for the combo of clientNumber and ClientAccountNumber. What I'm asking is, based on the other columns, what determines the row that you want?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 11:36 am
What's more painful in understanding is your create table or insert scripts do not work and its reaaly difficult to do that all manually from my side and then see the Data in Table.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 11, 2009 at 11:50 am
Sorry about all the confusion. It doesn't matter which of the dups I keep because the fields I need to sum on are the same in both records.
November 11, 2009 at 12:05 pm
To clear the confusion, can you come up with the correct create table script and also few Insert Stmts (may be 5) to display what it is and what you intend to do, with explanation.
While posting scripts use the Code Tags for better look and understanding.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 11, 2009 at 12:12 pm
I create all my tables in Enterprise Manager. I dont use scripts for those thats probably why the one I posted is wrong.
November 11, 2009 at 12:19 pm
blambu56 (11/11/2009)
I create all my tables in Enterprise Manager. I dont use scripts for those thats probably why the one I posted is wrong.
A syntax check would have shown it was wrong. All the commas are missing and the lengths of the varchars are missing. I didn't notice cause I don't start coding until I understand the requirements.
Is this a once-off operation or a regular query? Possibly the easiest way in SQL 2000 is to insert into a temp table, add an identity column and use that for a MAX aggregation to get just one row. Unless any of the columns in your source data is guaranteed unique. What's the primary key?
This is so much easier in SQL 2005. 🙁
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 12:23 pm
Attempt number two for you my friend. Hope this works.
CREATE TABLE tmp9999
(
ClientNumber VARCHAR(10) null,
ClientAcctNumber VARCHAR(20) null,
Period VARCHAR (8) null,
FinancialClass VARCHAR(10) null,
Listdate DATETIME(8) null,
InitialBalance MONEY(8) null,
CurrentBalance MONEY(8) null,
PaymentDate DATETIME(8) null,
PayType VARCHAR(12) null,
AmtAppliedMONEY(8) null,
NumberOfPayments INT(4) null,
Cancel VARCHAR(13) null,
DateCancelled DATETIME(8) null,
AmtCancelled MONEY(8) null,
BalanceAfterTrans MONEY(8) null,
ServiceDate DATETIME(8) null,
Disposition VARCHAR(20) null,
InsuranceName VARCHAR(255) null
);
Insert into tmp9999
(ClientNumber, ClientAcctNumber, period, FinancialClass,
ListDate, InitialBalance, CurrentBalance, PaymentDate, PayType,
AmtApplied,NumberOfPayments, Cancel, DateCancelled, AmtCancelled,
BalanceAfterTrans,ServiceDate, Disposition, InsuranceName)
Select 6035IN,101148117,13,8/21/2009,4149.75,4149.75,,,0,0,,,0,0,4/9/2008,3LN1,,UNION ALL
Select 6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,3253.69,0,HRT,5/21/2009,4149.75,3533.15,4/9/2008,9999,MEDICARE,,UNION ALL
select 6035MC,101148117,1,10/9/2008,279.46,0,12/3/2008,DBJ,616.6,0,HRT,5/21/2009,4149.75,4149.75,4/9/2008,9999,MEDICARE,,UNION ALL
select 6035IN,101244488,13,6/9/2009,711,0,,,0,0,DUP,6/12/2009,711,0,5/26/2008,9999,,UNION ALL
Select 6035BD,101244488,,10/24/2008,711,0,,,0,0,HRT,3/2/2009,711,0,5/26/2008,9999,,UNION ALL
Select 6035IN,101422296,13,7/30/2009,360.75,0,,,0,0,DUP,8/17/2009,360.75,0,8/27/2008,9999,,UNION ALL
Select 6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,DP,37.59,1,,,0,323.16,8/27/2008,3FUP,,UNION ALL
Select 6035BD,101422296,99,5/8/2009,360.75,150,8/10/2009,CRJ,173.16,1,,,0,150,8/27/2008,3FUP,,UNION ALL
Select 6035IN,101500850,13,10/15/2009,277,0,,,0,0,DUP,10/29/2009,277,0,10/7/2008,9999,,UNION ALL
Select 6035BD,101500850,,3/10/2009,277,277,,,0,0,,,0,0,10/7/2008,3700,,UNION ALL
Select 6062BD,5050896,99,6/8/2009,744.45,0,,,0,0,HRT,9/15/2009,744.45,0,5/4/2008,9999,,UNION ALL
Select 6062IN,5050896,,5/27/2008,744.45,0,,,0,0,CPC,9/24/2008,744.45,0,5/4/2008,9999,,UNION ALL
Select 6062IN,5591018,13,10/19/2009,271.2,271.2,,,0,0,,,0,0,10/9/2008,3NEW,,UNION ALL
Select 6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,DP,484.77,1,HRT,7/27/2009,271.2,437.14,10/9/2008,9999,,UNION ALL
Select 6062BD,5591018,,1/12/2009,921.91,0,7/10/2009,CRJ,165.94,1,HRT,7/27/2009,271.2,271.2,10/9/2008,9999,,UNION ALL
Select 6062MC,5628031,12,10/6/2009,50,50,,,0,0,,,0,0,10/20/2008,3LTR,,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,DP,485.13,1,HRT,8/31/2009,50,1440.97,10/29/2008,9999,,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/17/2009,CRJ,1321.14,1,HRT,8/31/2009,50,119.83,10/29/2008,9999,,UNION ALL
Select 6062BD,5628031,,3/24/2009,1926.1,0,6/24/2009,CRJ,69.83,1,HRT,8/31/2009,50,50,10/29/2008,9999,,UNION ALL
Select 6062IN,5632977,13,10/20/2009,75,0,,,0,0,DUP,10/29/2009,75,0,10/21/2008,9999,,UNION ALL
Select 6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,CRJ,1505.15,1,HRT,10/26/2009,75,6856.77,10/21/2008,9999,,UNION ALL
Select 6062BD,5632977,99,4/14/2009,8361.92,0,10/9/2009,DP,6781.77,1,HRT,10/26/2009,75,75,10/21/2008,9999,
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply