Solved

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

Posted on 2014-03-11
8
406 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

617 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