Moving Quarterly Totals 1

[Welshbird]

Dear All,

We supply some products that last either one or three months. So, we calcuate a 'MonthlyLasting' figure to compare our sales against competitors.

For that to work accurately, I actually look at sales on a moving quarterly basis.

So, MQT Jan13 includes the months of Nov12 + Dec12 + Jan13. Then MQT Feb13 contains the months of Dec12 + Jan13 + Feb13.

My prevuious data supplier provided this, but no more....

Any quick thoughts as to the best way to procede?

Thanks all.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[gmmastros]

This is mostly a reporting issue? You need a way to determine which rows to include in a query?

Without knowing more, I would suggest you add another table to your database. Something like...

QuarterlyRange

Description   StartDate   EndDate
-----------   ----------  ---------
MQT Jan13     2012-11-01  2013-02-01
MQT Feb13     2012-12-01  2013-03-01
etc...

Then you can join to this table to get your date range, something like this....

Select Columns
From   YourTable
       Inner Join QuarterlyRange
         On  QuarterlyRange.Description = 'MQT Jan13'
         And YourTable.DateColumn >= QuarterlyRange.StartDate
         And YourTable.DateColumn <  QuarterlyRange.EndDate

Notice a couple important things....

1. Each quarter is 3 months, so Nov 1 to Jan 31. Notice that the data in the table has EndDate of Feb 1.
2. Notice the query use >= for start date, but uses < for end date.

When you are querying for MQT Jan13, any data starting on Nov 1, 2012 through the last second of Jan 13, 2013 will be included because of the less than operator for Feb 1, 2013.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Welshbird]

That sounds like a plan!
The data is in monthly 'buckets' but I convert the text date (201201 etc) to '2012-01-01' in the import as I'm quite partial to time being linear even if the data isn;t!

Thanks George. I'll give that a go.

In fact, I can use that for any dort of moving periodic totals thinking about it!


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[gmmastros]

True.

Instead of "QuarterlyRange", you could name the table DateRange, and then put any sort of description, startdate, and enddate that you want.

I'm glad I was able to help.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Welshbird]

So far so good...

But, I also wat to add up the volume by MQT.
At the moment I have this:

Select
	Name as Geog,
	period as MQTPeriod,
	mkDescription as Brand,
	round(sum((pdQuantity*mkFactor)),0) as PTMs
from
presleveldata
inner join lkpPrepMarkets on mkPrepCode = pdChemicalCode
inner join GP_ODS on pdPractice = odPractice
inner join CCG on odCCG = ccgCode
inner join DatePeriods on pdPeriod = Period
and pdPeriod >= MQTStart and pdPeriod = Period
where mkDesc = 'LHRHa'
group by
NAme, Period, mkDescription

But I am not getting a sum of the PTM by MQT.... Just the same figures I had by month. As you can see, I'm calcualting the PTM figures on the fly too...

It may well be just too late in the day here!

(Oh - my DatePeriod table is is using the'period' description field (i.e. 2013-01-02) as the end date and a date two months before as the start date)

Any thoughts to ease my achy head very much appreciated.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[gmmastros]

I don't understand this part:

inner join DatePeriods on pdPeriod = Period
and pdPeriod >= MQTStart and pdPeriod = Period

First off... you have pdPeriod = Period in there twice, which isn't really a problem, but it's not needed.

Anyway... perhaps you should change this to:

inner join DatePeriods on pdPeriod < Period
and pdPeriod >= MQTStart

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Welshbird]

My 'DatePeriod' table uses the Period description as the end date:

Like this:

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
Period smalldatetime no 4 yes (n/a) (n/a) NULL
MQTStart smalldatetime no 4 yes (n/a) (n/a) NULL
MATStart smalldatetime no 4 yes (n/a) (n/a) NULL
ImpPeriod timestamp no 8 no (n/a) (n/a) NULL

So the date looks like this:

