January 16, 2007 at 6:27 am
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 - they 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.
January 16, 2007 at 6:39 am
really difficult without the schema of the tables...can we assume that the table CitrixApps, with only a single column, is source for the values in the Working table's Application1/2/3... columns?
if that is true, what value is in Application1 ? if it is the name of the CitrixApp, why is their a CitrixApp table?
please post the schema and a sample of the data, and I think we can get something rolling.
here's a WAG till then:
SELECT USER, CASE WHEN ISNULL(APPLICATION1,0) ELSE 1 END +
CASE WHEN ISNULL(APPLICATION2,0) ELSE 1 END +
CASE WHEN ISNULL(APPLICATION3,0) ELSE 1 END +
CASE WHEN ISNULL(APPLICATION4,0) ELSE 1 END +
CASE WHEN ISNULL(APPLICATION5,0) ELSE 1 END AS APPLICATION COUNT FROM WORKING
Lowell
January 16, 2007 at 7:28 am
Typical bad design that we meet so often... making one row per user with lots of columns holding the same information. If this is something you can influence, you should normalize the table Working to have only 2 columns - User and Application (well, and possibly some unique ID column or columns with other info not mentioned above). Then you can do a simple COUNT on it to find out the number of applications for each user.
If you simply have to live with this design, you'll be stuck with CASE statements (with as many CASEs as there are Application columns. I think Lowell mistyped the SQL a bit, it should be
SELECT USER,
CASE WHEN ISNULL(APPLICATION1,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION2,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION3,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION4,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION5,0)=0 THEN 0 ELSE 1 END AS APPLICATION COUNT FROM WORKING
It will be even more complicated if there can be other than Citrix applications in these columns, or if these columns contain some unlinked values.
January 16, 2007 at 7:30 am
thanks for the correction Vladan; i missed that one since i just winged it and didn't test it in QA. Maybe he can redesign the schema and fix the issue.
Lowell
January 16, 2007 at 7:50 am
Hi chaps, got some problems with the code...
Those scripts keep erroring out on syntax , I'm not even sure from how they're worded how they're supposed to accomplish what I want, or whether the entire script is meant to be run as one!!
Forgive my ignorance, I'm not a dev guy - and I agree the schema is tripe...imported from an Excel spreadsheet and I'm having to improvise with an unrealistic deadline...
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'COUNT'.
If I take out the last line including the 'Count', I get,
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '+'.
Also, is it just the 'user' that the script tries to return?? If so it's the whole row that I want, not just the user!!
Cheers,
Jaybee.
January 16, 2007 at 8:07 am
it's the alias for the count....we still need the schema and datasample to give a meaningful answer.
SELECT USER,
CASE WHEN ISNULL(APPLICATION1,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION2,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION3,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION4,0)=0 THEN 0 ELSE 1 END +
CASE WHEN ISNULL(APPLICATION5,0)=0 THEN 0 ELSE 1 END AS APPLICATION_COUNT FROM WORKING
Lowell
January 16, 2007 at 8:25 am
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
January 16, 2007 at 8:25 am
"Also, is it just the 'user' that the script tries to return?? If so it's the whole row that I want, not just the user!!"
What do you mean by "whole row"? Please supply more info as Lowell asked, otherwise we can't help you.
Posted SQL is the main part of the solution - code that will find number of applications for each user. Nothing more. You can join it then to the data again and display whetever you want.
Bear in mind that it is based on columns you mentioned - if there are more columns (like Application6, 7, 8...), you have to understand the code and modify it to include them all.
January 16, 2007 at 8:28 am
Sure, schema and sample posted above.
January 16, 2007 at 8:30 am
I see we posted about the same time... I'm sorry, but the data you supplied is absolutely illegible. Please, could you post a CREATE TABLE statement for your table (you don't have to include all columns, just those that are used in some way), and INSERT INTO for sample data? Otherwise we'd have to type it ourselves and guess where which column starts - to be honest, I like helping people, but I really don't have that much time... Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply