Click here to Skip to main content
15,886,075 members
Home / Discussions / Database
   

Database

 
GeneralRe: How to create data copies SQL Server 2005 Express ? Pin
Mycroft Holmes17-Dec-14 11:49
professionalMycroft Holmes17-Dec-14 11:49 
GeneralRe: How to create data copies SQL Server 2005 Express ? Pin
Member 245846717-Dec-14 20:59
Member 245846717-Dec-14 20:59 
GeneralRe: How to create data copies SQL Server 2005 Express ? Pin
Mycroft Holmes17-Dec-14 21:39
professionalMycroft Holmes17-Dec-14 21:39 
GeneralRe: How to create data copies SQL Server 2005 Express ? Pin
Member 245846721-Dec-14 17:01
Member 245846721-Dec-14 17:01 
QuestionChange cursor to common table Pin
Amr Mohammad15-Dec-14 1:16
Amr Mohammad15-Dec-14 1:16 
SuggestionRe: Change cursor to common table Pin
Richard Deeming15-Dec-14 2:56
mveRichard Deeming15-Dec-14 2:56 
GeneralRe: Change cursor to common table Pin
Amr Mohammad15-Dec-14 8:22
Amr Mohammad15-Dec-14 8:22 
AnswerRe: Change cursor to common table Pin
Richard Deeming15-Dec-14 10:32
mveRichard Deeming15-Dec-14 10:32 
It's tricky to answer without your table definitions and some sample data, but this should get you close:
SQL
WITH cteAccountTree As
(
    SELECT
        @AccountID As Account_ID

    UNION ALL

    SELECT
        P.Account_ID
    FROM
        cteAccountTree As P
        INNER JOIN AccountTree As C
        ON C.Account_ParentID = P.Account_ID
    And
        Account_Isleaf = 0
)
SELECT
    @TotalValue = IsNull(Sum(JournalDet_Debit), 0) * CASE WHEN @Currency = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
FROM
    cteAccountTree As T
    INNER JOIN Account_InBranch As B ON B.AccountBranch_AccountID = T.Account_ID
    LEFT JOIN Journal_Details As JD ON JD.JournalDet_AccountID = T.Account_ID
    LEFT JOIN Journal_Head As JH ON JH.Journal_ID = JD.JournalDet_HeadID
WHERE
    Journal_BranchID = @Branch
And
    Journal_Date Between @DateFrom And @DateTo
;


The first part is a recursive common table expression (CTE):
How to use recursive CTE calls in T-SQL[^]

This should return the list of all accounts in the tree which have the specified account ID as an ancestor, excluding any with the Account_Isleaf flag set.

NB: If your tree is particularly deep, you might run into the default recursion limit. There will probably be a way to work around it, but it won't be as nice as the recursive CTE solution. Smile | :)

You then join the tree of account IDs to your branch and journal tables to calculate the total in one hit.

The only part I'm not sure about: your code seems to be double-counting at each level:
sql>SELECT
<hr size="1" color="#63B4FF" noshade="">
  <span style="color: rgba(0, 88, 170, 1)">
    <cite>"These people looked deep within my soul and assigned me a number based on the order in which I joined."</cite>
     - Homer
  </span>

GeneralRe: Change cursor to common table Pin
Amr Mohammad16-Dec-14 21:40
Amr Mohammad16-Dec-14 21:40 
GeneralRe: Change cursor to common table Pin
Richard Deeming17-Dec-14 2:05
mveRichard Deeming17-Dec-14 2:05 
GeneralRe: Change cursor to common table Pin
Amr Mohammad17-Dec-14 3:32
Amr Mohammad17-Dec-14 3:32 
QuestionHow can you tell if a SQL Server instance max'ed out bottleneck is Memory? Pin
devvvy11-Dec-14 4:20
devvvy11-Dec-14 4:20 
AnswerRe: How can you tell if a SQL Server instance max'ed out bottleneck is Memory? Pin
Tim Carmichael11-Dec-14 4:23
Tim Carmichael11-Dec-14 4:23 
GeneralRe: How can you tell if a SQL Server instance max'ed out bottleneck is Memory? Pin
M Riaz Bashir11-Dec-14 15:07
M Riaz Bashir11-Dec-14 15:07 
QuestionMySQL Export Problem Pin
M Riaz Bashir10-Dec-14 22:46
M Riaz Bashir10-Dec-14 22:46 
AnswerRe: MySQL Export Problem Pin
Eddy Vluggen11-Dec-14 4:59
professionalEddy Vluggen11-Dec-14 4:59 
QuestionDatabase Mail Not sending Email Pin
Robymon8-Dec-14 20:56
Robymon8-Dec-14 20:56 
QuestionDo I have to have SQL Server Express installed? Pin
jkirkerx30-Nov-14 11:31
professionaljkirkerx30-Nov-14 11:31 
AnswerRe: Do I have to have SQL Server Express installed? Pin
Richard MacCutchan30-Nov-14 21:30
mveRichard MacCutchan30-Nov-14 21:30 
GeneralRe: Do I have to have SQL Server Express installed? Pin
jkirkerx2-Dec-14 10:21
professionaljkirkerx2-Dec-14 10:21 
AnswerRe: Do I have to have SQL Server Express installed? Pin
Eddy Vluggen2-Dec-14 7:27
professionalEddy Vluggen2-Dec-14 7:27 
GeneralRe: Do I have to have SQL Server Express installed? Pin
jkirkerx2-Dec-14 10:25
professionaljkirkerx2-Dec-14 10:25 
Questionsql server import export wizard stored procedure? Pin
iamvinod3426-Nov-14 18:57
iamvinod3426-Nov-14 18:57 
AnswerRe: sql server import export wizard stored procedure? Pin
Mycroft Holmes26-Nov-14 20:09
professionalMycroft Holmes26-Nov-14 20:09 
AnswerRe: sql server import export wizard stored procedure? Pin
jschell27-Nov-14 6:02
jschell27-Nov-14 6:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.