Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql for total lines

Posted on 2011-03-09
15
Medium Priority
?
243 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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