Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

roll up the data as per period between start and enddate Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:39 AM
Points: 22, Visits: 239
Hi

I have a query that returns aggregated data for a day between start and end date . Now My manager says he would send the start and end date and wants to rollup or aggregate the data to either month ,quarter or year as per the dates on the same data as per the parameter period(month,day,year ,quarter) that is sent .

Suppose he sends startdate = 06/20/2012 and enddate = 06/20/2013 and period = month then he wants to aggregate the data for every month Until the end date .
if period = quarter then he wants to see the aggregate for every 3months data from start to end date

below is the query

;with Vol_Factdata as

(Select CONVERT(char(10),EventStartDate,126) as Eventstartdate,
Agg.ProductTypeID,
Agg.ProductTypeName,
Agg.LoanProgramTypeId,
MIN(MetricValue) AS MetricValall,
SUM(SampleSize) as SampleSizeall

FROM [Reporting].[AggregatedRatesforWidgets] Agg with (NOLOCK)
WHERE Agg.EventStartDate >= @ipstartdate
and Agg.EventStartDate <=@ipenddate

and Agg.LoanProgramTypeID= @ipProducttypeID
GROUP BY CONVERT(char(10),agg.EventStartDate,126),Agg.ProductTypeID,Agg.ProductTypeName,Agg.LoanProgramTypeId)

Select
vcy.EventStartDate as EventStartDate,
vcy.ProductTypeID as ProductTypeID,
vcy.ProductTypeName as ProductTypeName,
vcy.LoanProgramTypeID AS LoanProgramTypeId,
'ALL' as Loanstate,
vcy.MetricValall_1year as MetricValue,
vcy.SampleSizeall_1year as SampleSize

FROM Vol_Factdata vcy

Can some one suggest me a better way ?

Thanks&Regards
SC

Post #1465911
Posted Thursday, June 20, 2013 2:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
Unfortunately, there's not much we can do without more details, DDL, sample data and expected results based on that sample data. These things will allow us to give you a better and faster answer. For more information, please read the article linked in my signature and come back again.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1465954
Posted Thursday, June 20, 2013 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:39 AM
Points: 22, Visits: 239
I Understand.Anyway I could solve it. Thanks
Post #1465955
Posted Thursday, June 20, 2013 3:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
I'm glad you could solved, I was working on a possible solution but I'm not sure that it will work for you. I'll share it anyway.

;with Vol_Factdata as
(
Select CASE WHEN @Period = 'Year' THEN DATEPART(YEAR, EventStartDate)
WHEN @Period = 'Quarter' THEN DATEPART( QUARTER, EventStartDate)
WHEN @Period = 'Month' THEN DATEPART( MONTH, EventStartDate)
END as Period,
Agg.ProductTypeID,
Agg.ProductTypeName,
Agg.LoanProgramTypeId,
MIN(MetricValue) AS MetricValall,
SUM(SampleSize) as SampleSizeall
FROM [Reporting].[AggregatedRatesforWidgets] Agg with (NOLOCK)
WHERE Agg.EventStartDate >= @ipstartdate
and Agg.EventStartDate <=@ipenddate
and Agg.LoanProgramTypeID= @ipProducttypeID
GROUP BY CASE WHEN @Period = 'Year' THEN DATEPART(YEAR, EventStartDate)
WHEN @Period = 'Quarter' THEN DATEPART( QUARTER, EventStartDate)
WHEN @Period = 'Month' THEN DATEPART( MONTH, EventStartDate)
END,
Agg.ProductTypeID,
Agg.ProductTypeName,
Agg.LoanProgramTypeId
)
Select
vcy.Period as Period,
vcy.ProductTypeID as ProductTypeID,
vcy.ProductTypeName as ProductTypeName,
vcy.LoanProgramTypeID AS LoanProgramTypeId,
'ALL' as Loanstate,
vcy.MetricValall_1year as MetricValue,
vcy.SampleSizeall_1year as SampleSize
FROM Vol_Factdata vcy

By the way, I noticed the NOLOCK hint you're using and you should be aware that the data returned by your query might be incorrect due to it.
Understanding the SQL Server NOLOCK hint
SQL Server NOLOCK Hint & other poor ideas.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1465959
Posted Thursday, June 20, 2013 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:39 AM
Points: 22, Visits: 239
This Looks very good.I did not use case statements.So,this is much simpler.Im going to try this one.Thankyou:)
Post #1465961
Posted Thursday, June 20, 2013 4:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:39 AM
Points: 22, Visits: 239
Your code was close to what I needed except for the eventstartdate need to be in date format(Not just year or month or quarter number)

BEGIN


;with Vol_Aggregatedata as

(Select CASE WHEN @ipperiod = 'day' THEN CONVERT(char(10),EventStartDate,126)
WHEN @ipperiod = 'week' THEN dateadd(week, datediff(week, 0, Agg.EventStartDate), 0)
WHEN @ipPeriod = 'quarter' THEN dateadd(qq, datediff(qq, 0, Agg.EventStartDate), 0)
WHEN @ipPeriod = 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, Agg.EventStartDate), 0)
END as Eventstartdate,
Agg.ProductTypeID,
Agg.ProductTypeName,
Agg.LoanProgramTypeId,
MIN(MetricValue) AS MetricValall ,
SUM(SampleSize) as SampleSizeall

FROM [Reporting].[AggregatedRatesforWidgets] Agg with (NOLOCK)
WHERE Agg.EventStartDate >= @ipstartdate
and Agg.EventStartDate <=@ipenddate
and Agg.LoanProgramTypeID= @ipProgramtypeID
GROUP BY CASE WHEN @ipperiod = 'day' THEN CONVERT(char(10),EventStartDate,126)
WHEN @ipperiod = 'week' THEN dateadd(week, datediff(week, 0, Agg.EventStartDate), 0)
WHEN @ipPeriod = 'quarter' THEN dateadd(qq, datediff(qq, 0, Agg.EventStartDate), 0)
WHEN @ipPeriod = 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, Agg.EventStartDate), 0)
END
,Agg.ProductTypeID,Agg.ProductTypeName,Agg.LoanProgramTypeId
)

Select
vcy.EventStartDate as EventStartDate,
vcy.ProductTypeID as ProductTypeID,
vcy.ProductTypeName as ProductTypeName,
vcy.LoanProgramTypeID AS LoanProgramTypeId,
'ALL' as Loanstate,
vcy.MetricValall as MetricValue,
vcy.SampleSizeall as SampleSize

FROM Vol_Aggregatedata vcy
order by Eventstartdate ,loanstate

END
Post #1465977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse