|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Summary Reportreport and am hoping someone out there has encountered this problem before... TABLE: Date datetime, Amount money, Person varchar(10) I need to be able to quickly build a data set that will give me the trailing 12 month total, the current month total, the previous month total, current and previous quarter and current and previous YTD totals. The table has data that goes back about 2 years and each month new data gets loaded. I want the query to find out the oldest date and use that to determine the date groupings above. I think I can probably do it with a bunch of correlated sub queries, but it seems so messy. I was hoping there might be some way to leverage a date table to help me, but I can't seem to figure it out very effectively. So if anyone has any ideas, let me know! thanks! Mark Off the top of my head - one possible solution.
I would start by creating a UDF, accepts a single argument (the target date) and returned a single row table that would contain the start dates and end dates of each "period" you need. Join this table with your data table where the data table rows are limited to those that are >= the earliest start date in the UDF table. With this joined information, you can then add the appropriate sum(case when...) expressions to compute your totals (e.g., sum (case when table.date between udftable.current_month_start and udftable.current_month_end then amount else 0 end) as current_month_total). You could also use a date table - you would use the same logic contained in the UDF to generate the information. Structure of the table would be the same (except that you would need the target date UDF argument as the PK column of the table). Show quote "Mark" <M***@discussions.microsoft.com> wrote in message news:D1BBB6A9-36FD-4A7B-9FF5-E0B64501BDD5@microsoft.com... > Hi - I'm trying to figure out the most "elegant" way to solve a summary > report and am hoping someone out there has encountered this problem before... > > TABLE: Date datetime, Amount money, Person varchar(10) > > I need to be able to quickly build a data set that will give me the trailing > 12 month total, the current month total, the previous month total, current > and previous quarter and current and previous YTD totals. The table has data > that goes back about 2 years and each month new data gets loaded. I want the > query to find out the oldest date and use that to determine the date > groupings above. > > I think I can probably do it with a bunch of correlated sub queries, but it > seems so messy. I was hoping there might be some way to leverage a date table > to help me, but I can't seem to figure it out very effectively. So if anyone > has any ideas, let me know! > thanks! > > Mark Scott - i was just trying to compose a response to your answer, that started
me thinking. I believe your suggestion along with Steve's suggestion may combine to give me exactly what I want - and be easier to troubleshoot. Thanks, Mark Show quote "Scott Morris" wrote: > Off the top of my head - one possible solution. > > I would start by creating a UDF, accepts a single argument (the target date) > and returned a single row table that would contain the start dates and end > dates of each "period" you need. Join this table with your data table where > the data table rows are limited to those that are >= the earliest start date > in the UDF table. With this joined information, you can then add the > appropriate sum(case when...) expressions to compute your totals (e.g., sum > (case when table.date between udftable.current_month_start and > udftable.current_month_end then amount else 0 end) as current_month_total). > > You could also use a date table - you would use the same logic contained in > the UDF to generate the information. Structure of the table would be the > same (except that you would need the target date UDF argument as the PK > column of the table). > > "Mark" <M***@discussions.microsoft.com> wrote in message > news:D1BBB6A9-36FD-4A7B-9FF5-E0B64501BDD5@microsoft.com... > > Hi - I'm trying to figure out the most "elegant" way to solve a summary > > report and am hoping someone out there has encountered this problem > before... > > > > TABLE: Date datetime, Amount money, Person varchar(10) > > > > I need to be able to quickly build a data set that will give me the > trailing > > 12 month total, the current month total, the previous month total, current > > and previous quarter and current and previous YTD totals. The table has > data > > that goes back about 2 years and each month new data gets loaded. I want > the > > query to find out the oldest date and use that to determine the date > > groupings above. > > > > I think I can probably do it with a bunch of correlated sub queries, but > it > > seems so messy. I was hoping there might be some way to leverage a date > table > > to help me, but I can't seem to figure it out very effectively. So if > anyone > > has any ideas, let me know! > > thanks! > > > > Mark > > > Mark,
You might find something like the following to be more efficient than some other solutions, since it requires just one group aggregate operator to calculate all the totals. declare @today smalldatetime /* Note that dateadd(month,datediff(month,0,@today),0) is the start of this month dateadd(quarter,datediff(quarter,0,@today),0) is the start of this quarter dateadd(year,datediff(year,0,@today),0) is the start of this year */ set @today = '1997-03-04T21:23:43' -- so Northwind works as an example. declare @lastmonth smalldatetime, @thismonth smalldatetime, @nextmonth smalldatetime, @lastquarter smalldatetime, @thisquarter smalldatetime, @nextquarter smalldatetime, @lastyear smalldatetime, @thisyear smalldatetime, @nextyear smalldatetime set @lastmonth = dateadd(month,datediff(month,0,@today)-1,0) set @thismonth = dateadd(month,datediff(month,0,@today) ,0) set @nextmonth = dateadd(month,datediff(month,0,@today)+1,0) -- same for quarter, year select EmployeeID, count(case when OrderDate >= @thismonth and OrderDate <= @today then 1 end) as MTD_orders, count(case when OrderDate >= dateadd(month,-1,@today) and OrderDate <= @today then 1 end) as last_MTD_orders, count(case when OrderDate >= @lastmonth and OrderDate < @thismonth then 1 end) as lastmonth_total_orders -- same for quarter, year from Northwind..Orders group by EmployeeID order by EmployeeID Steve Kass Drew University Mark wrote: Show quote >Hi - I'm trying to figure out the most "elegant" way to solve a summary >report and am hoping someone out there has encountered this problem before... > >TABLE: Date datetime, Amount money, Person varchar(10) > >I need to be able to quickly build a data set that will give me the trailing >12 month total, the current month total, the previous month total, current >and previous quarter and current and previous YTD totals. The table has data >that goes back about 2 years and each month new data gets loaded. I want the >query to find out the oldest date and use that to determine the date >groupings above. > >I think I can probably do it with a bunch of correlated sub queries, but it >seems so messy. I was hoping there might be some way to leverage a date table >to help me, but I can't seem to figure it out very effectively. So if anyone >has any ideas, let me know! >thanks! > >Mark > > Steve - thanks very much for the ideas. I was doing something similar, but
didn't try using the variables. I think it will be much easier to troubleshoot with this type of solution. Mark Show quote "Steve Kass" wrote: > Mark, > > You might find something like the following to be more efficient > than some other solutions, since it requires just one group aggregate > operator to calculate all the totals. > > declare @today smalldatetime > /* Note that > dateadd(month,datediff(month,0,@today),0) is the start of this month > dateadd(quarter,datediff(quarter,0,@today),0) is the start of this > quarter > dateadd(year,datediff(year,0,@today),0) is the start of this year > */ > > set @today = '1997-03-04T21:23:43' -- so Northwind works as an example. > declare > @lastmonth smalldatetime, @thismonth smalldatetime, > @nextmonth smalldatetime, @lastquarter smalldatetime, > @thisquarter smalldatetime, @nextquarter smalldatetime, > @lastyear smalldatetime, @thisyear smalldatetime, > @nextyear smalldatetime > > set @lastmonth = dateadd(month,datediff(month,0,@today)-1,0) > set @thismonth = dateadd(month,datediff(month,0,@today) ,0) > set @nextmonth = dateadd(month,datediff(month,0,@today)+1,0) > -- same for quarter, year > > select > EmployeeID, > count(case when OrderDate >= @thismonth and OrderDate <= @today then 1 > end) as MTD_orders, > count(case when OrderDate >= dateadd(month,-1,@today) and OrderDate <= > @today then 1 end) as last_MTD_orders, > count(case when OrderDate >= @lastmonth and OrderDate < @thismonth > then 1 end) as lastmonth_total_orders > -- same for quarter, year > from Northwind..Orders > group by EmployeeID > order by EmployeeID > > Steve Kass > Drew University > > Mark wrote: > > >Hi - I'm trying to figure out the most "elegant" way to solve a summary > >report and am hoping someone out there has encountered this problem before... > > > >TABLE: Date datetime, Amount money, Person varchar(10) > > > >I need to be able to quickly build a data set that will give me the trailing > >12 month total, the current month total, the previous month total, current > >and previous quarter and current and previous YTD totals. The table has data > >that goes back about 2 years and each month new data gets loaded. I want the > >query to find out the oldest date and use that to determine the date > >groupings above. > > > >I think I can probably do it with a bunch of correlated sub queries, but it > >seems so messy. I was hoping there might be some way to leverage a date table > >to help me, but I can't seem to figure it out very effectively. So if anyone > >has any ideas, let me know! > >thanks! > > > >Mark > > > > >  |
|||||||||||||||||||||||
Other interesting topics