Solved

MSSQL Query/View Single Line Output For Combined Multiple Data Elements

Posted on 2014-03-11
8
396 Views
Last Modified: 2014-03-13
HELLO...

Consider the following (2) Tables in MSSQL:

1. TENDERED --> Primary Key = DATE / DOC_NO / PAYMENT_SEQ_NO

DATE
DOC_NO
PMNT_SEQ_NO
PAYCODE_TYPE
AMOUNT

2. TENDERED_CR_CARD -->Primary Key = DATE / DOC_NO / PAYMENT_SEQ_NO

DATE
DOC_NO
PMNT_SEQ_NO
CR_CARD_NO_MASKED
CR_CARD_NAME
CR_CARD_EXP_DATE

These two tables are certainly related, based on their Primary Key values.

Now, consider the following data in those two tables:

DATE              DOC_NO      PMNT_SEQ_NO      PAYCODE_TYPE      AMOUNT
03/10/2014      100001      1                              CASH                     100.00
03/10/2014      100001      2                              CASH                        -9.75
03/10/2014      100002      1                              CASH                       50.00
03/10/2014      100002      2                              VISA                             100.00
03/10/2014      100002      3                              VISA                      250.00
03/10/2014      100003      1                              MC                             125.00
03/10/2014      100003      2                              AMEX                       75.00

DATE            DOC_NO PMNT_SEQ_NO  CR_CARD_MASKED   NAME                   CR_CARD_EXP
03/10/2014  100002   2                          4225******801289  MARY JONES  2016/08/31
03/10/2014  100002   3                                4121******637442  JOHN DOE           2015/04/30
03/10/2014  100003   1                                5428******971134  MIKE BAKER  2018/09/30
03/10/2014  100003   2                                3732*****344756    LINDA LIU           2017/07/31


OK...so what we NEED...is a Combined, SINGLE RECORD Audit Report type query.  The resulting query should show, based on the Data from above, the SINGLE LINE represented in the Attached Spreadsheet.  NOTE...what's important to point out here..is that ONLY the 'CASH' Tender gets "summed"...EACH INDIVIDUAL Credit Card record MUST have its own Field...as represented in the corresponding Columns of the Spreadsheet (i.e. PMT_TYP_1, AMT_1, PMT_TYP_2, AMT_2, and so forth).

PLEASE HELP!  Any suggestions/advice would be most appreciated!  Thank You!...Mark

PAYMENTS-AUDIT-QUERY-OUTPUT.xlsx
0
Comment
Question by:datatechcorp
  • 4
  • 3
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
is the number of credit cards "fixed", means know in advance?
this sounds like you want a specifc "pivot" query, build dynamically based on the number of credit cards in the db. ...
please clarify
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Guy...

Thank you for responding.  No, the number of credit cards is NOT known in advance.  In essence, a customer may make a purchase, for example, "splitting" their purchase across 2, 3, or 4 credit cards (say $100, splitting down to $25/each)...OR...they may desire to put their entire purchase on only 1 credit card...and so forth.

The Front-End Point of Sale software that processes and stores this data, is capable of, and allows for, multiple "splits" of Tenders...so there may be a mixed-bag of 'CASH', 'VISA', 'AMEX', and even 'GIFTCARD' tendering...against the same DOC_NO.

I hope this helps you and makes sense...Thanks!...Mark
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
and why does the "audit" report this in the columned-typed output? maybe some other output formatting would be "ok"? like having 1 "row" for the transaction, and in the other columns have 4 splitted cells for each credit card, if any. like in excel or html, you can "merge" some cells ...
0
 

Author Comment

by:datatechcorp
Comment Utility
I think we're more or less on the same page here.  The key is...having on ONE record of the query output...for each DOC_NO.  IF only one payment tender EXISTS for a given DOC_NO...then ONLY the 'PMT_TYP_1', 'AMT_1', and corresponding '1' fields would be populated with data from the 'TENDERED' table.  IF there are MULTIPLE tenders, THEN the 'PMT_TYP_2', 'AMT_2', and so on...fields...would get populated.  OTHERWISE, they all get an 'N/A' in those columns.

Does this help & make sense?  Please let me know...Thanks!...Mark
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think you should then check out the experts-exchange or google search for sql server group concat stuff, this should give you a good starter. I am about to leave my pc for the night, so you might try to work this out meanwhile ...
0
 

Author Comment

by:datatechcorp
Comment Utility
We don't know the syntax and/or what else would be necessary...sorry, we're not SQL Server Programmers...that's why we reached out to Experts Exchange.

Can anyone else please help & chime in here?  Thank You!
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
try this query.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
  DROP TABLE #temp 

SELECT T1.[DATE], 
       T1.DOC_NO, 
       T1.PAYCODE_TYPE, 
       MIN(T1.PMNT_SEQ_NO) PMNT_SEQ_NO, 
       SUM(T1.AMOUNT)   AMOUNT, 
       T2.CR_CARD_NO_MASKED, 
       T2.CR_CARD_NAME, 
       T2.CR_CARD_EXP_DATE 
  INTO #Temp
  FROM TENDERED T1 
       LEFT JOIN TENDERED_CR_CARD T2 
              ON T1.[DATE] = T2.[DATE] 
                 AND T1.DOC_NO = T2.DOC_NO 
                 AND T1.PMNT_SEQ_NO = T2.PMNT_SEQ_NO 
 GROUP BY T1.[DATE], 
          T1.DOC_NO, 
          T1.PAYCODE_TYPE, 
          T2.CR_CARD_NO_MASKED, 
          T2.CR_CARD_NAME, 
          T2.CR_CARD_EXP_DATE

declare @sql nvarchar(max)

select @sql = rtrim(substring(isnull((select ',max(case PMNT_SEQ_NO when ' + CONVERT(VARCHAR,PMNT_SEQ_NO) + ' then PAYCODE_TYPE end) PAYCODE_TYPE_' + CONVERT(VARCHAR,PMNT_SEQ_NO) +
									  ',max(case PMNT_SEQ_NO when ' + CONVERT(VARCHAR,PMNT_SEQ_NO) + ' then AMOUNT end) AMOUNT_' + CONVERT(VARCHAR,PMNT_SEQ_NO) +
									  ',max(case PMNT_SEQ_NO when ' + CONVERT(VARCHAR,PMNT_SEQ_NO) + ' then CR_CARD_NO_MASKED end) CR_CARD_NO_MASKED_' + CONVERT(VARCHAR,PMNT_SEQ_NO) +
									  ',max(case PMNT_SEQ_NO when ' + CONVERT(VARCHAR,PMNT_SEQ_NO) + ' then CR_CARD_NAME end) CR_CARD_NAME_' + CONVERT(VARCHAR,PMNT_SEQ_NO) +
									  ',max(case PMNT_SEQ_NO when ' + CONVERT(VARCHAR,PMNT_SEQ_NO) + ' then CR_CARD_EXP_DATE end) CR_CARD_EXP_DATE_' + CONVERT(VARCHAR,PMNT_SEQ_NO)
                                 from (select distinct PMNT_SEQ_NO from #Temp) T1 for xml path('')),' '),2,4000))

select @sql = 'SELECT [Date],DOC_NO,' + @sql + '
                 FROM #temp
                GROUP BY [Date],DOC_NO'
exec(@sql)

Open in new window


Tested here with your sample data.
http://sqlfiddle.com/#!3/b80e3/1
0
 

Author Comment

by:datatechcorp
Comment Utility
SHARATH...BRILLIANT!!!  OMG...thank you sooooo much!!!  It's very much appreciated, and this is *precisely* what we wanted/needed.

Again...many thanks!...Mark
0

Featured Post

Zoho SalesIQ

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

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

19 Experts available now in Live!

Get 1:1 Help Now