Solved

Rollup Query

Posted on 2011-02-14
18
267 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:Huseyin KAHRAMAN
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:Huseyin KAHRAMAN
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 41

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:Huseyin KAHRAMAN
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 41

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 41

Expert Comment

by:Sharath
ID: 34912460
What is the result with your last query?
0
 
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 41

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 41

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 41

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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