April 24, 2008 at 1:04 pm
I am trying to write a report in VS that will show me information on an account. I wanna know on an account what they paid last year, and then what we billed this year for a certain product. This report gives me the result set but I need to have the information across one line in a table. I would like account, 2007 paid, 2008 billed, and type of member across one line. What happens now is account, then only one or the other of 2007 paid and 2008 billed is filled out (the other is blank) and then type of member. Then there is a second line under that one with the same account, and whichever one was blank in the previous line is listed on this line, and the one that was listed previously is now blank, and then the membershiptype again. This sounds confusing, and I apologize. The 2007 paid column is pulling from a different table then the 2008 billed column. This is a code example. Any ideas on how to get it across 1 line?
SELECT CRMAF_Order.accountidname AS Company, CRMAF_Account.new_membershiptypename AS MemberType,
CASE WHEN crmaf_order.name = 'membership 2008' THEN crmaf_order.totalamount END AS Billed08,
CASE WHEN crmaf_invoice.name = 'membership 2007' THEN crmaf_invoice.new_paidamount END AS paid07, CRMAF_Order.name
FROM FilteredSalesOrder CRMAF_Order INNER JOIN
FilteredAccount CRMAF_Account ON CRMAF_Order.accountid = CRMAF_Account.accountid FULL OUTER JOIN
FilteredInvoice CRMAF_Invoice ON CRMAF_Order.salesorderid = CRMAF_Invoice.SalesOrderId
WHERE (CRMAF_Order.name = N'membership 2007') OR
(CRMAF_Order.name = N'membership 2008')
ORDER BY CRMAF_Order.accountidname
Visual studio automatically formats it funky, and then when I paste it into here it doesnt line up properly, but you get the idea. If you would like me to clean it up, let me know. Thanks guys.
April 24, 2008 at 1:54 pm
Try adding an Else statement to each of your cases. See what's in that column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2008 at 2:02 pm
If I include an ELSE in my CASE statements then all of those 'empty' rows return whatever is in my ELSE. I still get back 2 seperate rows. The ones that are empty for the most part do not have records created at all. For example, the paid invoices that are returning my ELSE, the accounts that are associated with that row do not have a paid invoice for that year. So now in addition to concatenating the information into 1 row, I need to know how would you populate an empty field on a report that is not null, but returns no data on the report because a record doesn't exist? I mean the ELSE works but is there another way to go about it?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply