SQL Server Performance Forum – Threads Archive

Complex code

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.
France. The finest wines. The tastiest cuisine. The most delicious women.

You have a serious design problem! How are your tables related and can you post some sample data? The query itself shouldn’t be too difficult, however, I’m not sure I understand you correctly. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

The basic syntax – slightly stretching the JOIN expression: SELECT W.User, COUNT(*)
FROM Working W
INNER JOIN CitrixApps C
ON C.App IN (W.Application1, W.Application2, W.Application3, W.Application4,
W.Application5, W.Application6, W.Application7, W.Application8, W.Application9)
GROUP BY W.User
HAVING COUNT(*) = xxxxxxxx

quote:Originally posted by FrankKalis You have a serious design problem! How are your tables related and can you post some sample data? The query itself shouldn’t be too difficult, however, I’m not sure I understand you correctly. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Hi Frank, Adriaan may have come quite close (though his script errors out on syntax!), but I’ll restate what I’m looking for – all the above, ie which users have 1 application in common with citrixapps.citrixapps, which users have 2 applications etc. THEN – I’ll need them grouped by location. Tables.columns: Working.user
Working.location
working.applications 1 (note the plural ‘s’!!)
working.application 2
working.application 3
working.application 4
working.application 5 citrixapps.citrixapps Bear in mind there are plenty of nulls in the table. Sample data: Sarah Lewis lewiss01 020 8888 8888 /7957 Civil Center – 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 I think i should emphasise something, "Applications 1" is NOT a column of users who have one application in common with citrixapps – if a users row has 5 applications listed, they will each be in Application 1, etc Application 2 etc as above. Cheers, France. The finest wines. The tastiest cuisine. The most delicious women.

Now I’m more confused than before. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Anyway, if I understood correctly, this should be it: <pre id="code"> CREATE TABLE citrixapps ( citrixapps VARCHAR(100) ) CREATE TABLE Working ( [user] VARCHAR(10) , location VARCHAR(10) , applications1 VARCHAR(100) , applications2 VARCHAR(100) , applications3 VARCHAR(100) , applications4 VARCHAR(100) , applications5 VARCHAR(100) ) INSERT INTO citrixapps SELECT ‘MS Word’ UNION ALL SELECT ‘MS Excel’ UNION ALL SELECT ‘MS Access' INSERT INTO working SELECT ‘me’, ‘here’, ‘MS Word’, ‘MS Excel’, ‘MS Access’, NULL, NULL UNION ALL SELECT ‘me’, ‘nowhere’, NULL, ‘MS Word’, NULL, NULL , NULL –UNION ALL SELECT ‘he’, ‘nowhere’, NULL, NULL, NULL, NULL, NULL –UNION ALL SELECT ‘he’, ‘nowhere’, NULL, ‘MS Word’, NULL, NULL, NULL SELECT MIN(t1.[user]), t1.location, COUNT(*) AS appcounter FROM working t1 JOIN citrixapps c1 ON (t1.applications1 = c1.citrixapps) OR (t1.applications2 = c1.citrixapps) OR (t1.applications3 = c1.citrixapps) OR (t1.applications4 = c1.citrixapps) OR (t1.applications5 = c1.citrixapps) GROUP BY t1.location –HAVING COUNT(*) = –adjust this to your needs DROP TABLE citrixapps, working location appcounter ———- ———- ———– me here 3 me nowhere 1 (2 row(s) affected) </pre id="code"> If that doesn’t go your direction, please provide the information requested here:<a target="_blank" href=http://www.aspfaq.com/etiquette.asp?id=5006>http://www.aspfaq.com/etiquette.asp?id=5006</a> It should save time and probably provide a "better" answer. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Heh heh mate! Ok, will keep simple, though I’m under tremendous pressure here!! (You have to smile, or you go nuts sometimes….) I have two tables: Table 1 = "working" Columns are, Working.user
Working.location
working.applications 1 (note the plural ‘s’!!)
working.application 2
working.application 3
working.application 4
working.application 5 Scripted as follows: CREATE TABLE [working] (
[User] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[User Name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Telephone NO & Extension] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Location] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Floor Location] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Asset Tag] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[PC Make] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Model No] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Serial No#] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Applications 1] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 3] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 4] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 5] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F15] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F16] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F17] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F18] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 6] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 7] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Application 8] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Source Code] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Installation Documentation] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Hardware Peripherals] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Installation Discs] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Comments] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Availability – Good (Y/N)] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Holidays booked] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Symbols] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F30] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F31] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F32] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F33] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F34] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F35] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F36] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F37] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F38] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F39] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F40] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F41] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[F42] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Table 2 = "Citrixapps.Citrixapps" CREATE TABLE [CitrixApps] (
[citrixapps] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SAMPLE DATA (CSV’ed) Sarah Lewis, lewiss01, 020 8888 8888/7957, Civil Center – Stockwell, 1st, LCS0205, COMPAQ, TM, 6S2AKN9Z331E, RSS, RON, Ebusiness, NULL, Adobe 7 As you see, this user has 5 applications (sometimes shortened to ‘apps’ in English) – and perhaps one of these five applications is also in Citrixapps.Citrixapps. Perhaps 2, perhaps 3 etc… DESIRED RESULT: What I want is a list of which users have X number of apps in common with Citrixapps.Citrixapps, ie User Citrix Applications
………………………………. Ronald Reagan 25
Bill Clinton 25
Angela Merkel 22
George Foreman 18
Bill Duke 15
Sarah Lewis 1 As you see, the above users are ‘grouped’ by numbers of common applications; Sarah Lewis has one application, ‘Ebusiness’, and the same again for locations: Location Citrix Applications
……………………………….
Hannover 25
Detmold 25
Aachen 25
Munich 22
Bielefeld 18
Dortmund 15
I hope the above is clear, I do need this report quite urgently!!! Vielen Danke!!! Jaybee.
France. The finest wines. The tastiest cuisine. The most delicious women.

Hm, isn’t it this, what you want? Grouped by users
SELECT t1.[user], t1.location, COUNT(*) AS appcounter
FROM working t1
JOIN citrixapps c1
ON (t1.[applications 1] = c1.citrixapps)
OR (t1.[application 2] = c1.citrixapps)
OR (t1.[application 3] = c1.citrixapps)
OR (t1.[application 4] = c1.citrixapps)
OR (t1.[application 5] = c1.citrixapps)
GROUP BY t1.location, t1.[user]
–HAVING COUNT(*) = 1 –here goes your number Grouped by location
SELECT t1.location, COUNT(*) AS appcounter
FROM working t1
JOIN citrixapps c1
ON (t1.[applications 1] = c1.citrixapps)
OR (t1.[application 2] = c1.citrixapps)
OR (t1.[application 3] = c1.citrixapps)
OR (t1.[application 4] = c1.citrixapps)
OR (t1.[application 5] = c1.citrixapps)
GROUP BY t1.location
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
https://www.xing.com/profile/Frank_Kalis

Frank, Vielen dank fuer den code, es wird naechste woche uberprueft, denn gibt es heute mit dankbarweise kein mehr dringlichkeit dabei!! Jaybee. France. The finest wines. The tastiest cuisine. The most delicious women.

]]>