query help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

query help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 18:18:57

I have 2 tables that I'm trying to query, one that holds basic cust. info (name, address...) and the other transactions (one to many). I need to write a query where I search on a workdate field from the transactions table when the value of the workdate = today or a previous date. My problem is, I only want one row per result, regardless of how many transactions I have. This is what I have so far:

SELECT
add1, add2 ....,convert(varchar, t.NextWorkDate,10) as NextWorkDate
FROM
CustInfo
LEFT JOIN Transactions AS t
ON t.CustInfoID=CustInfo.custinfoID
AND t.NextWorkDate = (
SELECT
MIN(innerT.NextWorkDate)
FROM
Transactions AS innerT
WHERE 1=1
AND innerT.CustInfoID=CustInfo.CustInfoID
)
WHERE 1=1

AND (custinfo.createdby = @EmployeeName OR assignedto= @EmployeeName)

ORDER BY
CASE WHEN NextWorkDate <= getdate() THEN t.NextWorkDate ELSE NULL END DESC

The problem with this query is that if the cust has more than 1 transaction that has t.NextWorkDate (say for ex 2), the query returns back 2 rows. I only want it once. Someone helped me with the above query and it seemed to work until I ran into this issue. Can anyone help? Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-24 : 19:11:00

To get 1 transaction, you need something to uniquely identify the transaction.
What is the Primary Key for the transaction?

rockmoose

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 19:16:26

The primary for the transaction table is transactionID

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 19:25:39

Actually, what I need is one instance of the cust info

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-24 : 19:35:56

Can you post some sample data, and expected result?
It will be easier to see exactly what you are after.

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rockmoose

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 20:32:31

Since it's confidential, I cannot recreat tables, but here is basics:
Requirement: Fill datagrid with custname, address, status - order by: If a cust has a NextWorkDate that = today or a previous day, custname asc
CustInfo (table):
*CustID (INT), CustName, Address, Status
Data for custInfo:
1, Mickey Mouse, 1 some st, Active
2, Donald Duck, 35 Park ave, New
3, Minnie Mouse, 3 Anywhere, Active
Transactions (table):
*TransID(Int),CustID (Int), NextWorkDate (datetime)
Data for Transactions:
1,3,9/25/2006
1,3,6/23/2006
1,3,6/15/2006
1,2,NULL
1,2,12/15/2006
Results:
Minnie Mouse, 3 Anywhere, Active
Donald Duck, 35 Park ave, New
Mickey Mouse, 1 some st, Active

So the results are a single instance of customers. The problem is in the NextWorkDate. I hope this is clearer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-24 : 20:58:06

[code]
select
CustName = max(c.CustName),
Address = max(c.Address),
Status = max(c.Status)
from
CustInfo c
join
Transactions t
on c.CustID = t.CustID
where
t.NextWorkDate = @SelectionDate
group by
c.CustID
order by
max(c.CustName) asc
[/code]

CODO ERGO SUM

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 21:17:33

Michael - that does not work. Let me add more data:
Requirement: Fill datagrid with custname, address, status - order by: If a cust has a NextWorkDate that = today or a previous day, custname asc
CustInfo (table):
*CustID (INT), CustName, Address, Status
Data for custInfo:
1, Mickey Mouse, 1 some st, Active
2, Donald Duck, 35 Park ave, New
3, Minnie Mouse, 3 Anywhere, Active
4, Donald Duck, 35 Park ave, New
Transactions (table):
*TransID(Int),CustID (Int), NextWorkDate (datetime)
Data for Transactions:
1,3,9/25/2006
1,3,6/23/2006
1,3,6/15/2006
1,2,NULL
1,2,12/15/2006
Results:
Minnie Mouse, 3 Anywhere, Active
Donald Duck, 35 Park ave, New
Donald Duck, 35 Park ave, New
Mickey Mouse, 1 some st, Active

So if a cust is in there more than once, they should be listed more than once. If a cust's transactions NextWorkDate is <= todaysDate more than once, that cust. should only be listed once.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-24 : 21:31:54

Did you try what I posted? That query will not list a customer more than once.

YOu may want to go through one of these:
http://www.w3schools.com/sql/default.asp
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm

CODO ERGO SUM

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 21:45:59

Yes I tried it - but your query will also elminitate the second Donald Duck because he has status of NEW in both rows.

ann
Posting Yak Master

220 Posts

Posted - 2006-06-24 : 21:47:07

The only thing I want to "Distinct" on really, is if the customer has more than one instance of the NextWorkDate

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-24 : 22:01:53

Why you want to return duplicate rows?

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-25 : 06:22:39

"Data for custInfo:
1, Mickey Mouse, 1 some st, Active
2, Donald Duck, 35 Park ave, New
3, Minnie Mouse, 3 Anywhere, Active
4, Donald Duck, 35 Park ave, New"

I don't understand why Donal Duck is listed twice (CustID = 2, CustID = 4) ?
The same customer?, or different Donald Ducks?

Also, the same Q as MVJ, why want to list duplicate Data?

rockmoose

ann
Posting Yak Master

220 Posts

Posted - 2006-06-25 : 07:37:37

To try and make long story short - my understanding as to the reason a cust can be listed more than once: The data is collected from diff. sources and imported into this db at diff times. When it was initially developed, there was only one source, diff. programs were written against it by diff vendors. Then as time went on, additional sources of data were imported - the cost to change the db and the programming changes would have been expensive - so a decision was made to leave it as is. Yes it should be designed differently, but no one is going to pay for these changes, since the life of each cust data is finite (it gets deleted after a few months, it did not seem like a cost effective change - my job is to write a new program for this data based on this db, so that is what I am stuck with. The programming I'm doing is being written from scratch, the db is pre-my involvement.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-25 : 11:50:12

So why do you want to return duplicate rows?

CODO ERGO SUM

ann
Posting Yak Master

220 Posts

Posted - 2006-06-25 : 12:41:46

They are not duplicate rows - they have unique ID's that are associated with unique transactions

ann
Posting Yak Master

220 Posts

Posted - 2006-06-25 : 12:45:14

I guess I am not explaining this correctly, let me try like this: CustA (ID2) has transactions , CustA (ID4) has a different set of transactions associated with that record (ID4)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-25 : 12:56:32

So you are talking about different CistID's?

The query I posted will return one row for each CustID.

CODO ERGO SUM

ann
Posting Yak Master

220 Posts

Posted - 2006-06-25 : 13:04:16

I tried your query and it eliminated the second DD - I will try it again, it was very late last night for me, so I might have done something wrong.
And yes, I am talking about diff. CustID's

ann
Posting Yak Master

220 Posts

Posted - 2006-06-25 : 13:12:00

The query needs to have something like this included because of the NextWorkDate OrderBy:

select
CustName = max(c.CustName ),
Address = max(c.Address),
Status = max(c.Status),
t.NextWorkDate = max(t.NextWorkDate)
from
CustInfo c
join
Transactions t
on c.CustInfoID = t.CustInfoID

group by
c.FraudCaseID
ORDER BY
CASE WHEN NextWorkDate <= getdate() THEN t.NextWorkDate ELSE NULL END DESC,
max(c.FullName) asc

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-25 : 13:54:09

If you can make the inner table unique on CustInfoID, then each record from the CustInfo table will be shown only once.
Maybe in this direction ?

SELECT	...
FROM	CustInfo
	LEFT JOIN
	(
		SELECT	CustInfoID
			,MIN(NextWorkDate) AS WD
		FROM	Transactions
		WHERE	NextWorkDate = <some criteria for workdateselection>
		GROUP BY
			CustInfoID
	) AS CustWorkTransactions
		ON CustInfo.CustInfoID = CustWorkTransactions.CustInfoID
ORDER BY
	CASE WHEN WD <= GETDATE() THEN WD ELSE NULL END DESC,
	c.FullName ASC

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources