Solved

Display additional rows based on rows contents in Ms Sql

Posted on 2013-10-27
20
348 Views
Last Modified: 2013-10-30
We are trying to prepare a script that will display more rows than what is in the table based on the rows contents.  For example, if a transaction table has 2 transaction, The first is an invoice and the second is a payment, we want the payment to per differences found in quantities column, cost or dates column.  So if the product has a different dates, the payment line will display 2 lines.  If the product has a different dates, different cost and different quantities, the payment line will display 3 lines for this one line payment
0
Comment
Question by:rayluvs
  • 9
  • 8
  • 2
  • +1
20 Comments
 

Author Comment

by:rayluvs
ID: 39604329
Ok to be more clear, here is an example of the actual data in the table:

TransNo TransType  TransDate Prod  Qty Cost
------- ---------- --------- ----- --- ----
Purch1  Invoice    1/1/2013  Prod1 40  105
Purch1  Payment    1/1/2013  Prod1 5   115
Purch1  Payment    2/1/2013  Prod1 45  120

Open in new window


The script should display:
(the reason is because from the invoice, there changes in dates, costs,quantities.  So in essences, for every change, re-display the lines and if the changes in the 1 line is 2, then display 2 lines)

TransNo TransType  TransDate Prod  Qty Cost Changes
------- ---------- --------- ----- --- ---- ------------
Purch1  Invoice    1/1/2013  Prod1 40  105
Purch1  Payment    1/1/2013  Prod1 5   115   change cost
Purch1  Payment    1/1/2013  Prod1 5   115   change qtys
Purch1  Payment    2/1/2013  Prod1 45  120   change dates
Purch1  Payment    2/1/2013  Prod1 45  120   change qtys
Purch1  Payment    2/1/2013  Prod1 45  120   change cost

Open in new window


We are working in the script, here is a sample data:
-- Create Virtual table
   DECLARE @PurchTransactions TABLE(TransNo VARCHAR(20), TransType VARCHAR(20), TransDate DATE, Prod VARCHAR(20),Qty INT,Cost FLOAT)

-- Insert Test Data
   INSERT INTO @PurchTransactions SELECT 'Purch1','Invoice','1/1/2013','Prod1',40,105
   INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','1/1/2013','Prod1',5,115
   INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','2/1/2013','Prod1',45,120

-- Display Tables
   select * from @PurchTransactions

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39604467
>we want the payment to per differences found in quantities column, cost or dates column.
I'm really confused as to what you're trying to do here, especially 'to per'.

For starters, a table should only have one activity going on, so you'll need to spell out why invoices and payments are co-existing in the same table, and not separate tables.

Also, payments are typically on an invoice, and not for an invoice line item (with a product), so it is not clear why payments are product-specific.

Are the multiple payment rows each individual payments, or some kind of running balance?
0
 

Author Comment

by:rayluvs
ID: 39604563
First of all we are in complete agreement, they should be in separate tables.  Unfortunately we have to analyse this old table that comes from an old system; it was was made that way.
More Info:
The transaction table is a child table of 2 parent tables.
The Parent Tables are Invoice and Payments.
The items or child are located in the Transaction Table.
We only wanted to include the Transaction Table because we don't need data from the Parent Tables.

As for "Are the multiple payment rows each individual payments, or some kind of running balance?", No.  The apps that works with table is design to apply a specific amount to the item being paid.

In essence what we need is to display additional rows in the result.  Rows that doesn't exist in the table but are created & displayed in the result due a the line is related.  For what we got so far was using a cursor to maintain the sequential (we think).

Hope it help what we explained.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39604735
what version of sql server is this please
(you have both 2008 and 2005 listed - but could it be sql 2012?)
(( sql 2012 has lead/lag which could make a difference here ))
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39604740
sorry, one more, is the field [Cost] really a float?
is it is, how many decimal places are considered significant for his exercise
e.g. 2? 3? 5?
0
 

Author Comment

by:rayluvs
ID: 39604742
Version 2008

Regarding [Cost], 2 decimal point.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39604796
Thanks, I haven't needed to bother with the float rounding here due to the sample - but you might have to.

