Solved

MSSQL Consolidate Records in Select Statement

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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