Need to alter some code

Question

Need to alter some code

Jay Bee-249994

SSCrazy

Points: 2393
More actions
July 11, 2007 at 7:49 am

#117649

Below is some rough code (some of it unnecessary) and an anonymised sample of its result set (how do I get the data to appear in grid format online??). I need to alter the code to return the following:
1) Total number of AccountId's where Ntuser ID is NOT in:
Domain1
Domain2
Domain3
2) Average number of ServiceNames per AccountID
Thanks,
Jaybee
SET NOCOUNT ON ;
-- validSubscriptions
select top 100 percent
tblServicesTree.serviceID ,
tblServicesTree.serviceName ,
tblAccounts.accountID ,
tblAccounts.ntUserDomain ,
tblAccounts.ntUserID ,
tblAccounts.accountEnabled ,
tblAccounts.accountFirstName ,
tblAccounts.accountLastName ,
tblAccounts.accountSubscriptionsEnabled ,
tblAccounts.useHtmlOnEmails
from
dbo.fn_getValidSubscriptions() as vs
inner join tblAccounts on tblAccounts.accountID = vs.accountID
inner join tblServicesTree on tblServicesTree.serviceID = vs.serviceID
where
tblAccounts.accountEnabled <> 0 and tblAccounts.accountSubscriptionsEnabled <> 0
SAMPLE RESULT SET:
serviceID, serviceName, accountID, ntUserDomain, ntUserID, accountEnabled, accountFirstName,
167........Multi SIM.......50194........20-uk.............puckern........1.................Natasha Pucker 1
58.........HARMS..........36394.........20-UK............SuUTERG.........1................Gordon Suuter 1
161........Prknet..........31547.........20-UK............EVAND5.........1.................Dennis Evan 1
84.........SURFS/.........34388.........20-UK............MILbergK........1.................Kevin Milberg 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hi Jay,

I think that you'll need to add another query (or probably 2 queries) to get these summary rows. Then you can use UNION if you want that it appears in one resultset with the detailed data (shown in sample). CASE statements could also be of some use here.

I'm not sure what is the problem - if you are able to write the above query, you should be able to write a simple SELECT COUNT. Please, could you be more precise in your requirements, and also add the desired result?

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 2

Hi,

I didn't write the query, I just want it changed (or split across multiple queries) to extract the needed data.

You can leave the averaging question, I have that licked now.

For the other, let me rephrase thus; what I want is count of the distinct accountId's where NTUser is NOT within (for example, Domain1, Domain2 etc).

Hope that clarifies,

Jaybee.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Well, since I have no idea what is in the function and the tables, I can't guarantee that this will work... but it should give you at least general idea:

SELECT COUNT(DISTINCT tblAccounts.accountID)

FROM dbo.fn_getValidSubscriptions() as vs

JOIN tblAccounts on tblAccounts.accountID = vs.accountID

JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID

WHERE tblAccounts.accountEnabled 0 AND tblAccounts.accountSubscriptionsEnabled 0

AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

If this is not what you want, I'll need more info about the data structure and requirements.

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 4

Thanks! That worked perfectly, the From/InnerJoin/Where statements are static and don't change. The functions and tables hold the correct data, and the result set was a perfect mathematic match.

There's a final tweak I need for this code, what I need now is for firstnames, surnames, servicenames and some other generic data to be returned (again, this data is ALL contained within the function and tables - I checked!) and grouped by distinct accountID. I'm sure it'll involve a Group by statement, but I'm not sure where it goes. I imagine my code will look similar to this;

Select top 100%

tblServicesTree.serviceID ,

tblServicesTree.serviceName ,

tblAccounts.accountID ,

tblAccounts.ntUserDomain ,

tblAccounts.ntUserID ,

tblAccounts.accountEnabled ,

tblAccounts.accountFirstName ,

tblAccounts.accountLastName ,

tblAccounts.accountSubscriptionsEnabled ,

FROM dbo.fn_getValidSubscriptions() as vs

JOIN tblAccounts on tblAccounts.accountID = vs.accountID

JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID

WHERE tblAccounts.accountEnabled <> 0 AND tblAccounts.accountSubscriptionsEnabled <> 0

AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

This is going to generate a big and repeating result set - there are an average of 27 serviceNames per account ID!!

Thanks,

Jaybee.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

"...servicenames and some other generic data to be returned and grouped by distinct accountID"

"there are an average of 27 serviceNames per account ID"

Now I'm at a loss how to group by account ID if there are 27 different service names and you want to display them all? Either you will group by account ID, and then you can only have 1 row for each accountID - or you will have to group by accountID + some other columns.

In case you really want to have 1 row per accountID, you need to decide how to display these other columns, like ServiceName. Either you choose one of the values (MIN, MAX...), or you can concatenate the values into one string, making it look like this:

accountID ServiceNames

asdqwe Service1, Service 2

bflmpsvz Room service, Secret service, Service1, Other service

For this solution, you would need UDF; please read the nearby discussion Creating a daily diary (and visit some of the links posted there).

BTW, why are you using SELECT TOP 100 PERCENT? There are a few places where this could be useful, but mostly it doesn't make sense... especially if there is no ORDER BY.