Query problem - 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 problem

Author

Topic

daisy
Starting Member

13 Posts

Posted - 2006-06-22 : 10:03:40

My problem occurs with my query when I need to split the same job instead of splitting between different jobs.

My results:
-----------------------------------------------------------------------------------------
| vsd_id | cl_id | slt_yn | job_no | cl_city | timestamp |
-----------------------------------------------------------------------------------------
| 140 | 181 | null | 6386 | HOUSTON | null |
| 141 | 181 | null | 6386 | HOUSTON | null |
| 140 | 88 | null | 6386 | SAN DIEGO | null |
| 141 | 88 | null | 6386 | SAN DIEGO | null |
-----------------------------------------------------------------------------------------

I need:
-----------------------------------------------------------------------------------------
| 140 | 181 | null | 6386 | HOUSTON | null |
| 140 | 88 | null | 6386 | SAN DIEGO | null |
-----------------------------------------------------------------------------------------
select

vsd.vsd_id,
c.cl_id
,slt_yn
,CONVERT(VARCHAR(50),job_no) as job_no
,case when cl_city is null then cl_name
when rtrim(cl_city) = '' then cl_name
else cl_city+' ('+cl_name+')'
end
,vm.time_stamp

from

member_order MO
join client C on (C.cl_id = MO.cl_id)
join sale_breakout SB on (SB.job_id = MO.job_id and SB.cl_id = c.cl_id)
join job j on (j.job_no = sb.job_id)
join job_version jv on (jv.ver_id = mo.ver_id and jv.job_id = j.job_id)
join vs_detail vsd on (vsd.ver_id = jv.ver_id)
join version_split vs on (vs.vsd_id = vsd.vsd_id )
left join vs_member vm on (vm.cl_id =c.cl_id and vm.vsd_id = vsd.vsd_id)

where

MO.active_yn = '1' and vsd.vs_id = 72

---------
vs_member
---------
vsd_id
cl_id
slt_yn
time_stamp

---------
vs_detail
---------
vsd_id
vs_id
ver_id
perct
vsd_name
over_perc
...
-------------
version_split
-------------
vs_id
vs_name
...
~~~~~~~~~~~~~~~~~~~
I've tried distinct. Not sure what to do.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-22 : 11:36:37

see if this does it.


select 
	min(vsd.vsd_id) as vsd_id,c.cl_id,slt_yn
	,CONVERT(VARCHAR(50),job_no) as job_no
	,case when cl_city is null then cl_name
	when rtrim(cl_city) = '' then cl_name
	else cl_city+' ('+cl_name+')' 
	end 
	,vm.time_stamp
from
	member_order MO 
join client C on (C.cl_id = MO.cl_id)
join sale_breakout SB on (SB.job_id = MO.job_id and SB.cl_id = c.cl_id)
join job j on (j.job_no = sb.job_id)
join job_version jv on (jv.ver_id = mo.ver_id and jv.job_id = j.job_id)
join vs_detail vsd on (vsd.ver_id = jv.ver_id)
join version_split vs on (vs.vsd_id = vsd.vsd_id )
left join vs_member vm on (vm.cl_id =c.cl_id and vm.vsd_id = vsd.vsd_id)

where
	MO.active_yn = '1' and vsd.vs_id = 72 
group by 
	c.cl_id,slt_yn
	,CONVERT(VARCHAR(50),job_no) 
	,case when cl_city is null then cl_name
	when rtrim(cl_city) = '' then cl_name
	else cl_city+' ('+cl_name+')' 
	end 
	,vm.time_stamp

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

daisy
Starting Member

13 Posts

Posted - 2006-06-22 : 12:42:19

Thank you DonAtWork. :)

I swear that I tried min & max - obviously I must have been including vsd_id in
the group by.

daisy
Starting Member

13 Posts

Posted - 2006-06-22 : 15:52:28

After saving and running the query again my results are pretty much were I had started.
Any ideas?

| vsd_id | cl_id | slt_yn | job_no | cl_city | timestamp |
-----------------------------------------------------------------------------------------
| 140 | 181 | 1 | 6386 | HOUSTON | 0x00000000017DE924|
| 141 | 181 | null | 6386 | HOUSTON | null |
| 140 | 88 | 1 | 6386 | SAN DIEGO | 0x00000000017DE925|
| 141 | 88 | null | 6386 | SAN DIEGO | null |
-----------------------------------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources