Solved

Rollup Query

Posted on 2011-02-14
18
263 Views
Last Modified: 2012-05-11
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
Comment
Question by:basile
  • 9
  • 6
  • 3
18 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34891531
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
 
LVL 1

Author Comment

by:basile
ID: 34891961
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34892105
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34892198
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34897534
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34900298
>> 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
 
LVL 1

Author Comment

by:basile
ID: 34911078
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
 
LVL 1

Author Comment

by:basile
ID: 34911138
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34912460
What is the result with your last query?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:basile
ID: 34913180
everything is in one line.

ex.

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

all accounts are returned in one row.....
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34913202
What is the expected result?
0
 
LVL 1

Author Comment

by:basile
ID: 34913359
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34913414
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
 
LVL 1

Author Comment

by:basile
ID: 34913574
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
 
LVL 1

Author Comment

by:basile
ID: 34913595
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
 
LVL 1

Author Comment

by:basile
ID: 34913602
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34913651
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
 
LVL 1

Author Closing Comment

by:basile
ID: 34918935
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now