Solved

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

Posted on 2014-03-11
8
398 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]
ID: 39923267
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
ID: 39923787
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]
ID: 39923807
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
ID: 39923826
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
Zoho SalesIQ

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

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923911
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
ID: 39923925
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
ID: 39927183
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
ID: 39927448
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

21 Experts available now in Live!

Get 1:1 Help Now