SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Complex code
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  09:05:52  Show Profile  Reply with Quote
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

Sweden
29908 Posts

Posted - 01/16/2007 :  09:14:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 01/16/2007 09:17:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  09:49:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  10:18:13  Show Profile  Reply with Quote
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 1
0 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

Edited by - Jaybee from his castle on 01/16/2007 10:20:04
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:25:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:26:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  10:34:57  Show Profile  Reply with Quote
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'.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:36:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:37:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Just replace column name Users with User in the queries.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  10:46:37  Show Profile  Reply with Quote
Latest news:

Server: Msg 137, Level 15, State 2, Line 25
Must declare the variable '@ParamForAppCountHere'.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:50:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  10:52:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I assume you replaced the Application1 column name with [Applications 1], Application2 with [Application 2] and so on?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  10:56:06  Show Profile  Reply with Quote
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'.
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  11:01:34  Show Profile  Reply with Quote
Changed 'users' to 'user', but still working on the application columnames....
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 01/16/2007 :  11:02:45  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  11:04:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/16/2007 :  11:08:34  Show Profile  Reply with Quote
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'.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 01/16/2007 :  11:16:26  Show Profile  Reply with Quote

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


'Users' should be 'User' right?


-ec
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/16/2007 :  11:30:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/16/2007 :  11:33:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
this is getting seriously ridicolous...




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 01/17/2007 :  04:40:52  Show Profile  Reply with Quote
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


Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000