For SQL 2008 achieved this result:
| TRANSNO | TRANSTYPE |  TRANSDATE |  PROD | QTY | COST |      CHANGES |
|---------|-----------|------------|-------|-----|------|--------------|
|  Purch1 |   Invoice | 2013-01-01 | Prod1 |  40 |  105 |       (null) |
|  Purch1 |   Payment | 2013-01-01 | Prod1 |   5 |  115 |  change qtys |
|  Purch1 |   Payment | 2013-01-01 | Prod1 |   5 |  115 |  change cost |
|  Purch1 |   Payment | 2013-02-01 | Prod1 |  45 |  120 | change dates |
|  Purch1 |   Payment | 2013-02-01 | Prod1 |  45 |  120 |  change qtys |
|  Purch1 |   Payment | 2013-02-01 | Prod1 |  45 |  120 |  change cost |

Open in new window

Produced by the following query, note there is not real reason for the second cte and it could just be a subquery instead:
;WITH
  trans_cte AS (
                    SELECT
                           TransNo
                         , TransType
                         , TransDate
                         , Prod
                         , Qty
                         , Cost
                         , row_number() over (partition BY TransNo ORDER BY TransDate ASC) AS rn
                    FROM PurchTransactions
                  )
, diffs_cte AS (
                    SELECT
                          t.TransNo
                        , t.TransType
                        , t.TransDate
                        , t.Prod
                        , t.Qty
                        , t.Cost
                        , t.rn
                        , CASE WHEN nxt.TransDate <> t.TransDate THEN nxt.rn ELSE NULL END AS diff_date
                        , CASE WHEN nxt.qty       <> t.qty       THEN nxt.rn ELSE NULL END AS diff_qtys
                        , CASE WHEN nxt.cost      <> t.cost      THEN nxt.rn ELSE NULL END AS diff_cost
                    FROM trans_cte AS t
                    LEFT JOIN trans_cte AS nxt ON t.TransNo = nxt.TransNo
                                              AND (t.rn + 1) = nxt.rn
               )
SELECT
        diffs_cte.TransNo
      , coalesce(trans_cte.TransType, diffs_cte.TransType) as TransType
      , coalesce(trans_cte.TransDate, diffs_cte.TransDate) as TransDate
      , coalesce(trans_cte.Prod     , diffs_cte.Prod     ) as Prod
      , coalesce(trans_cte.Qty      , diffs_cte.Qty      ) as Qty
      , coalesce(trans_cte.Cost     , diffs_cte.Cost     ) as Cost
      , case when trans_cte.rn > 1 then ca1.changes else null end as changes
FROM diffs_cte
CROSS APPLY (
              VALUES
                   (diff_date, 'change dates')
                 , (diff_qtys, 'change qtys' )
                 , (diff_cost, 'change cost' )
           ) AS ca1(diff_rn, changes)
left join trans_cte on ca1.diff_rn = trans_cte.rn
where diff_rn is not null
   or diffs_cte.rn = 1
order by
        diffs_cte.TransNo
      , diffs_cte.rn
      , diff_rn

-- data

create table PurchTransactions 
(TransNo VARCHAR(20), TransType VARCHAR(20)
 , TransDate DATE, Prod VARCHAR(20),Qty INT,Cost FLOAT)

-- Insert Test Data
   INSERT INTO PurchTransactions values ( 'Purch1','Invoice','1/1/2013','Prod1',40,105 )
   INSERT INTO PurchTransactions values ( 'Purch1','Payment','1/1/2013','Prod1',5,115 )
   INSERT INTO PurchTransactions values ( 'Purch1','Payment','2/1/2013','Prod1',45,120 )


[1]: http://sqlfiddle.com/#!3/ae451a/1

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39604800
alternate, using subquery
;WITH
  trans_cte AS (
                    SELECT
                           TransNo
                         , TransType
                         , TransDate
                         , Prod
                         , Qty
                         , Cost
                         , row_number() over (partition BY TransNo ORDER BY TransDate ASC) AS rn
                    FROM PurchTransactions
                  )
SELECT
        diffs_cte.TransNo
      , coalesce(trans_cte.TransType, diffs_cte.TransType) AS TransType
      , coalesce(trans_cte.TransDate, diffs_cte.TransDate) AS TransDate
      , coalesce(trans_cte.Prod     , diffs_cte.Prod     ) AS Prod
      , coalesce(trans_cte.Qty      , diffs_cte.Qty      ) AS Qty
      , coalesce(trans_cte.Cost     , diffs_cte.Cost     ) AS Cost
      , CASE WHEN trans_cte.rn > 1 THEN ca1.changes ELSE NULL END AS changes
FROM (
        SELECT
        t.TransNo
        , t.TransType
        , t.TransDate
        , t.Prod
        , t.Qty
        , t.Cost
        , t.rn
        , CASE WHEN nxt.TransDate <> t.TransDate THEN nxt.rn ELSE NULL END AS diff_date
        , CASE WHEN nxt.qty       <> t.qty       THEN nxt.rn ELSE NULL END AS diff_qtys
        , CASE WHEN nxt.cost      <> t.cost      THEN nxt.rn ELSE NULL END AS diff_cost
        FROM trans_cte AS t
        LEFT JOIN trans_cte AS nxt ON t.TransNo = nxt.TransNo
        AND (t.rn + 1) = nxt.rn
     ) AS diffs
CROSS APPLY (
              VALUES
                   (diff_date, 'change dates')
                 , (diff_qtys, 'change qtys' )
                 , (diff_cost, 'change cost' )
           ) AS ca1(diff_rn, changes)
LEFT JOIN trans_cte ON ca1.diff_rn = trans_cte.rn
WHERE diff_rn IS NOT NULL
   OR diffs.rn = 1
ORDER BY
        diffs.TransNo
      , diffs.rn
      , diff_rn

Open in new window

http://sqlfiddle.com/#!3/ae451a/2
0
 

Author Comment

by:rayluvs
ID: 39604892
Worked perfectly but when running on another item from real data, it repeats the invoice line.  

--------------------------
FROM DATA TABLE:
--------------------------
TransNo TransType TransDate  Prod  Qty Cost
------- --------- ---------- ----- --- ----
Purch1  Invoice   2004-02-15 Prod1 52  200
Purch1  Payment   2004-02-15 Prod1 -52 200
Purch1  Payment   2004-02-15 Prod1 10  200
Purch1  Payment   2004-02-15 Prod1 16  100

--------------------------
EXPECTED RESULT:
--------------------------
TransNo TransType TransDate  Prod  Qty Cost
------- --------- ---------- ----- --- ----
Purch1  Invoice   2004-02-15 Prod1 52  200  <------ THIS LINE IS REPEATED WHEN RUNNING THE SCRIPT
Purch1  Payment   2004-02-15 Prod1 -52 200 change qtys
Purch1  Payment   2004-02-15 Prod1 10  200 change qtys
Purch1  Payment   2004-02-15 Prod1 16  100 change qtys
Purch1  Payment   2004-02-15 Prod1 16  100 change cost

Open in new window


Here is the script (please advice):

-- Create Virtual table
   DECLARE @PurchTransactions TABLE(TransNo VARCHAR(20), TransType VARCHAR(20), TransDate DATE, Prod VARCHAR(20),Qty INT,Cost FLOAT)

-- Insert Test Data
 INSERT INTO @PurchTransactions SELECT 'Purch1','Invoice','20040215','Prod1',52,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',-52,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',10,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',16,100


-- Display Tables
  select * from @PurchTransactions

-- Script that does the Job:
;WITH
  trans_cte AS (SELECT TransNo
                      ,TransType
                      ,TransDate
                      ,Prod
                      ,Qty
                      ,Cost
                      ,row_number() over (partition BY TransNo ORDER BY TransDate ASC) AS rn
                    FROM @PurchTransactions)
  ,diffs_cte AS (SELECT t.TransNo
                       ,t.TransType
                       ,t.TransDate
                       ,t.Prod
                       ,t.Qty
                       ,t.Cost
                       ,t.rn
                       ,CASE WHEN nxt.TransDate <> t.TransDate THEN nxt.rn ELSE NULL END AS diff_date
                       ,CASE WHEN nxt.qty       <> t.qty       THEN nxt.rn ELSE NULL END AS diff_qtys
                       ,CASE WHEN nxt.cost      <> t.cost      THEN nxt.rn ELSE NULL END AS diff_cost
                    FROM trans_cte AS t
                    LEFT JOIN trans_cte AS nxt ON t.TransNo = nxt.TransNo AND (t.rn + 1) = nxt.rn)
SELECT diffs_cte.TransNo
      ,coalesce(trans_cte.TransType, diffs_cte.TransType) as TransType
      ,coalesce(trans_cte.TransDate, diffs_cte.TransDate) as TransDate
      ,coalesce(trans_cte.Prod     , diffs_cte.Prod     ) as Prod
      ,coalesce(trans_cte.Qty      , diffs_cte.Qty      ) as Qty
      ,coalesce(trans_cte.Cost     , diffs_cte.Cost     ) as Cost
      ,case when trans_cte.rn > 1 then ca1.changes else null end as changes
     FROM diffs_cte
     CROSS APPLY (VALUES
                   (diff_date, 'change dates')
                  ,(diff_qtys, 'change qtys' )
                  ,(diff_cost, 'change cost' )) AS ca1(diff_rn, changes)
     left join trans_cte on ca1.diff_rn = trans_cte.rn
     where diff_rn is not null
     or diffs_cte.rn = 1
     order by
        diffs_cte.TransNo
       ,diffs_cte.rn
       ,diff_rn

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39604970
are you are performing this in a loop, or are you going to be doing this across multiple transno as a big query?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39605009
it occurs due to the expansion (by 3) of all rows, but rn 1 is a special case and to compensate I now also assign a number to each cross applied row to select by.
| TRANSNO | TRANSTYPE |  TRANSDATE |  PROD | QTY | COST |     CHANGES |
|---------|-----------|------------|-------|-----|------|-------------|
|  Purch1 |   Invoice | 2004-02-15 | Prod1 |  52 |  200 |      (null) |
|  Purch1 |   Payment | 2004-02-15 | Prod1 | -52 |  200 | change qtys |
|  Purch1 |   Payment | 2004-02-15 | Prod1 |  10 |  200 | change qtys |
|  Purch1 |   Payment | 2004-02-15 | Prod1 |  16 |  100 | change qtys |
|  Purch1 |   Payment | 2004-02-15 | Prod1 |  16 |  100 | change cost |



-- Create Virtual table
   DECLARE @PurchTransactions TABLE(TransNo VARCHAR(20), TransType VARCHAR(20), TransDate DATE, Prod VARCHAR(20),Qty INT,Cost FLOAT)

-- Insert Test Data
 INSERT INTO @PurchTransactions SELECT 'Purch1','Invoice','20040215','Prod1',52,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',-52,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',10,200
 INSERT INTO @PurchTransactions SELECT 'Purch1','Payment','20040215','Prod1',16,100


-- Display Tables
--  select * from @PurchTransactions

-- Script that does the Job:
;WITH
  trans_cte AS (
                SELECT TransNo
                      ,TransType
                      ,TransDate
                      ,Prod
                      ,Qty
                      ,Cost
                      ,row_number() over (partition BY TransNo ORDER BY TransDate ASC) AS rn
                    FROM @PurchTransactions
                )
SELECT
       diffs.TransNo
      ,coalesce(trans_cte.TransType, diffs.TransType) AS TransType
      ,coalesce(trans_cte.TransDate, diffs.TransDate) AS TransDate
      ,coalesce(trans_cte.Prod     , diffs.Prod     ) AS Prod
      ,coalesce(trans_cte.Qty      , diffs.Qty      ) AS Qty
      ,coalesce(trans_cte.Cost     , diffs.Cost     ) AS Cost
      ,CASE WHEN trans_cte.rn > 1 THEN ca1.changes ELSE NULL END AS changes
FROM (
                 SELECT t.TransNo
                       ,t.TransType
                       ,t.TransDate
                       ,t.Prod
                       ,t.Qty
                       ,t.Cost
                       ,t.rn
                       ,CASE WHEN nxt.TransDate <> t.TransDate THEN nxt.rn ELSE NULL END AS diff_date
                       ,CASE WHEN nxt.qty       <> t.qty       THEN nxt.rn ELSE NULL END AS diff_qtys
                       ,CASE WHEN nxt.cost      <> t.cost      THEN nxt.rn ELSE NULL END AS diff_cost
                    FROM trans_cte AS t
                    LEFT JOIN trans_cte AS nxt ON t.TransNo = nxt.TransNo AND (t.rn + 1) = nxt.rn
     ) AS diffs
     CROSS APPLY (
                  VALUES
                         (1, diff_date, 'change dates')
                        ,(2, diff_qtys, 'change qtys' )
                        ,(3, diff_cost, 'change cost' )
                 ) AS ca1(ca_rn, diff_rn, changes)
     LEFT JOIN trans_cte ON ca1.diff_rn = trans_cte.rn
WHERE diff_rn IS NOT NULL
OR (diffs.rn = 1 AND ca_rn = 1)

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 39606225
with cte as
(select t1.transno, t1.transtype, t1.transdate t1date, t1.prod, t1.qty t1qty, t1.cost t1cost,  t2.transdate t2date, t2.qty t2qty, t2.cost t2cost from
 (select t.*, row_number() over (partition by transno, prod order by transdate) rn
  from trans t) t1
 left join
 (select t.*, row_number() over (partition by transno, prod order by transdate) rn
  from trans t) t2
 on t1.transno = t2.transno
 and t1.prod = t2.prod
 and t1.rn = t2.rn + 1
 order by t1.transtype, t1.transdate)
select cte.transno, cte.transtype, cte.t1date, cte.t1qty, cte.t1cost, null as changes
from cte where cte.transtype = 'Invoice'
union
select cte.transno, cte.transtype, cte.t1date, cte.t1qty, cte.t1cost, 'change dates'
from cte where cte.transtype = 'Payment' and cte.t1date <> cte.t2date
union
select cte.transno, cte.transtype, cte.t1date, cte.t1qty, cte.t1cost, 'change qtys'
from cte where cte.transtype = 'Payment' and cte.t1qty <> cte.t2qty
union
select cte.transno, cte.transtype, cte.t1date, cte.t1qty, cte.t1cost, 'change cost'
from cte where cte.transtype = 'Payment' and cte.t1cost <> cte.t2cost
;
0
 
LVL 31

Expert Comment

by:awking00
ID: 39606233
I'm always forgetting the AS keyword for table aliases in SQL Server -
...
from trans t) AS t1
and
...
from trans t) AS t2
0
 

Author Comment

by:rayluvs
ID: 39606271
It worked, but to answer yu question "...are you are performing this in a loop, or are you going to be doing this across multiple transno as a big query?":

No, we are not performing in a loop but we are running only this query for the results as describes in the initial question.
Don't understand by "across multiple transno as a big query", but if you refer to a big table with multiples transactions and running this script on it, then yes.

Based on the above, do we need to change anything on the script to run it on a table with multiple transactions?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39607311
>>Based on the above, do we need to change anything on the script to run it on a table with multiple transactions?
No, I was just curious because you were using a table variable, so it looked like it was established for a loop.
0
 

Author Comment

by:rayluvs
ID: 39608621
Understood.  We use the variable table just to give sample data to EE in order to help EE help us.

Prior closing the question, we would like a bit explanation on how you got the additional rows based on the existing table row contents.  We are googling the info but would greatly appreciate your input on explaining your script.  Analyzing your script, you used:

;WITH
3 separate SELECT in your query
finally,  CROSS APPLY

You don't have to give us a details explanation, don't want you to invest more of your valuable time already done, but a brief explanation to guide us to understand your script.

Thanx in advance.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39608749
"with" is used to declare a "common table expression" (CTE)
see: http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx
these are re-usable components in the query that follows, and here it is used to minimize the number scans performed on the base table. (you might think of it as being similar to a temp table without having to declare it as a table)

the first select creates the CTE

the third (innermost) select gathers the required data together
this also contains a left join reusing the CTE to provide a comparison of numbers that is needed to calculate what is different between one row of the base table and another.

the second select sits above the third and refines the result, including the filter to dispense with generated rows (from the cross apply) that are not relevant in the final outcome. Here we also re-use the CTE again via a left join based on the values provided by the innermost subquery.

The cross apply is used to multiply all rows (of the third select) by 3, this is achieved by use of "VALUES" and each row of values is a row of output. This is an alternative technique to the "unpivot" feature; using "values" achieves much the same outcome but in a much more convenient form for this query. The following link
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
explores the efficiency of this technique and presents results showing it to be slightly faster than unpivot and with a lower CPU usage..
0
 

Author Comment

by:rayluvs
ID: 39613395
Sorry for the delay.  Thank you very much!
0
 

Author Closing Comment

by:rayluvs
ID: 39613401
Thanx!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39613419
A pleasure, thanks for the grading. Cheers, Paul.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

23 Experts available now in Live!

Get 1:1 Help Now