Solved

Display additional rows based on rows contents in Ms Sql

Posted on 2013-10-27
20
358 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
[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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

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 32

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot tables in SQL 1 37
Limit number of characters returned to 999 9 33
Database maintenance 36 110
Converting Stored Procedure to SQL Statement 5 44
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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