Solved

MSSQL Consolidate Records in Select Statement

Posted on 2012-12-21
1
302 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

622 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