Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hires by month issue


Hires by month issue

Author
Message
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
our company is in the clothing hire business. Ive just started with them and they have no reporting to speak off

Most of thier hire orders fall on Friday and Saturday as thats when the majority of the functions tend to be. they are keen to accurately compare hires for coming months with previous months, the problem they have always faced in doing this is some months have more weekend hire days than others


ie.
march 2013 5xfriday & 5xSaturday
march 2014 4xfriday & 5xSaturday


is there any clean way to actually do this comparison, or is this only possible if you break the year down into 4 week months starting from the first weekend of the year?

thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
Divide all totals by the number of days (Fridays, for example) contained in a given month and then multiply by 5. Think of it as a normalized total.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
Jeff Moden (2/5/2014)
Divide all totals by the number of days (Fridays, for example) contained in a given month and then multiply by 5. Think of it as a normalized total.



thanks for the reply, hmm not quite following you :-( brain is prob sleeping now.


for talking sake say i was comparing order value.

march 2013 my order value was $1000
march 2014 my order values is also $1000

but 2013 had one more friday so although the two months look as though they performed the same, 2014 was better as it had fewer days to work with.


sorry if im being dumb lol
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
ps_vbdev (2/5/2014)
Jeff Moden (2/5/2014)
Divide all totals by the number of days (Fridays, for example) contained in a given month and then multiply by 5. Think of it as a normalized total.



thanks for the reply, hmm not quite following you :-( brain is prob sleeping now.


for talking sake say i was comparing order value.

march 2013 my order value was $1000
march 2014 my order values is also $1000

but 2013 had one more friday so although the two months look as though they performed the same, 2014 was better as it had fewer days to work with.


sorry if im being dumb lol



As you say, 2013 had 5 Fridays and 2014 had 4 Fridays. That means that if both months has $1000, 2013 actually did worse than 2014. Let's see that work according to the math I spoke of.

 SELECT  Mar2013 = 1000/5*5 --1000 / number or Fridays in month * 5
,Mar2014 = 1000/4*5 --1000 / number or Fridays in month * 5
;



Results:

Mar2013     Mar2014
----------- -----------
1000 1250



Think of the numbers above as the "Normalized Sales for the Month". If there had been 5 Fridays in 2014, it's likely it would have had 1250 for the month.

The numbers aren't "real" but do indicate, relatively speaking which month did better by taking the number of weeks into consideration. The "5" in both forumlas could be any constant but either 1 or 5 seem to be the easiest to understand. "5" is used to "normalize" all months to a 5 week period. If you use "1" instead, then you end up with the average performance by week for the month. For example...

 SELECT  Mar2013 = 1000/5 --1000 / number or Fridays in month (Times 1 is implied)
,Mar2014 = 1000/4 --1000 / number or Fridays in month (Times 1 is implied)
;




Results:

Mar2013     Mar2014
----------- -----------
200 250



Think of the numbers above as the "Average Friday Sales per Week for the Month".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
"The "5" in both forumlas could be any constant but either 1 or 5 seem to be the easiest to understand"


so the 5 is an arbitrary value that is commonly used for the normalization calculation or is it based on the max number of Fridays a month COULD have.

Thanks Jeff for your time, excellent explanation.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
ps_vbdev (2/6/2014)
"The "5" in both forumlas could be any constant but either 1 or 5 seem to be the easiest to understand"


so the 5 is an arbitrary value that is commonly used for the normalization calculation or is it based on the max number of Fridays a month COULD have.

Thanks Jeff for your time, excellent explanation.


Sorry for the delayed response. I picked the number "5" because it's the maximum number of Fridays that any month could have.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search