SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed column with subquery


Computed column with subquery

Author
Message
a_ud
a_ud
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 336
Hi,

My question is about calculating a computed column in SSIS. Unfortunately, the column is based on another column in the same query which, at its turn, is a subquery.

I'd prefer a generic answer, but I know some people can't answer unlesss they see the script, so here they are.

Table X1 (scores of volunteer per week and class):
CREATE TABLE [dbo].[tblPAScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VolunteerID] [varchar](50) NOT NULL,
[NoWeek] [int] NULL,
[PA] [numeric](5, 2) NULL,
[Class] [varchar](50) NULL,
CONSTRAINT [PK_tblPAScores] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


The view on previous table:
SELECT     TOP (100) PERCENT VolunteerID, Class, NoWeek, PA,
(SELECT COUNT(*) AS Expr1
FROM dbo.tblPAScores
WHERE (PA > X1.PA) AND (Class = X1.Class) AND (NoWeek = X1.NoWeek)) + 1 AS PA1R,
(SELECT COUNT(VolunteerID) AS Expr1
FROM dbo.tblPAScores AS tblPAScores_1
WHERE (Class = X1.Class) AND (NoWeek = X1.NoWeek)) AS N
FROM dbo.tblPAScores AS X1
ORDER BY NoWeek, VolunteerID


• N: calculates the number of people for a week and class (column 'N')
• PA1R: calculates how many people have higher PA score than you in that week and class

In the next column, I'd need to divide N by PA1R, it's that simple. Why? The target is, in terms of PA, calculate what percentage of people does better than yourself (say, 30%), same as you (40%) and worse (say 30%) fior a given week and class.

To get this, I'd need dividing N by PA1R (1/N, (PA1R-1/N), etc).

Questions:
1) Is it possible to have a computed column based on previous columns which are subqueries?
2) If not, any alternative solution (pivot tables or the like)? Do I have to run a megasubquery (sort of the subquery1 / subquery 2)

Thanks in advance, a.
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5960 Visits: 5084
a_ud (4/19/2013)
Hi,

Questions:
1) Is it possible to have a computed column based on previous columns which are subqueries?


Hi,
It is possible to have computed columns in a view. If you tried directly 1/N; PA1R/N then you probably had an error message.

create view vwPAScores
as
SELECT TOP (100) PERCENT VolunteerID, Class, NoWeek, PA,
(SELECT COUNT(*) AS Expr1
FROM dbo.tblPAScores
WHERE (PA > X1.PA) AND (Class = X1.Class) AND (NoWeek = X1.NoWeek)) + 1 AS PA1R,
(SELECT COUNT(VolunteerID) AS Expr1
FROM dbo.tblPAScores AS tblPAScores_1
WHERE (Class = X1.Class) AND (NoWeek = X1.NoWeek)) AS N,
1/(SELECT COUNT(VolunteerID) AS Expr1 FROM dbo.tblPAScores AS tblPAScores_1 WHERE (Class = X1.Class) AND (NoWeek = X1.NoWeek)) as [1_divide_N],
(((SELECT COUNT(*) AS Expr1 FROM dbo.tblPAScores WHERE (PA > X1.PA) AND (Class = X1.Class) AND (NoWeek = X1.NoWeek)) + 1)-1)/(SELECT COUNT(VolunteerID) AS Expr1 FROM dbo.tblPAScores AS tblPAScores_1 WHERE (Class = X1.Class) AND (NoWeek = X1.NoWeek)) as [PA1R-1_divide_N]

FROM dbo.tblPAScores AS X1
ORDER BY NoWeek, VolunteerID



Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 2582
a_ud (4/19/2013)
Hi,

My question is about calculating a computed column in SSIS. Unfortunately, the column is based on another column in the same query which, at its turn, is a subquery.

I'd prefer a generic answer, but I know some people can't answer unlesss they see the script, so here they are.

Table X1 (scores of volunteer per week and class):
CREATE TABLE [dbo].[tblPAScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VolunteerID] [varchar](50) NOT NULL,
[NoWeek] [int] NULL,
[PA] [numeric](5, 2) NULL,
[Class] [varchar](50) NULL,
CONSTRAINT [PK_tblPAScores] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


The view on previous table:
SELECT     TOP (100) PERCENT VolunteerID, Class, NoWeek, PA,
(SELECT COUNT(*) AS Expr1
FROM dbo.tblPAScores
WHERE (PA > X1.PA) AND (Class = X1.Class) AND (NoWeek = X1.NoWeek)) + 1 AS PA1R,
(SELECT COUNT(VolunteerID) AS Expr1
FROM dbo.tblPAScores AS tblPAScores_1
WHERE (Class = X1.Class) AND (NoWeek = X1.NoWeek)) AS N
FROM dbo.tblPAScores AS X1
ORDER BY NoWeek, VolunteerID


• N: calculates the number of people for a week and class (column 'N')
• PA1R: calculates how many people have higher PA score than you in that week and class

In the next column, I'd need to divide N by PA1R, it's that simple. Why? The target is, in terms of PA, calculate what percentage of people does better than yourself (say, 30%), same as you (40%) and worse (say 30%) fior a given week and class.

To get this, I'd need dividing N by PA1R (1/N, (PA1R-1/N), etc).

Questions:
1) Is it possible to have a computed column based on previous columns which are subqueries?
2) If not, any alternative solution (pivot tables or the like)? Do I have to run a megasubquery (sort of the subquery1 / subquery 2)

Thanks in advance, a.





Hi again, @a_ud,

By posting your code rather than asking for a generic answer, you've allowed me to give you what should be a better solution. Your view definition could be

SELECT VolunteerID, Class, NoWeek, PA,
Y.PA1R,
Z.N,
((Z.N-Y.PA1R)/(Z.N * 1.0)) * 100 AS pct
FROM dbo.tblPAScores AS X1

OUTER APPLY (SELECT COUNT(*) + 1 AS PA1R
FROM dbo.tblPAScores X2
WHERE (X2.PA > X1.PA) AND (X2.Class = X1.Class) AND (X2.NoWeek = X1.NoWeek)) Y

OUTER APPLY (SELECT COUNT(X3.VolunteerID) AS N
FROM dbo.tblPAScores X2
WHERE (X2.Class = X1.Class) AND (X2.NoWeek = X1.NoWeek)) Z



The correlated subqueries in the OUTER APPLYs generate columns for PA1R and N for each row, and you can use those columns as many different times and ways as you'd like in the SELECT columns.

One note - you'll see that I set up the calculation of the percent with better scores with a seemingly superfluous "* 1.0". Since COUNT() returns an int, N and PA1R will both be ints. Therefore, (N-PA1R)/N will return an int, which will always be 0. Adding the "* 1.0" introduces a decimal datatype into the mix so that all the int values will be implicitly converted to decimal values before the calculation is performed, allowing you to get values in the domain 0.0 - 1.0, as expected.

Also, you'll see that I removed the TOP(100) PERCENT . . . ORDER BY from my code. It's generally better practice to order the results in the SELECT statement that references your view. You can run into some unexpected performance problems when you write views to return ordered result sets. When the query optimizer expands the view definition into the query, the ORDER BY in the view may confound the optimizer and lead to an undesirable execution plan.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search