Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Computed column with subquery Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
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.



Post #1444472
Posted Thursday, May 2, 2013 9:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 3,014, Visits: 3,101
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
www.seavus.com
Post #1448845
Posted Friday, May 3, 2013 9:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 1,061, Visits: 2,578
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
Post #1449258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse