where condition a special case

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 2008 Forums

Transact-SQL (2008)

where condition a special case

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-08-12 : 16:57:24

i have a table customer

customer_no, begintime,end time
1,0,0
2,0,0
3,8,9
4,0,0
5,7,8,
6,0,0
7,9,11

Second table agents
agent_ID,starttime
X,10
y,12
z,7,
k,10

What i want to basically do is outer join the tow tables for a reason

When I do i want to leave out the records in Customer table that are not zero for agents whose start time do not lie between the begintime and endtime of customer but if the starttime of the agent is between the begintime and endtime of customer then it shud be considered for join.

The 0 begin time and end time are shud be always considered.

resultset example i wnat to see after joining
1,0,0 ,X,10
2,0,0,X,10
4,0,0,X,10
6,0,0,X,10
7,9,11,X,10

and so on for other agents as well....

Hope i am clear

Nikhil1home
Starting Member

23 Posts

Posted - 2011-08-12 : 19:20:20

Sorry to be blunt here but this seems like a poor database design to me.

You don't need an our join here. You need two queries and combine their results with a union. What is the purpose of the query? It seems wierd.

First query will cross join all agents with customers who have 0s in begin and end fields. The second query will perform an inner join between agents and the customers who have non-zero value in begin or end field. You need to combine these two queries with a union.

SELECT customer_no, begintime,endtime, agent_ID,starttime
FROM customer
CROSS JOIN agents
WHERE customer.begintime = 0 AND customer.endtime = 0
UNION
SELECT customer_no, begintime,endtime, agent_ID,starttime
FROM customer
JOIN agents
ON agents.starttime BETWEEN customer.begintime AND customer.endtime
WHERE customer.endtime > 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-13 : 13:35:28

/*
customer_no, begintime,end time
1,0,0
2,0,0
3,8,9
4,0,0
5,7,8,
6,0,0
7,9,11

Second table agents
agent_ID,starttime
X,10
y,12
z,7,
k,10

1,0,0 ,X,10
2,0,0,X,10
4,0,0,X,10
6,0,0,X,10
7,9,11,X,10

*/
DECLARE @Table1 TABLE (CustNo int,begintime int,endtime int)
DECLARE @Table2 TABLE (agent char(1),startTime int)

INSERT INTO @Table1
SELECT 1,0,0 UNION ALL
SELECT 2,0,0 UNION ALL
SELECT 3,8,9 UNION ALL
SELECT 4,0,0 UNION ALL
SELECT 5,7,8 UNION ALL
SELECT 6,0,0 UNION ALL
SELECT 7,9,11

INSERT INTO @Table2
SELECT 'X',10 UNION ALL
SELECT 'y',12 UNION ALL
SELECT 'z',7 UNION ALL
SELECT 'k',10

SELECT t1.CustNo,t1.BeginTime,t1.EndTime,t2.Agent,t2.StartTime
FROM @table1 t1 CROSS JOIN @table2 t2
WHERE (t1.BeginTime = 0 and t1.EndTime = 0)
OR (t2.StartTime between t1.BeginTime and t1.EndTime)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources