| Author |
 Topic  |
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
 Posted - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 09:14:47
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/16/2007 : 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 - 01/16/2007 : 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
|
Edited by - Jaybee from his castle on 01/16/2007 10:20:04 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 10:26:55
|
Or simplySELECT 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 - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 10:36:40
|
Or this, if there is a chance for duplicate application value for a specific userSELECT 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 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 - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 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 - 01/16/2007 : 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 - 01/16/2007 : 11:01:34
|
| Changed 'users' to 'user', but still working on the application columnames.... |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 01/16/2007 : 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
Sweden
29138 Posts |
Posted - 01/16/2007 : 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 - 01/16/2007 : 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
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 01/16/2007 : 11:16:26
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/16/2007 : 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
Slovenia
11741 Posts |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 01/17/2007 : 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
|
|
|
|
Topic |
|
Complex code
