Solved

sql for total lines

Posted on 2011-03-09
15
192 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
  • 9
  • 6
15 Comments
 
LVL 40

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 40

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
 

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 40

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 40

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 40

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 40

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now