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 CHECSELECT 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 TotalsFROM 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 |
|
|
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 4446APRIL 2 3 540 115 RETAIL 4446APRIL 3 3 540 115 RETAIL 4446APRIL 4 3 540 115 RETAIL 4446APRIL 5 3 540 115 RETAIL 4446APRIL 6 3 540 115 RETAIL 4446APRIL 7 3 540 115 RETAIL 4446APRIL 8 3 540 115 RETAIL 4446APRIL 9 3 540 115 RETAIL 4446The data tables are way to large for me to post. How do you suggest I help with that?Thanks,Kurt |
|
|
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 |
|
|
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 descriptionBranchTranType 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 11:16:35
|
I believe you have 114 branches, right?Peter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
|