| Author |
 Topic  |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 01/17/2007 : 04:52:58
|
SELECT x.[User],
x.Location,
COUNT(*) AS Apps
FROM (
SELECT [User],
Location,
[Applications 1] AS App
FROM Working
UNION
SELECT [User],
Location,
[Application 2]
FROM Working
UNION
SELECT [User],
Location,
[Application 3]
FROM Working
UNION
SELECT [User],
Location,
[Application 4]
FROM Working
UNION
SELECT [User],
Location,
[Application 5]
FROM Working
) AS x
GROUP BY x.[User],
x.Location
ORDER BY 3 DESC
Peter Larsson
Helsingborg, Sweden |
 |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 01/17/2007 : 06:28:42
|
Peso - I just realised something - the latest scripts don't reference the CitrixApps table!!!
Do you remember I wanted to group what users had 1 application common to citrixapps.citrixapps, then 2 applications, then 3 etc (and ideally their location) ??
Cheers,
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/17/2007 : 06:39:20
|
Here it is anyway, since I figured out you never will be able to solve this by yourself.
First, create a VIEW with thisCREATE VIEW dbo.vwSomeData
AS
SELECT x.[User],
x.Location,
COUNT(*) AS Apps
FROM (
SELECT [User],
Location,
[Applications 1] AS App
FROM Working
INNER JOIN CitrixApps ON CitrixApps.CitrixApps = Working.[Applications 1]
UNION
SELECT [User],
Location,
[Application 2]
FROM Working
INNER JOIN CitrixApps ON CitrixApps.CitrixApps = Working.[Application 2]
UNION
SELECT [User],
Location,
[Application 3]
FROM Working
INNER JOIN CitrixApps ON CitrixApps.CitrixApps = Working.[Application 3]
UNION
SELECT [User],
Location,
[Application 4]
FROM Working
INNER JOIN CitrixApps ON CitrixApps.CitrixApps = Working.[Application 4]
UNION
SELECT [User],
Location,
[Application 5]
FROM Working
INNER JOIN CitrixApps ON CitrixApps.CitrixApps = Working.[Application 5]
) AS x
GROUP BY x.[User],
x.Location And then all you have to do is to writeSELECT [User],
Location
FROM dbo.vwSomeData
WHERE Apps = 1 -- Or 2, Or 0, or 5...
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 01/17/2007 07:51:27 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/17/2007 : 06:40:57
|
If you are not satisfied with this, you will have to post some sample data and your expected output based on your provided sample data!
Peter Larsson
Helsingborg, Sweden |
|
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 01/17/2007 : 07:47:10
|
I think i should have said, "Applications 1" is NOT a column of users who have one application in common - if a users row has 5 applications listed, they will each be in Application 1, etc Application 2 etc.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/17/2007 : 07:52:56
|
???
Peter Larsson
Helsingborg, Sweden |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 01/17/2007 : 12:00:41
|
quote:
Originally posted by Jaybee from his castle
I think i should have said, "Applications 1" is NOT a column of users who have one application in common - if a users row has 5 applications listed, they will each be in Application 1, etc Application 2 etc.
seriously, you need to start thinking on your own. I think this thread is done.
-ec |
|
|
|
Topic |
|
|
|
Complex code
