MSSQL Query/View Single Line Output For Combined Multiple Data Elements
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
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
ASKER
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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 ...
ASKER
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
Does this help & make sense? Please let me know...Thanks!...Mark
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 ...
ASKER
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!
Can anyone else please help & chime in here? Thank You!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SHARATH...BRILLIANT!!! OMG...thank you sooooo much!!! It's very much appreciated, and this is *precisely* what we wanted/needed.
Again...many thanks!...Mark
Again...many thanks!...Mark
this sounds like you want a specifc "pivot" query, build dynamically based on the number of credit cards in the db. ...
please clarify