Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

sql for total lines

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
erp1022
Asked:
erp1022
  • 9
  • 6
1 Solution
 
SharathData EngineerCommented:
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
 
erp1022Author Commented:
2005. Two columns: String and Amount. Yes.
0
 
SharathData EngineerCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
erp1022Author Commented:
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
 
erp1022Author Commented:
What I meant to say was, we have NO data in the line number field for what are supposed to be total lines.
0
 
SharathData EngineerCommented:
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
 
erp1022Author Commented:
Look at comment ID:35087572, I said that they are ordered by another field in table called line ID.
0
 
erp1022Author Commented:
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
 
SharathData EngineerCommented:
So, you have three columns - ID, String, Amount. String and Amount fields are blank for ID = 5, 8 and 10. right?
0
 
erp1022Author Commented:
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
 
erp1022Author Commented:
Sample of what my data looks like...
Sample-Datalist.xls
0
 
SharathData EngineerCommented:
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
 
erp1022Author Commented:
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
 
SharathData EngineerCommented:
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
 
erp1022Author Commented:
Yes, absolutely. Thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now