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 

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-16 : 10:48:32
Hello,

When I try the SELECT DISTINCT like this:


USE CHEC

SELECT DISTINCT
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
DAT01.[_@550] AS LoanType,
DAT01.[_@051] AS Branch,
DAT01.[_@TP] AS ProdTypeDescr,
SMT_Branches.[BranchTranType] AS TranType,
--SMT_Branches.[AUCode] AS AuCode,
COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
ON DAT01.[_@040] = [_@040]
INNER JOIN SMT_BRANCHES
ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
AND DAT01.[_@051] = '540'
--And SMT_Branches.[AUCode] = '1882'
And DAT01.[_@TP] = '115'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
SMT_Branches.[BranchTranType],
--SMT_Branches.[AUCode],
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
DAT01.[_@051],
DayofMonth ASC
--SMT_Branches.[AUCode] ASC
--COMPUTE sum(count(*))


I get the same result set as before.

What do I need to change?

Kurt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:54:31
You have posted this code on several thread. It will very much help those who are trying to help you if you can post the table structure, some sample data that illustrate the problem you have. Sometimes Most of the time, having a sample data and the expected result will make things clearer and getting the solution faster.


KH

Go to Top of Page

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-16 : 11:00:13
Hello khtan,

Here is some of the sample data:

APRIL 1 3 540 115 RETAIL 4446
APRIL 2 3 540 115 RETAIL 4446
APRIL 3 3 540 115 RETAIL 4446
APRIL 4 3 540 115 RETAIL 4446
APRIL 5 3 540 115 RETAIL 4446
APRIL 6 3 540 115 RETAIL 4446
APRIL 7 3 540 115 RETAIL 4446
APRIL 8 3 540 115 RETAIL 4446
APRIL 9 3 540 115 RETAIL 4446

The data tables are way to large for me to post. How do you suggest I help with that?

Thanks,

Kurt
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 11:06:00
Post the relevant column(Primary Key, and those columns used in your query). You don't have to post the entire table structure


KH

Go to Top of Page

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-16 : 11:10:04
@_LOAN# is the primary key.

DAT01.[_@550] AS LoanType,
DAT01.[_@051] AS Branch,
DAT01.[_@TP] AS ProdTypeDescr,
SMT_Branches.[BranchTranType] AS TranType,

_@550 is the type of loan whether it is retail or other
_@051 is the Branch number to identify the specific branch
_TP is the type of property description

BranchTranType is the Branch transaction type.

I need to get a detailed count by each day for each branch number I choose.

Hope this explanation helps clarify some things. If you need anything else please let me know.

TIA,


Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 11:15:49
Look at this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73569#263329


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 11:16:35
I believe you have 114 branches, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-16 : 11:18:00
Hello Peso,

My bad is this something I can do without?

SMT Branches is the table that contains the Branch Info.

TIA.

Thanks for looking again at this for me.

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 11:19:24
Look at the link I posted, to the original posting about this problem?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -