Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL Consolidate Records in Select Statement

Posted on 2012-12-21
1
Medium Priority
?
305 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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