Dumb Question - 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 2005 Forums

Transact-SQL (2005)

Dumb Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-11 : 15:31:39

Hello,

I'm having a couple of issues. If someone could explain what I'm doign wrong in these 2 scenarios that would help me out greatly

My main issues are

Multiple Subqueries with "OR" statements.. I'm think I have all the data correct except the syntax.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 15:54:32

Second query is missing a lot of ending paranthesises.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 08:34:45

Why did you remove your queries?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-12 : 08:35:11

quote:
Originally posted by ZMike

Hello,

I'm having a couple of issues. If someone could explain what I'm doign wrong in these 2 scenarios that would help me out greatly

My main issues are

Multiple Subqueries with "OR" statements.. I'm think I have all the data correct except the syntax.

Why did you edit the thread?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 08:50:16

where's the query?

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-12 : 10:45:22

quote:
Originally posted by visakh16

where's the query?

Sorry I took it down to edit what I had up. I really didnt need the whole thing up.

SUM(DISTINCT(CASE
WHEN dbo.SERVICE.CODE ='SERVICEA' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEB' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEC' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICED' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEE' THEN 1
ELSE 0 END ))AS PACKAGE1

FROM dbo.SERVICE.CODE
WHERE dbo.SERVICE.CODE IN ('SERVICEA','SERVICEB','SERVICEC','SERVICED','SERVICEE')
HAVING PACKAGE1=5

I'm guessing I have to lable package 1 in a new way. I had the original query up but wanted to edit it so I didnt have the live data up on the board.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:17:17

SUM(DISTINCT(...

is equal to
MAX(...

and faster too!

E 12°55'05.25"
N 56°04'39.16"

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-12 : 12:11:53

quote:
Originally posted by Peso

SUM(DISTINCT(...

is equal to
MAX(...

and faster too!

Peso,

Could you type up there what you mean. Sorry I'm a bit new to the syntax on SQL Server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 12:45:25

i think what you're looking for is this

...
FROM dbo.SERVICE.CODE
WHERE dbo.SERVICE.CODE IN ('SERVICEA','SERVICEB','SERVICEC','SERVICED','SERVICEE')
HAVING COUNT(DISTINCT dbo.SERVICE.CODE)=5

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-12 : 13:00:29

visakh16 I think that's what I'm looking for... My only question is how do I distingquish multiple packages. I have that same Case statement using the same field. (ServiceA , B, C, D, E) = 5 and (ServiceF, G, H, I, J) =0

How do I made this work with the same field multiple times to = different packagages say Package1=5 but Package2 =3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:11:27

quote:
Originally posted by ZMike

visakh16 I think that's what I'm looking for... My only question is how do I distingquish multiple packages. I have that same Case statement using the same field. (ServiceA , B, C, D, E) = 5 and (ServiceF, G, H, I, J) =0

How do I made this work with the same field multiple times to = different packagages say Package1=5 but Package2 =3

multiple packages? by packages do you mean groups? can you illustrate with some data sample please what you exactly want?

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-12 : 14:56:57

SUM(DISTINCT(CASE
WHEN dbo.SERVICE.CODE ='SERVICEA' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEB' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEC' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICED' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEE' THEN 1
ELSE 0 END ))AS PACKAGE1

SUM(DISTINCT(CASE
WHEN dbo.SERVICE.CODE ='SERVICEZ' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEY' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEX' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEW' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEV' THEN 1
ELSE 0 END ))AS PACKAGE2

FROM dbo.SERVICE.CODE
WHERE dbo.SERVICE.CODE IN ('SERVICEA','SERVICEB','SERVICEC','SERVICED','SERVICEE') OR
('SERVICEZ','SERVICEBY,'SERVICEX','SERVICEW','SERVICEV')
HAVING PACKAGE1=5 AND PACKAGE2=3

So I'm using the same field (Service Code) to make differnt packages that we have for our company.

I was going to do a sub query and do NOT IN (
But if I can count certain service codes and lable them into packages. I should be able to get my data back a little quicker and with the multiple packages that I have to retried this would save me a whole lot of time since I can just change the # of Service codes. Does that make sense ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 15:58:07

[code]SUM(DISTINCT(CASE
WHEN dbo.SERVICE.CODE ='SERVICEA' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEB' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEC' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICED' THEN 1
WHEN dbo.SERVICE.CODE ='SERVICEE' THEN 1
ELSE 0 END ))AS PACKAGE1[/code]equals to[code]MAX( CASE
WHEN dbo.SERVICE.CODE LIKE 'SERVICE[ABCDE]' THEN 1
ELSE 0
END) AS PACKAGE1[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources