December 7, 2005 at 9:44 am
Hello all. New user here.
Developer trying to mess with SQL a bit, and need some help with a totals issue.
Declare @StartDate datetime
Declare @EndDate datetime
Set @StartDate = '04/15/2005'
Set @EndDate = '04/25/2005'
Select distinct(a.ACCOUNTNUMBER) as [Account Number],
b.ID as [Share ID],
b.TYPE as [Share Type],
b.LASTDIVAMOUNT as [Dividend],
b.BALANCE as [Current Balance],
d.MemberCode as [Member Code],
d.Description as [University],
(Select Sum(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.ACCOUNTNUMBER = c.PARENTACCOUNT
and c.SUBACTIONCODE = 'P'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Purchase Amount],
(Select Count(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.ACCOUNTNUMBER = c.PARENTACCOUNT
and c.SubActionCode = 'P'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Purchase Count],
(Select Sum(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.ACCOUNTNUMBER = c.PARENTACCOUNT
and c.SUBACTIONCODE = 'C'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Cash Advance Amount],
(Select Count(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.AccountNumber = c.ParentAccount
and c.SubActionCode = 'C'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Cash Advance Count],
(Select Sum(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.ACCOUNTNUMBER = c.PARENTACCOUNT
and c.SUBACTIONCODE = 'R'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Returns Amount],
(Select Count(c.BalanceChange)
From xxxx.dbo.TranApr c
Where
a.AccountNumber = c.ParentAccount
and c.SubActionCode = 'R'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
) as [Total Debit Card Returns Count]
From xxxx.dbo.ACCOUNT a
Join xxxx.dbo.Savings b
on a.AccountNumber = b.ParentAccount
Join xxxx.dbo.TranApr c
on a.AccountNumber = c.ParentAccount
Join xxxx.dbo.MemberCodes d
on a.MemberGroup = d.MemberCode
Where a.CLOSEDATE is NULL
and a.MemberGroup <> 0000
and b.CLOSEDATE is NULL
and d.CategoryID = '2'
Group by d.Description,
a.ACCOUNTNUMBER,
b.ID,
b.Type,
b.LASTDIVAMOUNT,
b.BALANCE,
c.SUBACTIONCODE,
d.MemberCode
Order by b.Type,
d.Description
Compute count (a.ACCOUNTNUMBER),
sum (b.lastdivamount),
sum (b.balance),
*****sum (Total Debit Card Purchase Amount)
***** i need to total up the 6 nested SELECT statements in the middle of the query using the compute clause, and I am having troubles. Could anyone lend a hand? Thanks!
-Ed
December 7, 2005 at 10:15 am
Sub-SELECTs within the SELECT will perform horribly - you are forcing SQL server to run a sub-query for every main row retruned, which is essentially creating the equivalent of a cursor. Yuck.
You will get better performance converting each sub-select into a derived table and joining to it based on the ACCOUNTNUMBER. Then the columns from the derived tables can be references in the Group/Order By etc.
An example, using just the [Total Debit Card Purchase Amount] calculated value. This is just a portion of your SQL statement, showing the relevant changes forjust 1 of the calculated columns ...
Select distinct(a.ACCOUNTNUMBER) as [Account Number],
b.ID as [Share ID],
b.TYPE as [Share Type],
b.LASTDIVAMOUNT as [Dividend],
b.BALANCE as [Current Balance],
d.MemberCode as [Member Code],
d.Description as [University],
dt1.[Total Debit Card Purchase Amount]
-- Other columns omitted for clarity
From xxxx.dbo.ACCOUNT a
Inner Join
-- Join a derived table to get [Total Debit Card Purchase Amount]
(
Select c.PARENTACCOUNT, Sum(c.BalanceChange) As [Total Debit Card Purchase Amount]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'P'
and c.PostDate >= @StartDate
and c.PostDate <= @EndDate
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
) dt1 -- reference this derived table as dt1
On (dt1.PARENTACCOUNT = a.ACCOUNTNUMBER)
-- remainder of statement omitted for clarity
December 8, 2005 at 8:53 am
PW, thank you for fine tuning my code, as you can see, I am still very new to SQL. Here is what I have, but I am still getting an "Invalid Column Name dt6, dt5, etc" Thank you again in advance.
Select distinct(a.ACCOUNTNUMBER) as [Account Number],
b.ID as [Share ID],
b.TYPE as [Share Type],
b.LASTDIVAMOUNT as [Dividend],
b.BALANCE as [Current Balance],
d.MemberCode as [Member Code],
d.Description as [University],
dt1.[Total Debit Card Purchase Amount],
dt2.[Total Debit Card Purchase Count],
dt3.[Total Debit Card Cash Advance Amount],
dt4.[Total Debit Card Cash Advance Count],
dt5.[Total Debit Card Returns Amount],
dt6.[Total Debit Card Returns Count]
From xxx.dbo.ACCOUNT a
Inner Join
-- Join a derived table to get [Total Debit Card Purchase Amount]
(
Select c.PARENTACCOUNT, Sum(c.BalanceChange) As [Total Debit Card Purchase Amount]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'P'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt1 -- reference this derived table as dt1
On (dt1.PARENTACCOUNT = a.ACCOUNTNUMBER)
Inner Join
(
Select c.PARENTACCOUNT, Count(c.BalanceChange) As [Total Debit Card Purchase Count]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'P'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt2
On (dt2.PARENTACCOUNT = a.ACCOUNTNUMBER)
Inner Join
(
Select c.PARENTACCOUNT, Sum(c.BalanceChange) As [Total Debit Card Cash Advance Amount]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'C'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt3
On (dt3.PARENTACCOUNT = a.ACCOUNTNUMBER)
Inner Join
(
Select c.PARENTACCOUNT, Count(c.BalanceChange) As [Total Debit Card Cash Advance Count]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'C'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt4
On (dt4.PARENTACCOUNT = a.ACCOUNTNUMBER)
Inner Join
(
Select c.PARENTACCOUNT, Sum(c.BalanceChange) As [Total Debit Card Returns Amount]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'R'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt5
On (dt5.PARENTACCOUNT = a.ACCOUNTNUMBER)
Inner Join
(
Select c.PARENTACCOUNT, Count(c.BalanceChange) As [Total Debit Card Returns Count]
From xxxx.dbo.TranApr c
Where c.SUBACTIONCODE = 'R'
and c.ParentRecord = 'SAVINGS'
Group By c.PARENTACCOUNT
)dt6
On (dt6.PARENTACCOUNT = a.ACCOUNTNUMBER)
Join xxxx.dbo.Savings b
on a.AccountNumber = b.ParentAccount
Join xxxx.dbo.MemberCodes d
on a.MemberGroup = d.MemberCode
Join xxxxx.dbo.TranApr c
on a.AccountNumber = c.ParentAccount
Where a.CLOSEDATE is NULL
and a.MemberGroup 0000
and b.CLOSEDATE is NULL
and d.CategoryID = '2'
Group by d.Description,
a.ACCOUNTNUMBER,
b.ID,
b.Type,
b.LASTDIVAMOUNT,
b.BALANCE,
c.SUBACTIONCODE,
d.MemberCode
Order by d.Description,
b.Type
Compute Sum(b.LASTDIVAMOUNT),
Sum(b.BALANCE),
Sum(dt1),
Count(dt2),
Sum(dt3),
Count(dt4),
Sum(dt5),
Count(dt6)
December 8, 2005 at 10:55 am
In your COMPUTE, you have several itmes like this: Sum(dt1).
The SUM() aggregate function expects a column name, but you are giving it a table name. It needs to be SUM(dt1.SomeColumnName)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply