Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2014-03-11
8
Medium Priority
?
415 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 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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