Complex code - 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)

Complex code

Author

Topic

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 09:05:52

Hi all,

Got two tables, one called 'Working' with columns called, Users, Application1, Application2, Application3 etc. Second table called CitrixApps with a single column, citrixapps.

What I need to do is return several recordsets:

1) All rows with 1 application in citrixapps;

2) As above, but with 2 applications in citrixapps;

3) As above, but with 3 applications in citrixapps etc.

As some of you might have guessed, this is reporting for a management decision - my colleagues may WELL ask, "How many users have more than 5, or don't have ANY applications in the CitrixApps table?"

Your help much appreciated!!!

Jaybee.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 09:14:47

[code]SELECT x.Users,
COUNT(*) AS Apps
FROM (
SELECT w.Users,
ca.Application1 AS App
FROM Working AS w
LEFT JOIN CitrixApps AS ca ON ca.CitrixApps = w.Application1

UNION ALL

SELECT w.Users,
ca.Application2
FROM Working AS w
LEFT JOIN CitrixApps AS ca ON ca.CitrixApps = w.Application2

UNION ALL

SELECT w.Users,
ca.Application3
FROM Working AS w
LEFT JOIN CitrixApps AS ca ON ca.CitrixApps = w.Application3
) AS x
GROUP BY x.Users
HAVING COUNT(*) = @ParamForAppCountHere
ORDER BY 2 DESC[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 09:49:28

Hard to do better since we don't have more information, nor sample data.
What is stored in the ApplicationX columns if data is not entered? NULL? 0?
What does ApplicationX have for datatype? VARCHAR? Or is it an ID for CitrixApps table?

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 10:18:13

Schema, sample data....

1 [User] nvarchar 255 1
0 [User Name] nvarchar 255 1
0 [Telephone NO & Extension] nvarchar 255 1
0 Location nvarchar 255 1
0 [Floor Location] nvarchar 255 1
0 [Asset Tag] nvarchar 255 1
0 [PC Make] nvarchar 255 1
0 [Model No] nvarchar 255 1
0 [Serial No#] nvarchar 255 1
0 [Applications 1] nvarchar 255 1
0 [Application 2] nvarchar 255 1
0 [Application 3] nvarchar 255 1
0 [Application 4] nvarchar 255 1
0 [Application 5] nvarchar 255 10 F15 nvarchar 255 1
0 F16 nvarchar 255 1
0 F17 nvarchar 255 1
0 F18 nvarchar 255 1
0 [Application 6] nvarchar 255 1
0 [Application 7] nvarchar 255 1
0 [Application 8] nvarchar 255 1
0 [Source Code] nvarchar 255 1
0 [Installation Documentation] nvarchar 255 1
0 [Hardware Peripherals] nvarchar 255 1
0 [Installation Discs] nvarchar 255 1
0 Comments nvarchar 255 1
0 [Availability - Good (Y/N)] nvarchar 255 1
0 [Holidays booked] nvarchar 255 1
0 Symbols nvarchar 255 1
0 F30 nvarchar 255 1
0 F31 nvarchar 255 1
0 F32 nvarchar 255 1
0 F33 nvarchar 255 1
0 F34 nvarchar 255 1
0 F35 nvarchar 255 1
0 F36 nvarchar 255 1
0 F37 nvarchar 255 1
0 F38 nvarchar 255 1
0 F39 nvarchar 255 1
0 F40 nvarchar 255 1
0 F41 nvarchar 255 1
0 F42 nvarchar 255 1

Sarah Lewis lewiss01 020 8888 8888 /7957 Civil Centre - Stockwell 1st LCS0205 COMPAQ TM 6S2AKN9Z331E RSS RON Ebusiness NULL Adobe 7 is a requirement for RON NULL NULL NULL NULL NULL NULL NULL N/K With EDS HP Laserjet 1100 (MS) / HP business inkjet 1200 Available in Central Software Library NULL No - Generally in meetings am / appointments with public pm NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:25:51

How about this one then?

SELECT		Users,
		SUM(Apps) AS Apps
FROM		(
			SELECT		Users,
					CASE
						WHEN Application1 IS NULL THEN 0
						ELSE 1
					END + 
					CASE
						WHEN Application2 IS NULL THEN 0
						ELSE 1
					END + 
					CASE
						WHEN Application3 IS NULL THEN 0
						ELSE 1
					END + 
					CASE
						WHEN Application4 IS NULL THEN 0
						ELSE 1
					END + 
					CASE
						WHEN Application5 IS NULL THEN 0
						ELSE 1
					END AS Apps
			FROM		Working
		) AS x
GROUP BY	Users
HAVING		COUNT(*) = @ParamForAppCountHere
ORDER BY	2 DESC

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:26:55

Or simply

SELECT		Users,
		CASE
			WHEN Application1 IS NULL THEN 0
			ELSE 1
		END + 
		CASE
			WHEN Application2 IS NULL THEN 0
			ELSE 1
		END + 
		CASE
			WHEN Application3 IS NULL THEN 0
			ELSE 1
		END + 
		CASE
			WHEN Application4 IS NULL THEN 0
			ELSE 1
		END + 
		CASE
			WHEN Application5 IS NULL THEN 0
			ELSE 1
		END AS Apps
FROM		Working

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 10:34:57

Getting back the following,
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application1'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application2'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application3'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application4'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application5'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:36:40

Or this, if there is a chance for duplicate application value for a specific user

SELECT		x.Users,
		COUNT(*)
FROM		(
			SELECT	Users,
				Application1 AS App
			FROM	Working
			UNION
			SELECT	Users,
				Application2
			FROM	Working
			UNION
			SELECT	Users,
				Application3
			FROM	Working
			UNION
			SELECT	Users,
				Application4
			FROM	Working
			UNION
			SELECT	Users,
				Application5
			FROM	Working
		) AS x
GROUP BY	x.Users
HAVING		COUNT(*) = @ParamForAppCountHere
ORDER BY	2 DESC

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:37:54

Just replace column name Users with User in the queries.

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 10:46:37

Latest news:

Server: Msg 137, Level 15, State 2, Line 25
Must declare the variable '@ParamForAppCountHere'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:50:14

Exactly.

If you write this query as a STORED PROCEDURE, you can reuse the code many times by just passing a parameter with a new value!

Pass 1 for getting the users with only 1 application, 0 for no application, 5 for five applications...
For now, just substitute the @ParamForAppCountHere with a real value, say 2.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:52:52

I assume you replaced the Application1 column name with [Applications 1], Application2 with [Application 2] and so on?

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 10:56:06

That makes sense, but more errors...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application1'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application2'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application3'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application4'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Users'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Application5'.

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 11:01:34

Changed 'users' to 'user', but still working on the application columnames....

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-01-16 : 11:02:45

JB, why do you think you are getting those errors?

Any more help from peso and you'll have to send him your paycheck.

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 11:04:20

Hallulujah! You are in serious trouble, if you are not able to change the column names according to your own environment.
This should work if the posted DDL above is correct.

SELECT		x.[User],
		COUNT(*)
FROM		(
			SELECT	[User],
				[Applications 1] AS App
			FROM	Working
			UNION
			SELECT	[User],
				[Application 2]
			FROM	Working
			UNION
			SELECT	[User],
				[Application 3]
			FROM	Working
			UNION
			SELECT	[User],
				[Application 4]
			FROM	Working
			UNION
			SELECT	[User],
				[Application 5]
			FROM	Working
		) AS x
GROUP BY	x.[User]
--HAVING		COUNT(*) = @ParamForAppCountHere
ORDER BY	2 DESC

Peter Larsson
Helsingborg, Sweden

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-16 : 11:08:34

quote:
Originally posted by eyechart

JB, why do you think you are getting those errors?
Any more help from peso and you'll have to send him your paycheck.
-ec

I needed a good laugh today!!! :)

Shame he's 1500 1 hours flight time away in Sweden, if he were closer I'd buy him a few pints of Skol/Carlsberg (delete as appropriate!) for his troubles....

Latest code with error message...

SELECT x.User,
COUNT(*)
FROM (
SELECT User,
[Applications 1] AS App
FROM Working
UNION
SELECT Users,
[Application 2]
FROM Working
UNION
SELECT User,
[Application 3]
FROM Working
UNION
SELECT User,
[Application 4]
FROM Working
UNION
SELECT User,
[Application 5]
FROM Working
) AS x
GROUP BY x.Users
HAVING COUNT(*) = 1
ORDER BY 2 DESC

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'User'.
Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'AS'.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-01-16 : 11:16:26

[code]
SELECT x.User,
COUNT(*)
FROM
(SELECT User,
[Applications 1] AS App
FROM Working
UNION
SELECT Users,
[Application 2]
FROM Working
UNION
SELECT User,
[Application 3]
FROM Working
UNION
SELECT User,
[Application 4]
FROM Working
UNION SELECT User, [Application 5] FROM Working ) AS x
GROUP BY x.Users
HAVING COUNT(*) = 1
ORDER BY 2 DESC
[/code]

'Users' should be 'User' right?

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 11:30:55

quote:
Originally posted by eyechart

Any more help from peso and you'll have to send him your paycheck.

Great! Now I can cancel my lunch plan for tomorrow and eat on the boat to Denmark instead.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-16 : 11:33:27

this is getting seriously ridicolous...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-17 : 04:40:52

Not so ridiculous, the code below works error-free...
I also hope to group by location.

SELECT x.[User],
COUNT(*)
FROM (
SELECT [User],
[Applications 1] AS App
FROM Working
UNION
SELECT [User],
[Application 2]
FROM Working
UNION
SELECT [User],
[Application 3]
FROM Working
UNION
SELECT [User],
[Application 4]
FROM Working
UNION
SELECT [User],
[Application 5]
FROM Working
) AS x
GROUP BY x.[User]
--HAVING COUNT(*) = @ParamForAppCountHere
ORDER BY 2 DESC

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources