Getting data/rows to line up after running query

Question

Post reply

Getting data/rows to line up after running query

bkana

Valued Member

Points: 67
More actions
August 6, 2010 at 7:37 am

#387519

Hello all,

I'm running a query against 2 invocie tables and need the results or rows to line up.I know there might be a simple way to achive this, but I can't figure it out and it is driving me nuts. I have this query:

SELECT FilteredInvoice.contactidname, FilteredInvoiceDetail.description, FilteredInvoiceDetail.extendedamount,

FilteredInvoiceDetail.pa_paymentapplied, FilteredInvoice.pa_payments,

FilteredInvoiceDetail.pa_transactiondate, FilteredInvoice.pa_invoicedate, FilteredInvoiceDetail.invoiceid, FilteredInvoice.totalamount

FROM FilteredInvoice JOIN

FilteredInvoiceDetail ON FilteredInvoice.invoiceid = FilteredInvoiceDetail.invoiceid

WHERE (FilteredInvoiceDetail.pa_cancelled IS NULL) AND (FilteredInvoice.name = 'ABA Certification Annual Fee - New' OR

FilteredInvoice.name = 'ABA Certification Annual Fee - renew')

ORDER BY FilteredInvoice.contactidname

The results look something like this:

Name Description PaymentApplied

Jon Smith Certification they purchased $100

Jon Smith Certification they purchased $100

Jon Smith Certification they purchased

Jon Smith Cash Payment

Jon Smith

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

bkana Valued Member Points: 67 More actions · Answer 1

Sorry guys, hit the submit button too quickly, here's the whole issue:

Hello all,

I'm running a query against 2 invocie tables and need the results or rows to line up.I know there might be a simple way to achive this, but I can't figure it out and it is driving me nuts. I have this query:

SELECT FilteredInvoice.contactidname, FilteredInvoiceDetail.description, FilteredInvoiceDetail.extendedamount,

FilteredInvoiceDetail.pa_paymentapplied, FilteredInvoice.pa_payments,

FilteredInvoiceDetail.pa_transactiondate, FilteredInvoice.pa_invoicedate, FilteredInvoiceDetail.invoiceid, FilteredInvoice.totalamount

FROM FilteredInvoice JOIN

FilteredInvoiceDetail ON FilteredInvoice.invoiceid = FilteredInvoiceDetail.invoiceid

WHERE (FilteredInvoiceDetail.pa_cancelled IS NULL) AND (FilteredInvoice.name = 'ABA Certification Annual Fee - New' OR

FilteredInvoice.name = 'ABA Certification Annual Fee - renew')

ORDER BY FilteredInvoice.contactidname

The results look something like this:

Name Description PaymentApplied Payments

Jon Smith Certification they purchased $100

Jon Smith Certification they purchased

Jon Smith Cash Payment $100

I need the payment amounts on the same line with the rest of the information. Also, similiar names have a unique identifier associated with them. I would even consider making a new view, if feasible.

Thanks in advance!

Bill

Lowell SSC Guru Points: 323482 More actions · Answer 2

formatting shouldn't really be done in the SQL if at all possible, but you can still do it.

one easy way is to convert the results to the right CHAR size:



SELECT CONVERT(CHAR(32),FilteredInvoice.contactidname) AS  FilteredInvoice.contactidname...

here's another example:



--get the longest name

declare @maxLen int

select @maxLen = MAX(LEN(FilteredInvoice.contactidname))

FROM FilteredInvoice

--ad a 2 char buffer for aesthetic reasons

SET @maxLen = @maxLen + 2

--force the results to fit in the max space plus 2

SELECT FilteredInvoice.contactidname + SPACE(@maxLen - LEN(FilteredInvoice.contactidname)) As contactidname, FilteredInvoiceDetail.description, FilteredInvoiceDetail.extendedamount,

FilteredInvoiceDetail.pa_paymentapplied, FilteredInvoice.pa_payments,

FilteredInvoiceDetail.pa_transactiondate, FilteredInvoice.pa_invoicedate, FilteredInvoiceDetail.invoiceid, FilteredInvoice.totalamount

FROM FilteredInvoice JOIN

FilteredInvoiceDetail ON FilteredInvoice.invoiceid = FilteredInvoiceDetail.invoiceid

WHERE (FilteredInvoiceDetail.pa_cancelled IS NULL) AND (FilteredInvoice.name = 'ABA Certification Annual Fee - New' OR

FilteredInvoice.name = 'ABA Certification Annual Fee - renew')

ORDER BY FilteredInvoice.contactidname

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bkana Valued Member Points: 67 More actions · Answer 3

Hi Lowell,

Thanks for the quick reply, I will try as you suggested and get back with you. I am a little unclear as to your first suggestion - what will that CONVERT char statement do?

Also, if your not supposed to do formatting in SQL, are there other methods to achieve what I need? What about cfeating a n new table or view.

