Solved

MSSQL Consolidate Records in Select Statement

Posted on 2012-12-21
1
298 Views
Last Modified: 2012-12-21
Can someone help me construct a MSSQL statement that will return data in a consolidated form?  The MSSQL command that I'm currently using currently returns one row per customer per vendor as follows:

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel,CUSTOMER.LastName, CUSTOMER.FirstName, crm_customer_purchaseHistoryBuckets.LifetimeSalesDollars,custsls.vendornum,sum(priceextension) as sales,count(priceextension) as trcount
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
INNER JOIN CUSTOMER ON crm_Customer_Employee_Assignments.CustomerNum = CUSTOMER.CustomerNum
INNER JOIN crm_customer_purchaseHistoryBuckets ON crm_customer_purchaseHistoryBuckets.CustomerNum=crm_Customer_Employee_Assignments.CustomerNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
WHERE custsls.transdate>='2000-1-1' and custsls.transdate<='2012-12-21' AND (custsls.vendornum=3654 or custsls.vendornum=11691 or custsls.vendornum=62242) group by crm_Customer_Employee_Assignments.customernum,custlevel,CUSTOMER.LASTNAME,CUSTOMER.FIRSTNAME,custsls.vendornum,crm_customer_purchaseHistoryBuckets.LifetimeSalesDollars


This SQL statment returns data in the following manner....

<cust>     <lvl>     <fname>   <lname> <life$>      <vend#>  <vend$>  <vendct>
12345     Gold     Edward      Smith     1234.56     3654        50             3
12345     Gold     Edward      Smith     1234.56     62242      89             2
54545     Silver    Frank         Roger     1053.54     3654        80            5
54545     Silver    Frank         Roger     1053.54     11691      100          3
54545     Silver    Frank         Roger     1053.54     62242       54           5
89817     Gold      Jim            Jim         2432.22     11691      111          4

What I would like to do is to return one row per customer with vendor totals on a single row (return either 0 or null if there are no purchases for a particular vendor.  For instance, the data above would be returned as follows:

<c#>    <lvl>   <fn>      <ln>     <$$$>    <v$>    <vc>     <v$>    <vc>     <v$>   <vc>
-------------------------------------------------------------------------------------------------------------------------------------------
12345  Gold    Edward  Smith  1234.56  50        3          89        2         <null>  <null>
54545  Silver  Frank     Roger  1053.54  80        5          54        5          100      3
89817  Gold    Jim        Jim      2432.22  <null>  <null>  <null>  <null>  111      4

Is this possible???
0
Comment
Question by:trbbhm
[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
1 Comment
 

Accepted Solution

by:
trbbhm earned 0 total points
ID: 38714421
Solved my own problem by using a SQL Function:

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel,CUSTOMER.LastName, CUSTOMER.FirstName, crm_customer_purchaseHistoryBuckets.LifetimeSalesDollars,
[dbo].[VendorDolsTotalTimeFrame](3654,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as dols3654,
[dbo].[VendorTransCountTotalTimeFrame](3654,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as tc3654,
[dbo].[VendorDolsTotalTimeFrame](11691,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as dols11691,
[dbo].[VendorTransCountTotalTimeFrame](11691,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as tc11691,
[dbo].[VendorDolsTotalTimeFrame](62242,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as dols62242,
[dbo].[VendorTransCountTotalTimeFrame](62242,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') as tc62242
FROM crm_Customer_Employee_Assignments
INNER JOIN CUSTOMER ON crm_Customer_Employee_Assignments.CustomerNum = CUSTOMER.CustomerNum
INNER JOIN crm_customer_purchaseHistoryBuckets ON crm_customer_purchaseHistoryBuckets.CustomerNum=crm_Customer_Employee_Assignments.CustomerNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
where [dbo].[VendorDolsTotalTimeFrame](3654,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21')>0
or [dbo].[VendorDolsTotalTimeFrame](11691,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21') >0
or [dbo].[VendorDolsTotalTimeFrame](62242,crm_Customer_Employee_Assignments.CustomerNum,'2000-1-1','2012-12-21')>0
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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