Select Distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Distinct

Author

Topic

Dale45039
Starting Member

41 Posts

Posted - 2012-07-17 : 15:12:59

How do I add "vwGenCustApptInfo.Appt_DateTime" and "vwGenCustApptInfo.Appt_Sched_Location_Descr" columns to the results and prevent getting duplicate customers?

===========================
Here is the original query:
===========================
Select DISTINCT
vwGenAcctBalInfo.Acct_Self_Balance,
vwGenCustInfo.Customer_ID,
vwGenCustInfo.Customer_Last_Name,
vwGenCustInfo.Customer_First_Name,
vwGenCustInfo.Customer_DOB
From
vwGenAcctBalInfo Inner Join
vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenAcctBalInfo.Account_ID
Inner Join
vwGenCustApptInfo On vwGenCustApptInfo.Customer_ID =
vwGenCustInfo.Customer_ID
Where (vwGenCustApptInfo.Appt_DateTime Between '2012-07-10 00:00:01' And
'2012-07-10 23:59:59') AND (vwGenAcctBalInfo.Acct_Self_Balance > '0')
Order By Customer_Last_Name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 15:16:30

without seeing data we cant suggest much. post some data from tables and then explain what you want from them as output and what you mean by duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 15:18:04

Well don't you have different datetimes and descriptions per customer?

Data BELONGS to the row it's on.

You shouldn't mix and match, unless you have a business reason?

Like...

Give me the Customer Info, and the last datetime and description for them

Is that what you want?

Dale45039
Starting Member

41 Posts

Posted - 2012-07-17 : 15:36:38

This is what I would like to see without the duplicate Customer on rows 4 and 5:

"Acct_Self_Balance","Customer_ID","Customer_Last_Name","Customer_First_Name","Customer_DOB","Appt_DateTime","Appt_Sched_Location_Descr"
"72.07","123456","Smith","John","7/22/1961","7/10/2012 08:30:00 AM","OFFICE1"
"215.01","234567","Brown","Steve","4/21/1975","7/10/2012 08:30:00 AM","OFFICE2"
"15.34","345678","Last","James","5/17/1951","7/10/2012 09:00:00 AM","OFFICE2"
"15.34","345678","Last","James","6/17/1951","7/10/2012 09:30:00 AM","OFFICE2"
"250.9","456789","Click","William","2/6/1969","7/10/2012 09:00:00 AM","OFFICE3"

Dale45039
Starting Member

41 Posts

Posted - 2012-07-17 : 15:39:08

quote:
Originally posted by Dale45039

This is what I would like to see without the duplicate Customer on rows 4 and 5:

"Acct_Self_Balance","Customer_ID","Customer_Last_Name","Customer_First_Name","Customer_DOB","Appt_DateTime","Appt_Sched_Location_Descr"
"72.07","123456","Smith","John","7/22/1961","7/10/2012 08:30:00 AM","OFFICE1"
"215.01","234567","Brown","Steve","4/21/1975","7/10/2012 08:30:00 AM","OFFICE2"
"15.34","345678","Last","James","5/17/1951","7/10/2012 09:00:00 AM","OFFICE2"
"15.34","345678","Last","James","6/17/1951","7/10/2012 09:30:00 AM","OFFICE2"
"250.9","456789","Click","William","2/6/1969","7/10/2012 09:00:00 AM","OFFICE3"

I changed the Customer_DOB on rows 4 and 5. Both rows should be 5/17/1951.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-17 : 15:43:50

You might try using the ROW_NUMBER function.

SELECT *
FROM
	(
		Select 
			vwGenAcctBalInfo.Acct_Self_Balance,
			vwGenCustInfo.Customer_ID,
			vwGenCustInfo.Customer_Last_Name,
			vwGenCustInfo.Customer_First_Name,
			vwGenCustInfo.Customer_DOB,
			ROW_NUMBER() OVER (PARTITION BY Customer_Last_Name ORDER BY vwGenCustInfo.Customer_DOB DESC) AS RowNum
		From
			vwGenAcctBalInfo 
		Inner Join
			vwGenCustInfo 
			On vwGenCustInfo.Customer_ID = vwGenAcctBalInfo.Account_ID
		Inner Join
			vwGenCustApptInfo 
			On vwGenCustApptInfo.Customer_ID = vwGenCustInfo.Customer_ID 
		Where 
			vwGenCustApptInfo.Appt_DateTime Between '2012-07-10 00:00:01' And '2012-07-10 23:59:59'
			AND vwGenAcctBalInfo.Acct_Self_Balance > '0'
	) AS T
WHERE
	RowNum = 1
Order By 
	Customer_Last_Name

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-17 : 15:45:15

Also, what is the datatype of the "Appt_DateTime" column? It's possible that your BETWEEN clause could miss values that have fractions of a second (if that's possible for the datatype).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 22:53:11

doesnt look like datetime though...seeing values at least

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dale45039
Starting Member

41 Posts

Posted - 2012-07-19 : 12:09:59

datatype is Datetime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 13:04:55

then why not use condition like


vwGenCustApptInfo.Appt_DateTime >= '2012-07-10' 
And vwGenCustApptInfo.Appt_DateTime< '2012-07-11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources