• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Rollup Query

Hello EE World,

I'm trying to figure out the best way to write up a query to do the following.

let's say in a table i have a customer for each customer they will buy a certain number of services and each will have an expiration date. what i want to get on the same line is:

Count followed by a semi colon of each service and expiration date:

4;product1, 01/01/2011;product2, 01/20/2011; product3, 02/28/2011;product4,04/01/2011

column names are product, exp date the count is just calculated from how many products the client may have purchased.......

0
Auerelio Vasquez
Asked:
Auerelio Vasquez
  • 9
  • 6
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
any limit on product? do you want this for all customers or for one customer only?

is a function ok? like

select dbo.GetExpList(CustomerID) from Customers

why do you want this? it is probably easier on client side and just get a list of

ProductID ExpDate
product1   01/01/2011
product2   01/20/2011
...

and do the rest on client app...
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
this is being pulled from the cloud. they want this data, so i can wrap it into a package, and export to a .csv file, and they can upload that file. it's not that i need it in this format.

ProductID ExpDate
product1   01/01/2011
product2   01/20/2011

it's like this:
4;product1, 01/01/2011;product2, 01/20/2011; product3, 02/28/2011;product4,04/01/2011

they will want this for a dashboard.
0
 
HainKurtSr. System AnalystCommented:
something like this

remove with p as (...) and replace p with your table name...

with p as (...) is used for demo data...
with p as (
select '4' EmpID, 'product1' productid, '01/01/2011' ExpDate
union
select '4', 'product2', '01/20/2011'
union
select  '4', 'product3', '02/28/2011'
union
select  '4', 'product4','04/01/2011'
) 
select EmpID + ';' + Products from (
select distinct EmpID,
 STUFF((select ';' + productid + ',' + ExpDate  FROM p p2 WHERE p2.EmpID = p1.EmpID FOR XML PATH('')), 1, 1, '') AS Products
 from p p1
 ) x

4;product1,01/01/2011;product2,01/20/2011;product3,02/28/2011;product4,04/01/2011

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Are you looking for this?
select convert(varchar,COUNT(*))+';'+
       STUFF((select ';' + productid + ',' + convert(varchar,ExpDate)  FROM your_table p2 FOR XML PATH('')), 1, 1, '') AS Products
  from your_table

Open in new window

0
 
HainKurtSr. System AnalystCommented:
although the previos post is same as the one I posted, here again with demo data replaced with your table in case of any confusion

select EmpID + ';' + Products from (
select distinct EmpID,
 STUFF((select ';' + productid + ',' + ExpDate  FROM your_table p2 WHERE p2.EmpID = p1.EmpID FOR XML PATH('')), 1, 1, '') AS Products
 from your_table p1
 ) x

0
 
SharathData EngineerCommented:
>> although the previos post is same as the one I posted, here again with demo data replaced with your table in case of any confusion

Not exactly. basile asked to have the count followed by semi colon of each service and expiration date. Hence I tweaked it to have count(*) in the result set.
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Ok, back to this. I've tried it like this:

WITH    cte
          AS ( SELECT   o.AccountId AS AccountID ,
                        A.Name AS ProductName ,
                        P.ProductCode AS ProductCode ,
                        P.Bolt_On__c AS BoltOn ,
                        CONVERT(NVARCHAR(10), SA.Service_End_Date__c, 101) AS ServiceAccessEndDate
               FROM     dbo.Opportunity o
                        INNER JOIN dbo.OpportunityLineItem ol ON o.Id = ol.OpportunityId
                        INNER JOIN dbo.PricebookEntry PE ON ol.PricebookEntryId = PE.Id
                        INNER JOIN dbo.Product2 P ON P.Id = PE.Product2Id
                        INNER JOIN dbo.Service_Access__c SA ON SA.Renewal_Opportunity__c = o.Id
                        INNER JOIN Account A ON o.AccountId = A.Id
               WHERE    P.Practice__c = 'MM'
                        AND o.Opportunity_Type__c = 'Renewal'
                        AND o.StageName IN ( 'Undiagnosed', 'Pre-Qualified',
                                             'Proposed', 'Qualified' )
             )
    SELECT  AccountID ,
            ProductName
    FROM    cte
SELECT  CONVERT(VARCHAR, COUNT(*)) + ';'
        + STUFF(( SELECT    ';' + ProductName + ','
                            + CONVERT(VARCHAR, ServiceAccessEndDate)
                  FROM      CTE p2
                FOR
                  XML PATH('')
                ), 1, 1, '') AS ProductName
FROM    CTE

it's not getting the right result set, i'm probably using the wrong cte in the xml part....
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
last remark i made should have had this: it works, but it coutns ALL the data on one line, so i get 4383,ProductCode all in one line. I want one line for each unique accountID. there maye be on more than one line, Account ID Like this:

ACCT1234,PROD1,01/01/2011
ACCT1234,PROD2,01/09/2011
ACCT1234,PROD3,01/10/2011

so, i want one the one line

3,ACCT1234,01/01/2011;PROD2,01/09/2011;PROD3,01/10/2011

and there are many accountIDs....

does the query make sense?

USE SFDC_REPLICA
GO
WITH    cte
          AS ( SELECT   o.AccountId AS AccountID ,
                        A.Name AS ProductName ,
                        P.ProductCode AS ProductCode ,
                        P.Bolt_On__c AS BoltOn ,
                        CONVERT(NVARCHAR(10), SA.Service_End_Date__c, 101) AS ServiceAccessEndDate
               FROM     dbo.Opportunity o
                        INNER JOIN dbo.OpportunityLineItem ol ON o.Id = ol.OpportunityId
                        INNER JOIN dbo.PricebookEntry PE ON ol.PricebookEntryId = PE.Id
                        INNER JOIN dbo.Product2 P ON P.Id = PE.Product2Id
                        INNER JOIN dbo.Service_Access__c SA ON SA.Renewal_Opportunity__c = o.Id
                        INNER JOIN Account A ON o.AccountId = A.Id
               WHERE    P.Practice__c = 'MM'
                        AND o.Opportunity_Type__c = 'Renewal'
                        AND o.StageName IN ( 'Undiagnosed', 'Pre-Qualified',
                                             'Proposed', 'Qualified' )
             )
    SELECT  CONVERT(VARCHAR, COUNT(*)) + ';'
            + STUFF(( SELECT    ';' + AccountID + ','
                                + CONVERT(VARCHAR, ServiceAccessEndDate)
                      FROM      cte p2
                    FOR
                      XML PATH('')
                    ), 1, 1, '') AS ProductName
    FROM    cte
0
 
SharathData EngineerCommented:
What is the result with your last query?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
everything is in one line.

ex.

4383;acct1234,prod1,01/01/2011;next product

all accounts are returned in one row.....
0
 
SharathData EngineerCommented:
What is the expected result?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
one account, with all products per line and the count at the beginning of each line:

currently each account, product and date are showing up one per line

this is how it shows now in the db each is a separate column

account product# expiredate
acct1234 product1 01/01/2011
acct1234 product2 01/01/2011
acct1234 product3 01/01/2011
acct2345 product1 01/01/2011
acct2345 product2 01/01/2011
acct3456 product1 01/01/2011

i would like it to be concatenated on one line for each disticnt account, with the count as the first item

4;acct1234,prod1.01/01/2011;acct1234,prod2,01/01/2011
2;acct2345,product1,01/01/2011;product2,01/01/20111
1;acct3456 product1, 01/01/2011

does that help ?
0
 
SharathData EngineerCommented:
try this.
WITH    cte
          AS ( SELECT   o.AccountId AS AccountID ,
                        A.Name AS ProductName ,
                        P.ProductCode AS ProductCode ,
                        P.Bolt_On__c AS BoltOn ,
                        CONVERT(NVARCHAR(10), SA.Service_End_Date__c, 101) AS ServiceAccessEndDate
               FROM     dbo.Opportunity o
                        INNER JOIN dbo.OpportunityLineItem ol ON o.Id = ol.OpportunityId
                        INNER JOIN dbo.PricebookEntry PE ON ol.PricebookEntryId = PE.Id
                        INNER JOIN dbo.Product2 P ON P.Id = PE.Product2Id
                        INNER JOIN dbo.Service_Access__c SA ON SA.Renewal_Opportunity__c = o.Id
                        INNER JOIN Account A ON o.AccountId = A.Id
               WHERE    P.Practice__c = 'MM'
                        AND o.Opportunity_Type__c = 'Renewal'
                        AND o.StageName IN ( 'Undiagnosed', 'Pre-Qualified',
                                             'Proposed', 'Qualified' )
             ),
             cte2 as (
    SELECT  distinct COUNT(*) OVER (PARTITION BY AccountID) Cnt,
             STUFF(( SELECT    ';' + AccountID + ','
                                + CONVERT(VARCHAR, ServiceAccessEndDate)
                      FROM      cte p2 where p1.AccountID = p2.AccountID 
                    FOR
                      XML PATH('')
                    ), 1, 1, '') AS ProductName
    FROM    cte p1)
    select CONVERT(varchar(10),Cnt)+';'+ProductName
      from cte2

Open in new window

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
It looks like this does work, but i have to do more testing on it tomorrow. Can you explain to me what Over, Partition and Stuff do, just in a nutshell? i have to try and see if i can apply this else where, as the DW is growing, and they are asking many questions like this, so it would be good if i had a better understanding, it looks to me preliminarily, like stuff, replaces an expresion. Also, how does the xml path play in? I'll let you know in the early morning if this works, but i do accept the solution.
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
ok so i modified the select and added one more column, product name, it's not stuffing a comma, between productnamee and serviceaccessenddate, can you help me with that ?
WITH    cte
          AS ( SELECT   o.AccountId AS AccountID ,
                        A.Name AS ProductName ,
                        P.ProductCode AS ProductCode ,
                        P.Bolt_On__c AS BoltOn ,
                        CONVERT(NVARCHAR(10), SA.Service_End_Date__c, 101) AS ServiceAccessEndDate
               FROM     dbo.Opportunity o
                        INNER JOIN dbo.OpportunityLineItem ol ON o.Id = ol.OpportunityId
                        INNER JOIN dbo.PricebookEntry PE ON ol.PricebookEntryId = PE.Id
                        INNER JOIN dbo.Product2 P ON P.Id = PE.Product2Id
                        INNER JOIN dbo.Service_Access__c SA ON SA.Renewal_Opportunity__c = o.Id
                        INNER JOIN Account A ON o.AccountId = A.Id
               WHERE    P.Practice__c = 'MM'
                        AND o.Opportunity_Type__c = 'Renewal'
                        AND o.StageName IN ( 'Undiagnosed', 'Pre-Qualified',
                                             'Proposed', 'Qualified' )
             ),
             cte2 as (
    SELECT  distinct COUNT(*) OVER (PARTITION BY AccountID) Cnt,
             STUFF(( SELECT    ';' + AccountID + ',' + ProductCode + 
                                + CONVERT(VARCHAR, ServiceAccessEndDate)
                      FROM      cte p2 where p1.AccountID = p2.AccountID 
                    FOR
                      XML PATH('')
                    ), 1, 1, '') AS ProductName
    FROM    cte p1)
    select CONVERT(varchar(10),Cnt)+';'+ProductName
      from cte2

Open in new window

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
i'm sorry, i meant in here

 cte2 as (
    SELECT  distinct COUNT(*) OVER (PARTITION BY AccountID) Cnt,
             STUFF(( SELECT    ';' + AccountID + ',' + ProductCode +
                                + CONVERT(VARCHAR, ServiceAccessEndDate)
                      FROM      cte p2 where p1.AccountID = p2.AccountID
                    FOR
                      XML PATH('')
                    ), 1, 1, '') AS ProductName
    FROM    cte p1)
    select CONVERT(varchar(10),Cnt)+';'+ProductName
      from cte2

i added productcode and serviceAccessEndDate ?
0
 
SharathData EngineerCommented:
check this
cte2 as (
    SELECT  distinct COUNT(*) OVER (PARTITION BY AccountID) Cnt,
             STUFF(( SELECT    ';' + AccountID + ',' + ProductCode + ',' 
                                + CONVERT(VARCHAR, ServiceAccessEndDate)
                      FROM      cte p2 where p1.AccountID = p2.AccountID 
                    FOR
                      XML PATH('')
                    ), 1, 1, '') AS ProductName
    FROM    cte p1)
    select CONVERT(varchar(10),Cnt)+';'+ProductName
      from cte2

Open in new window

0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Perfect! One last part of this, and i can post for more points as well. Can i take it now, and have the output be

AccoutnID(one columen) the rest of the information in another column

AccountID, PRODUCT NAME ?

so it would look like if there was on opportunity:

ACCT1234   1,Prod1,01/01/11

Now they want the accoutnID separated to a different column.

Thanks for a great response.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now