SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT DISTINCT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdnichols
Posting Yak Master

USA
232 Posts

Posted - 10/16/2006 :  10:48:32  Show Profile  Send kdnichols an AOL message  Reply with Quote
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)

Singapore
17593 Posts

Posted - 10/16/2006 :  10:54:31  Show Profile  Reply with Quote
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

USA
232 Posts

Posted - 10/16/2006 :  11:00:13  Show Profile  Send kdnichols an AOL message  Reply with Quote
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)

Singapore
17593 Posts

Posted - 10/16/2006 :  11:06:00  Show Profile  Reply with Quote
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

USA
232 Posts

Posted - 10/16/2006 :  11:10:04  Show Profile  Send kdnichols an AOL message  Reply with Quote
@_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

Sweden
30175 Posts

Posted - 10/16/2006 :  11:15:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30175 Posts

Posted - 10/16/2006 :  11:16:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I believe you have 114 branches, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

USA
232 Posts

Posted - 10/16/2006 :  11:18:00  Show Profile  Send kdnichols an AOL message  Reply with Quote
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

Sweden
30175 Posts

Posted - 10/16/2006 :  11:19:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Look at the link I posted, to the original posting about this problem?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000