Solved

Recursive Query

Posted on 2012-03-19
7
318 Views
Last Modified: 2012-03-20
I need to write a query to update a table Billed with all activity for that record and any records previous to that date.  
BillNO      date       Allow      Payrec      ADjAmt      tot     Type
1      1/1/2012        45.94      0                 0              45      INV
1      1/10/2012      0            27.46            0           18.48    Chk
1      1/10/2012      0           18.48            0              0         Chk
2      1/2/2012        60.00       0              0          60.00     INV
2      1/5/2012      0      0          15.00              0          45.00     ADJ
2      1/6/2012      15.00        0              0          60.00     ADJ
2      2/7/2012        0          40.00             0          20.00     Chk
 
I need to take any entries in Allow and subtract any amounts in Payrec and AdjAmt and insert the total into tot.  Then on the subsequent records take the Tot from the previous record and add or subtract the Allow, Payrec and AdjAmt to obtain a new total using Date to sort by.
How do I go about doing this?  I believe I need a recursive query, but I don’t know how to go about writing this?

As a first attempt I wanted to just print out the records before I tried to accumulate totals:
This is what I wrote:
WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled, HierarchyLevel) AS
(
   -- Base case
   SELECT
     BillNO_NoDash,
     Allowed,
     Payrec,
     AdjustAmt,
     DateBilled,
      1 as HierarchyLevel
   FROM TBilled
   WHERE Descript = 'INV'
     and BillNO_NoDash = 56793 and DELFLAG = 0

   UNION ALL

   -- Recursive step
   SELECT
   B.BillNO_NoDash,
     B.Allowed,
     B.Payrec,
     B.AdjustAmt,
     B.DateBilled,
     pr.HierarchyLevel + 1 AS HierarchyLevel
   FROM (Select *
            from TBILLED
            where  Descript <> 'INV' and BillNO_NoDash = 56793
            
            )B
      INNER JOIN BaseBillNO pr ON
         B.BillNO_NoDash = pr.BillNO_NODash
     
)

SELECT *
FROM BaseBillNO
where BillNO_NoDash = 56793 and HierarchyLevel < 10
ORDER BY BillNO_NoDash, HierarchyLevel, Allowed, Payrec, AdjustAmt,DateBilled

I am getting an error: The maximum recursion 100 has been exhausted before statement completion.

Any thoughts would be greatly appreciated.
0
Comment
Question by:LenTompkins
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 37740044
Someday I'll figure out if you can do that with a recursive query.  Maybe.  But till then, this style works:


WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled) AS
(
   -- Filtered case
   SELECT
     BillNO_NoDash,
     Allowed,
     Payrec,
     AdjustAmt,
     DateBilled
   FROM TBilled
   WHERE Descript = 'INV'
     and BillNO_NoDash = 56793 and DELFLAG = 0
)
SELECT *,
     (Select sum(f.allowed - f.payrec - f.AdjustAmount) from BaseBillNO f
            where f.billNO_NoDash = b.BillNO_NoDash  
                  and f.DateBilled <= b.dateBilled
                  group by f.billNO_NoDash  
        ) as Tot
   FROM BaseBillNO B
   order by b.DateBilled
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37740094
Hi Len,

Several things seem to be at work here.

1.  Are you familiar with the concepts of combinations, permutations, and cartesian products?  In your recursive query, the second subquery (the lower half) joins on the column *BillNO_NODash*.  That causes a cartesian product when every row that matches on *BillNO_NODash* is joined to the results of the base query.  When the recursive step occurs, every row that matches on *BillNO_NODash* is joined with the result of the first join.  If there are 10 rows with the same value for *BillNO_NODash*, the base query generates 1 row, the lower half of the query generates 10 (for that value of *BillNO_NODash*) the first recursive pass generates 100, the next 1000, etc.

The lower sub-query needs to join ONE row from the table each pass.  (Anything else is extremely complicated AND advanced.)

There are a couple of articles in the DB2 forum that describe this.  The Common Table Expression (CTE) syntax is nearly identical between DB2 and SQL Server.  The only real difference of note is that the concatenation operator in DB2 is double-pipe (||) and in SQL Server it's plus (+).

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html
  http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html


2.  But you don't really need recursive SQL here.  Recursive SQL puts multiple lines on a single row, or splits a single row into multiple lines.  You seem to need aggregation here (SUM, COUNT, AVG, etc.)  If you want to study recursive techniques, those articles will help.  But I don't think that they're needed for this solution.


Good Luck,
Kent
0
 

Author Comment

by:LenTompkins
ID: 37742772
Thank you both for this information.  I read the article on converting rows to columns and I had a problem.  
1.  I created the table and the query as follows and I am still getting an error:
CREATE TABLE rec
(
  snum      INTEGER,        -- sentence number
  wordnum   INTEGER,        -- word number in the sentence
  word      VARCHAR(100)    -- word being saved
);

 INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');
 
 Select * from Rec  
 
WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.snum, base.wordnum, base.word
  FROM rec base
  WHERE wordnum = 1
 
  UNION ALL
 
  SELECT t1.snum, t1.wordnum, t0.sentence + ' ' + t1.word
  FROM rquery t0, rec t1
  WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery;      

Types don't match between the anchor and the recursive part in column "sentence" of recursive query "rquery"

Can you tell me what is wrong?

2.  I tried the query that dqmq posted and that returns one record.  I simplied the test, by creating a table just like what was stated above:
USE [SleepMgt]
GO

CREATE TABLE [dbo].[ttBilled](
      [BillNO] [int] NULL,
      [Allowed] [numeric](19, 2) NULL,
      [Payrec] [numeric](19, 2) NULL,
      [AdjustAmt] [numeric](19, 2) NULL,
      [DateBilled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, CAST(45.00 AS Numeric(19, 2)), NULL, NULL, CAST(0x00009FCB00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(25.00 AS Numeric(19, 2)), CAST(0x00009FEA00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(20.00 AS Numeric(19, 2)), CAST(0x00009FF800000000 AS DateTime))

WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
   -- Filtered case
   SELECT
     T.BillNO,
     Allowed,
     Payrec,
     AdjustAmt,
     DateBilled,
     isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
   FROM (Select Min(DateBilled) as minDateBilled, BillNO
                  from  TTBilled
                  Group by BillNO
             ) A
    inner join tTBILLED  T
    on A.BillNO       = T.BillNO and A.minDateBilled = T.DATEBILLED             
)
SELECT *,
     (Select sum(isNull(f.allowed,0) - IsNull(f.payrec,0) - isNull(f.AdjustAmt,0)) from BaseBillNO f
            where f.billNO = b.BillNO  
                  and b.DateBilled <= f.dateBilled
                  group by f.billNO
        ) as Tot
   FROM BaseBillNO B
   order by b.DateBilled

This doesn't abend, but it is not giving me a proper answer.
BillNO      Allowed      Payrec      AdjustAmt      DateBilled      Tot      Tot
1      45.00      NULL      NULL      2012-01-01 00:00:00.000      45.00      45.00

How do I debug these queries?  I suspect I have something wrong with the lower select statement.

Thanks for giving me some assistance.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 37742821
Hi Len,

For some reason, SQL Server is recasting the concatenated value in the recursive sub-subquery as a type that's not compatible with varchar(100).  (Varchar(100) is the default type for that column because it's the type associated with the original data element, word.)

Changing the first line in the low-subquery solves it:

    SELECT t1.snum, t1.wordnum, cast (t0.sentence + ' ' + t1.word as varchar(100))


If the string could be longer than 100 characters, the column in both the upper and lower sub-queries will need to be recast to a larger size.



Good Luck,
Kent
0
 

Author Comment

by:LenTompkins
ID: 37743001
Kent Thanks that solved that problem.  How do I debug the query in question 2?  I tried using the same idea for this problem and I'm getting this error:

 WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
   -- Filtered case
   SELECT
     T.BillNO,
     Allowed,
     Payrec,
     AdjustAmt,
     DateBilled,
     isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
   FROM (Select Min(DateBilled) as minDateBilled, BillNO
                  from  TTBilled
                  Group by BillNO
             ) A
    inner join tTBILLED  T
    on A.BillNO       = T.BillNO and A.minDateBilled = T.DATEBILLED             
Union all

     (Select F.BillNO,F.Allowed, F.Payrec, F.AdjustAmt, F.DateBilled, B.tot
      from ( Select BILLNO, Allowed, Payrec, AdjustAmt, DateBilled
                    from  ttBilled
                  
                  )  f,  BaseBillNO B
            where f.billNO = b.BillNO  
                  and b.DateBilled <= f.dateBilled
                 
        )

 
)
Select * from BaseBillNO    

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Can I put a print statement in this somewhere?  If so where?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 37743273
Not sure why you are only getting one row back.  Your test data and my old query returns 3 rows.   Avoid the recursive CTE for now.  I've cleaned this up to use your test data and to treat nulls as zero. Give it a try:

;WITH BaseBillNO AS
(
   SELECT
     BillNO,
     isnull(Allowed,0) allowed,
     isnull(Payrec,0) payrec,
     isnull(AdjustAmt,0) adjustamt,
     DateBilled
     FROM TTBilled          
)
SELECT B.*,
     (Select sum(f.allowed - f.payrec - f.AdjustAmt) from BaseBillNO f
            where f.billNO = b.BillNO  
                  and b.DateBilled <= f.dateBilled
                  group by f.billNO
        ) as Tot
   FROM BaseBillNO B
   order by b.DateBilled

Open in new window

0
 

Author Closing Comment

by:LenTompkins
ID: 37743762
Thanks for all of the information.  I got the results I needed for my specific post from the last post and I appreciated getting the information about recursive queries.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

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 ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

729 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