[td]
Period MQTStart MATStart ImpPeriod
2010-08-01 00:00:00 NULL NULL 0x00000000143AFB2E
2010-09-01 00:00:00 NULL NULL 0x00000000143AFB2F
2010-10-01 00:00:00 2010-08-01 00:00:00 NULL 0x00000000143AFB8C
2010-11-01 00:00:00 2010-09-01 00:00:00 NULL 0x00000000143AFB8D
2010-12-01 00:00:00 2010-10-01 00:00:00 NULL 0x00000000143AFB8E
2011-01-01 00:00:00 2010-11-01 00:00:00 NULL 0x00000000143AFB8F
2011-02-01 00:00:00 2010-12-01 00:00:00 NULL 0x00000000143AFB90
2011-03-01 00:00:00 2011-01-01 00:00:00 NULL 0x00000000143AFB91
2011-04-01 00:00:00 2011-02-01 00:00:00 NULL 0x00000000143AFB92
2011-05-01 00:00:00 2011-03-01 00:00:00 NULL 0x00000000143AFB93
2011-06-01 00:00:00 2011-04-01 00:00:00 NULL 0x00000000143AFB94
2011-07-01 00:00:00 2011-05-01 00:00:00 2010-08-01 00:00:00 0x00000000143AFBE8
2011-08-01 00:00:00 2011-06-01 00:00:00 2010-09-01 00:00:00 0x00000000143AFBE9
2011-09-01 00:00:00 2011-07-01 00:00:00 2010-10-01 00:00:00 0x00000000143AFBEA
2011-10-01 00:00:00 2011-08-01 00:00:00 2010-11-01 00:00:00 0x00000000143AFBEB
2011-11-01 00:00:00 2011-09-01 00:00:00 2010-12-01 00:00:00 0x00000000143AFBEC
2011-12-01 00:00:00 2011-10-01 00:00:00 2011-01-01 00:00:00 0x00000000143AFBED
2012-01-01 00:00:00 2011-11-01 00:00:00 2011-02-01 00:00:00 0x00000000143AFBEE
2012-02-01 00:00:00 2011-12-01 00:00:00 2011-03-01 00:00:00 0x00000000143AFBEF
2012-03-01 00:00:00 2012-01-01 00:00:00 2011-04-01 00:00:00 0x00000000143AFBF0
2012-04-01 00:00:00 2012-02-01 00:00:00 2011-05-01 00:00:00 0x00000000143AFBF1
2012-05-01 00:00:00 2012-03-01 00:00:00 2011-06-01 00:00:00 0x00000000143AFBF2
2012-06-01 00:00:00 2012-04-01 00:00:00 2011-07-01 00:00:00 0x00000000143AFBF3
2012-07-01 00:00:00 2012-05-01 00:00:00 2011-08-01 00:00:00 0x00000000143AFBF4
2012-08-01 00:00:00 2012-06-01 00:00:00 2011-09-01 00:00:00 0x00000000143AFBF5
2012-09-01 00:00:00 2012-07-01 00:00:00 2011-10-01 00:00:00 0x00000000143AFBF6
2012-10-01 00:00:00 2012-08-01 00:00:00 2011-11-01 00:00:00 0x00000000143AFBF7
2012-11-01 00:00:00 2012-09-01 00:00:00 2011-12-01 00:00:00 0x00000000143AFBF8
2012-12-01 00:00:00 2012-10-01 00:00:00 2012-01-01 00:00:00 0x00000000143AFBF9
2013-01-01 00:00:00 2012-11-01 00:00:00 2012-02-01 00:00:00 0x00000000143AFBFA
2013-02-01 00:00:00 2012-12-01 00:00:00 2012-03-01 00:00:00 0x00000000143AFBFB
2013-03-01 00:00:00 2013-01-01 00:00:00 2012-04-01 00:00:00 0x00000000143AFBFC
2013-04-01 00:00:00 2013-02-01 00:00:00 2012-05-01 00:00:00 0x00000000143AFBFD
2013-05-01 00:00:00 2013-03-01 00:00:00 2012-06-01 00:00:00 0x00000000143AFBFE
2013-06-01 00:00:00 2013-04-01 00:00:00 2012-07-01 00:00:00 0x00000000143AFBFF
2013-07-01 00:00:00 2013-05-01 00:00:00 2012-08-01 00:00:00 0x00000000143AFC01
2013-08-01 00:00:00 2013-06-01 00:00:00 2012-09-01 00:00:00 0x00000000143AFC02
2013-09-01 00:00:00 2013-07-01 00:00:00 2012-10-01 00:00:00 0x00000000143AFC03
2013-10-01 00:00:00 2013-08-01 00:00:00 2012-11-01 00:00:00 0x00000000143AFC04
2013-11-01 00:00:00 2013-09-01 00:00:00 2012-12-01 00:00:00 0x00000000143AFC05
2013-12-01 00:00:00 2013-10-01 00:00:00 2013-01-01 00:00:00 0x00000000143AFC06
2014-01-01 00:00:00 2013-11-01 00:00:00 2013-02-01 00:00:00 0x00000000143AFC07
2014-02-01 00:00:00 2013-12-01 00:00:00 2013-03-01 00:00:00 0x00000000143AFC08
2014-03-01 00:00:00 2014-01-01 00:00:00 2013-04-01 00:00:00 0x00000000143AFC09
2014-04-01 00:00:00 2014-02-01 00:00:00 2013-05-01 00:00:00 0x00000000143AFC0A
2014-05-01 00:00:00 2014-03-01 00:00:00 2013-06-01 00:00:00 0x00000000143AFC0B
2014-06-01 00:00:00 2014-04-01 00:00:00 2013-07-01 00:00:00 0x00000000143AFC0C
2014-07-01 00:00:00 2014-05-01 00:00:00 2013-08-01 00:00:00 0x00000000143AFC0D
2014-08-01 00:00:00 2014-06-01 00:00:00 2013-09-01 00:00:00 0x00000000143AFC0E
2014-09-01 00:00:00 2014-07-01 00:00:00 2013-10-01 00:00:00 0x00000000143AFC0F
2014-10-01 00:00:00 2014-08-01 00:00:00 2013-11-01 00:00:00 0x00000000143AFC10
2014-11-01 00:00:00 2014-09-01 00:00:00 2013-12-01 00:00:00 0x00000000143AFC11
2014-12-01 00:00:00 2014-10-01 00:00:00 2014-01-01 00:00:00 0x00000000143AFC12
2015-01-01 00:00:00 2014-11-01 00:00:00 2014-02-01 00:00:00 0x00000000143AFC13
2015-02-01 00:00:00 2014-12-01 00:00:00 2014-03-01 00:00:00 0x00000000143AFC14
2015-03-01 00:00:00 2015-01-01 00:00:00 2014-04-01 00:00:00 0x00000000143AFC15
2015-04-01 00:00:00 2015-02-01 00:00:00 2014-05-01 00:00:00 0x00000000143AFC16
2015-05-01 00:00:00 2015-03-01 00:00:00 2014-06-01 00:00:00 0x00000000143AFC17
2015-06-01 00:00:00 2015-04-01 00:00:00 2014-07-01 00:00:00 0x00000000143AFC18
2015-07-01 00:00:00 2015-05-01 00:00:00 2014-08-01 00:00:00 0x00000000143AFC19
2015-08-01 00:00:00 2015-06-01 00:00:00 2014-09-01 00:00:00 0x00000000143AFC1A
2015-09-01 00:00:00 2015-07-01 00:00:00 2014-10-01 00:00:00 0x00000000143AFC1B
2015-10-01 00:00:00 2015-08-01 00:00:00 2014-11-01 00:00:00 0x00000000143AFC1C
2015-11-01 00:00:00 2015-09-01 00:00:00 2014-12-01 00:00:00 0x00000000143AFC1D
2015-12-01 00:00:00 2015-10-01 00:00:00 2015-01-01 00:00:00 0x00000000143AFC1E
2016-01-01 00:00:00 2015-11-01 00:00:00 2015-02-01 00:00:00 0x00000000143AFC1F
2016-02-01 00:00:00 2015-12-01 00:00:00 2015-03-01 00:00:00 0x00000000143AFC20
2016-03-01 00:00:00 2016-01-01 00:00:00 2015-04-01 00:00:00 0x00000000143AFC21
2016-04-01 00:00:00 2016-02-01 00:00:00 2015-05-01 00:00:00 0x00000000143AFC22
2016-05-01 00:00:00 2016-03-01 00:00:00 2015-06-01 00:00:00 0x00000000143AFC23
2016-06-01 00:00:00 2016-04-01 00:00:00 2015-07-01 00:00:00 0x00000000143AFC24
2016-07-01 00:00:00 2016-05-01 00:00:00 2015-08-01 00:00:00 0x00000000143AFC25
2016-08-01 00:00:00 2016-06-01 00:00:00 2015-09-01 00:00:00 0x00000000143AFC26
2016-09-01 00:00:00 2016-07-01 00:00:00 2015-10-01 00:00:00 0x00000000143AFC27
2016-10-01 00:00:00 2016-08-01 00:00:00 2015-11-01 00:00:00 0x00000000143AFC28
2016-11-01 00:00:00 2016-09-01 00:00:00 2015-12-01 00:00:00 0x00000000143AFC29
2016-12-01 00:00:00 2016-10-01 00:00:00 2016-01-01 00:00:00 0x00000000143AFC2A
2017-01-01 00:00:00 2016-11-01 00:00:00 2016-02-01 00:00:00 0x00000000143AFC2B
2017-02-01 00:00:00 2016-12-01 00:00:00 2016-03-01 00:00:00 0x00000000143AFC2C
2017-03-01 00:00:00 2017-01-01 00:00:00 2016-04-01 00:00:00 0x00000000143AFC2D
2017-04-01 00:00:00 2017-02-01 00:00:00 2016-05-01 00:00:00 0x00000000143AFC2E
2017-05-01 00:00:00 2017-03-01 00:00:00 2016-06-01 00:00:00 0x00000000143AFC2F
2017-06-01 00:00:00 2017-04-01 00:00:00 2016-07-01 00:00:00 0x00000000143AFC30
2017-07-01 00:00:00 2017-05-01 00:00:00 2016-08-01 00:00:00 0x00000000143AFC31
2017-08-01 00:00:00 2017-06-01 00:00:00 2016-09-01 00:00:00 0x00000000143AFC32
2017-09-01 00:00:00 2017-07-01 00:00:00 2016-10-01 00:00:00 0x00000000143AFC33
2017-10-01 00:00:00 2017-08-01 00:00:00 2016-11-01 00:00:00 0x00000000143AFC34
2017-11-01 00:00:00 2017-09-01 00:00:00 2016-12-01 00:00:00 0x00000000143AFC35
2017-12-01 00:00:00 2017-10-01 00:00:00 2017-01-01 00:00:00 0x00000000143AFC36
2018-01-01 00:00:00 2017-11-01 00:00:00 2017-02-01 00:00:00 0x00000000143AFC37
2018-02-01 00:00:00 2017-12-01 00:00:00 2017-03-01 00:00:00 0x00000000143AFC38
2018-03-01 00:00:00 2018-01-01 00:00:00 2017-04-01 00:00:00 0x00000000143AFC39
2018-04-01 00:00:00 2018-02-01 00:00:00 2017-05-01 00:00:00 0x00000000143AFC3A
2018-05-01 00:00:00 2018-03-01 00:00:00 2017-06-01 00:00:00 0x00000000143AFC3B

[/td]

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Solved it!

What I needed in my join clause was this:

inner join DatePeriods on pdPeriod <= Period
and pdPeriod >= MQTStart

Very happy teddy now.

Thanks all!


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen