Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-03-11
8
Medium Priority
?
409 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
[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
  • 4
  • 3
8 Comments
 
LVL 143

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 143

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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
 
LVL 143

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 41

Accepted Solution

by:
Sharath earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

721 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