| Author |
 Topic  |
|
|
Dale45039
Starting Member
41 Posts |
 Posted - 07/17/2012 : 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
India
48029 Posts |
Posted - 07/17/2012 : 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 - 07/17/2012 : 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 - 07/17/2012 : 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 - 07/17/2012 : 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
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 07/17/2012 : 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
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 07/17/2012 : 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
India
48029 Posts |
Posted - 07/17/2012 : 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 - 07/19/2012 : 12:09:59
|
| datatype is Datetime |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48029 Posts |
Posted - 07/19/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Select Distinct