One more question. Is it possible to add new columns when creating a view and then populating those columns with say an UPDATE command?

My thinking was that I could ceate a view from my query with 2 or 3 extra columns and then update those columns with the payment information from my base tables, that way the data would line up.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Bill

Your query benefits from the use of table aliases:

SELECT i.contactidname,



	d.description,

	d.extendedamount,

	d.pa_paymentapplied,

	i.pa_payments,

	d.pa_transactiondate,

	i.pa_invoicedate,

	d.invoiceid,

	i.totalamount

FROM FilteredInvoice i

INNER JOIN FilteredInvoiceDetail d ON i.invoiceid = d.invoiceid

WHERE d.pa_cancelled IS NULL

	AND i.name IN ('ABA Certification Annual Fee - New', 'ABA Certification Annual Fee - renew')

ORDER BY i.contactidname

Can you give an example of what you want your data to look like?

It would also help if you could knock up some sample data, see the link below on how to do this. Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bkana Valued Member Points: 67 More actions · Answer 5

Unfortunately, your suggestion didn’t produce the desired results, the Check/Cash payment line is still on a row by itself, along with the date associated with it. Is there another way to get the row “up” with its corresponding description? My problem is that my query produces 1 row for every detailed entry in my Invoice tables and I need the Check/Cash payment line (or at least the amount and date) to be on the same line as the product (description) it goes with. Hope that made sense). I’ve seen other sites that suggest using a “COLLASCE” statement.

bkana Valued Member Points: 67 More actions · Answer 6

bkana

Valued Member

Points: 67

August 6, 2010 at 8:38 am

#1203573

Will read through the instructions and post back with the samples, stand by.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

bkana (8/6/2010)

Will read through the instructions and post back with the samples, stand by.

Hi Bill, sample to help you.

-- create some sample data



DROP TABLE #FilteredInvoice

CREATE TABLE #FilteredInvoice (

	invoiceid,

	contactidname,

	name,

	pa_payments,

	pa_invoicedate,

	totalamount

)

INSERT INTO #FilteredInvoice (

	invoiceid, contactidname, name, pa_payments, pa_invoicedate, totalamount)

-- substitute the column names for realistic values which demonstrate the problem

SELECT 1, contactidname, 'ABA Certification Annual Fee - New', pa_payments, pa_invoicedate, totalamount UNION ALL

SELECT 2, contactidname, 'ABA Certification Annual Fee - renew', pa_payments, pa_invoicedate, totalamount UNION ALL

SELECT 3, contactidname, 'Excluded by filter', pa_payments, pa_invoicedate, totalamount 

DROP TABLE #FilteredInvoiceDetail

CREATE TABLE #FilteredInvoiceDetail (

	invoiceid,

	[description],

	extendedamount,

	pa_paymentapplied,

	pa_transactiondate,

	pa_cancelled

)

INSERT INTO #FilteredInvoiceDetail (

	invoiceid, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled)

-- substitute the column names for realistic values which demonstrate the problem

SELECT 1, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 1, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 1, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 1, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 1, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 2, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 2, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 2, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 2, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled UNION ALL

SELECT 3, [description], extendedamount, pa_paymentapplied, pa_transactiondate, pa_cancelled 

-- run the query

SELECT i.contactidname,

        d.[description],

        d.extendedamount,

        d.pa_paymentapplied,

        i.pa_payments,

        d.pa_transactiondate,

        i.pa_invoicedate,

        d.invoiceid,

        i.totalamount

FROM #FilteredInvoice i

INNER JOIN #FilteredInvoiceDetail d ON i.invoiceid = d.invoiceid

WHERE d.pa_cancelled IS NULL

        AND i.name IN ('ABA Certification Annual Fee - New', 'ABA Certification Annual Fee - renew')

ORDER BY i.contactidname

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 8

bkana (8/6/2010)

Also, if your not supposed to do formatting in SQL, are there other methods to achieve what I need? .

SQL is generally for data retrieval not display. Formatting should be done in the presentation layer, whatever mechanism your users are accessing the data with: an application interface, a pdf file, a web page, etc.

I am not a zealot about this. I will do some formatting in SQL in certain circumstances, if I am using SQL mail or in certain complicated reporting circumstances.

Lowell SSC Guru Points: 323482 More actions · Answer 9

based on his latest description, i think the issue has nothing to do with formatting...it's the master data is on row 1 of the results, but he wants rows 2/3/etc of the child data on the same row.

we will definitely need the CREATETABLE/INSERT INTO data to go witht he scipt he posted so we can see the "wrong" results, and make recommendations.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bkana Valued Member Points: 67 More actions · Answer 10

bkana

Valued Member

Points: 67

August 6, 2010 at 10:35 am

#1203664

Having somer issues creating the sample database for the correct display on this site, please forgive me. I have attached a .doc to illustrate my issue. I hope this will suffice.