Solved

sql for total lines

Posted on 2011-03-09
15
230 Views
Last Modified: 2012-06-27
Hello,

I have a query that pulls financial data: a line description preceded by a number, and a month-to-date amount. I'd like to put some total lines in (based on the number).

So here's what my output looks like:

1. Cash                  100
2. Checking            500
3. Recievables        600
4. Fixed Assets       200
5. <this field is blank>
6. Accounts Payable  100
7. Loan due               200
8. <this field is blank>
9. Sales Revenue       300
10. <blank>

What I would like to do is something like this:
If line number = 5 sum MTD amount for lines 1 - 4
If line number = 8 sum MTD amount for lines 6 - 7
etc.

Thanks.
0
Comment
Question by:erp1022
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35087427
What is your SQL version? Do you have three columns in your output (SNo, String, Amount)? Is the String NULL for line 5, 8 and 10?
0
 

Author Comment

by:erp1022
ID: 35087449
2005. Two columns: String and Amount. Yes.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35087477
How do you order those records if you have two columns only? How do you know that 5th record is blank. Do you have an ID/Date field?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:erp1022
ID: 35087572
They are ordered by another field in my table called Line ID. I know that the 5th record is blank because we had have data in that field for what are supposed to be total lines. No date field.
0
 

Author Comment

by:erp1022
ID: 35087603
What I meant to say was, we have NO data in the line number field for what are supposed to be total lines.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35087633
How do you know the 'Cash' is 1st line and 'Checking' is 2nd line? There should some ordering between the records to decide which one is first and which one is second.
0
 

Author Comment

by:erp1022
ID: 35087646
Look at comment ID:35087572, I said that they are ordered by another field in table called line ID.
0
 

Author Comment

by:erp1022
ID: 35087669
Sorry, fields 5, etc. are not blank, they just contain the number but no text. That's okay, I just need to get the total for those lines.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35088880
So, you have three columns - ID, String, Amount. String and Amount fields are blank for ID = 5, 8 and 10. right?
0
 

Author Comment

by:erp1022
ID: 35095258
Yes, but we are not pulling the ID field. We are only pulling string and amount fields. Data for string field would be '1. Cash', '2. Checking', etc.
0
 

Author Comment

by:erp1022
ID: 35095751
Sample of what my data looks like...
Sample-Datalist.xls
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35102167
Try this query.
;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(ORDER BY ID) rn 
           FROM your_table 
          WHERE ISNUMERIC(Line_Description) = 1), 
     cte2 
     AS (SELECT c1.ID, 
                ISNULL(c2.ID,0) + 1 StartID, 
                c1.ID - 1           EndID 
           FROM cte c1 
                LEFT JOIN cte c2 
                  ON c1.rn = c2.rn + 1), 
     cte3 
     AS (SELECT *, 
                (SELECT SUM(MTD_Amount) 
                   FROM your_table t1 
                  WHERE t1.ID BETWEEN c2.StartID AND c2.EndID) MTD_Amount 
           FROM cte2 c2) 
SELECT t1.ID, 
       t1.Line_Description, 
       ISNULL(t1.MTD_Amount,c3.MTD_Amount) MTD_Amount 
  FROM your_table t1 
       LEFT JOIN cte3 c3 
         ON t1.ID = c3.ID

Open in new window

Here is the result with your sample data.
DECLARE  @table  TABLE( 
                                     ID INT, 
                       Line_Description VARCHAR(40), 
                             MTD_Amount DECIMAL(8,2) 
                       ) 

INSERT @table 
VALUES(1,'1. Cash',100.00), 
      (2,'2. Checking',400.00), 
      (3,'3. Accounts Receivable',200.00), 
      (4,'4. Fixed Assets',100.00), 
      (5,'5',NULL), 
      (6,'6. Accounts Payable',200.00), 
      (7,'7. Loans Payable',100.00), 
      (8,'8',NULL), 
      (9,'9. Sales Revenue',200.00), 
      (10,'10. Other Revenue',100.00), 
      (11,'11',NULL), 
      (12,'12. Admin Expense',100.00); 

;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(ORDER BY ID) rn 
           FROM @table 
          WHERE ISNUMERIC(Line_Description) = 1), 
     cte2 
     AS (SELECT c1.ID, 
                ISNULL(c2.ID,0) + 1 StartID, 
                c1.ID - 1           EndID 
           FROM cte c1 
                LEFT JOIN cte c2 
                  ON c1.rn = c2.rn + 1), 
     cte3 
     AS (SELECT *, 
                (SELECT SUM(MTD_Amount) 
                   FROM @table t1 
                  WHERE t1.ID BETWEEN c2.StartID AND c2.EndID) MTD_Amount 
           FROM cte2 c2) 
SELECT t1.ID, 
       t1.Line_Description, 
       ISNULL(t1.MTD_Amount,c3.MTD_Amount) MTD_Amount 
  FROM @table t1 
       LEFT JOIN cte3 c3 
         ON t1.ID = c3.ID
/*
ID	Line_Description	MTD_Amount
1	1. Cash	100.00
2	2. Checking	400.00
3	3. Accounts Receivable	200.00
4	4. Fixed Assets	100.00
5	5	800.00
6	6. Accounts Payable	200.00
7	7. Loans Payable	100.00
8	8	300.00
9	9. Sales Revenue	200.00
10	10. Other Revenue	100.00
11	11	300.00
12	12. Admin Expense	100.00
*/

Open in new window

0
 

Author Comment

by:erp1022
ID: 35110272
Wow, pretty slick!! Could you just briefly explain what each cte does? I'd like to actually learn something from this in case this issue ever comes up again.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35112874
CTE 1 - Get only the blank lines and give a row number from 1 onwards to those records
CTE 2 - Get the Start and End lines number for each blank line. For 5, its 1 to 4, For 8, its 6 to 7 and For 11, its 9 to 10
CTE 3 - Sum the amount from Start line to End line for those blank lines

And finally Left join this result set with the actual table and display this amount for the blank lines.

Does that make sence?
0
 

Author Comment

by:erp1022
ID: 35113261
Yes, absolutely. Thanks so much!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
efficient backup report for SQL Server 13 79
Create a Calendar table 29 43
SQL Server Pivot 5 39
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question