More MAX fun - 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 2008 Forums

Transact-SQL (2008)

More MAX fun

Author

Topic

Dobly
Starting Member

16 Posts

Posted - 2011-02-18 : 00:57:01

After my problem yesterday [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156649[/url] I hoped all was solved and I could get on with it. Alas I have a new problem that takes this MAX thing to a whole new level

In this problem I have 3 tables.

Locations
loc_Location_ID PK

Prospects
pro_Prospect_ID PK
pro_Location_ID FK

ItemValue
iv_ItemValue_ID PK
iv_Prospect_ID FK
iv_Actioned (bit)

My query returns a list of locations.

In each row I need to know if this Location, has any Prospects, that have an ItemValue.iv_Actioned value of False.

Bear in mind that a Location might have 1 to many Prospects, and any one of those Prospects might have a ItemValue that is Actioned = false.

It does not matter to me which Prospect has the false Actioned ItemValue. I just need to know if this Location's Prospects have any iv_Actioned values of False in which case I'd like to return False. Else I can return True. That was now if this Location as any Items left to action. If that makes sense.

This one I've been on all day but at 3.55PM Friday afternoon (Brisbane, Aust) my head has had enough.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-18 : 01:37:21

it will be easier for us to help you if you can provide some sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

Dobly
Starting Member

16 Posts

Posted - 2011-02-20 : 18:53:51

Ok, here goes..


Location
loc_Location_ID (PK)
4

Prospects
pro_Prospect_ID (PK)  |   pro_Location_ID (FK)
1                         4
2                         4
3                         4                                     



ItemValue
iv_ItemValue_ID   | iv_Prospect_ID  |  Task              | iv_Actioned
1                   1                  'Create Invoice'    true
2                   1                  'Delivery'          false
3                   1                  'Phone'             false
4                   2                  'Create Invoice'    true
5                   2                  'Delivery'          true
6                   2                  'Email'             true
7                   3                  'Delivery'          true
8                   3                  'Training'          false

Just to recap. I need a list of Locations.

I need to know if any of the listed Location's Prospects have ANY iv_Actioned set to False. If any one of them is false I need to return false, but if they are all true, I need to return true.

This query will tell me if any Locations has any outstanding items left to Action in it's Prospects.

A result set might look something like...



Location_ID | Title   | TotallyActioned
4             'Bobs'    false
5             'ABC'     true
6             'XYZ'     false
7             'Jans'    true

Alternatively I could return False if there is a False Actioned value, or return NULL if not. That would do the trick too if that makes it any easier.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-20 : 20:22:44

[code]
select l.Loc_Location_ID, case when a.Actioned = 0 then 'false' else 'true' end
from Location l
cross apply
(
select Actioned = min(iv.iv_Actioned)
from Prospects p
inner join ItemValue iv on p.pro_Prospect_ID = iv.iv_Prospect_ID
where p.pro_Location_ID = l.Loc_Location_ID
) a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Dobly
Starting Member

16 Posts

Posted - 2011-02-20 : 20:37:33

khtan

That's it!

Thanks so much.

I know now I would never have worked this out as I have never see the 'cross apply' stuff before. Off to learn about this now.

Just one thing I had to tweak was this.

select	Actioned = min(tpv.tpv_Actioned)

Can't do MIN on a bit field. Had to cast it.

select	Actioned = min(CAST(tpv.tpv_Actioned AS INT))

But that's just nit-picking.

Thanks again dude.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-20 : 20:59:11

oh didn't noticed that the column is BIT.

you can also try this


select	*,
	Actioned	= case 	when 	exists
					(
						select	*
						from	Prospects p
							inner join ItemValue iv	on	p.pro_Prospect_ID	= iv.iv_Prospect_ID
						where	p.pro_Location_ID	= l.Loc_Location_ID
						and	iv.iv_Actioned		= 0
					)
				then	'false'
				else	'true'
				end
from	Location l

KH
[spoiler]Time is always against us[/spoiler]

Dobly
Starting Member

16 Posts

Posted - 2011-02-21 : 00:40:13

Nice.. Still does not mean I could have worked it out. :)

You're awesome as this stuff khtan and I'm very thankful.

This one is more understandable to me. But the first one did the job fine and its already installed in the View that the query belong too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources