January 20, 2016 at 8:38 am
I'm trying to figure out what or how to do the following We run a event that the registration opens in 2015 and the event is in 2016.
What I need help with is figuring out how to calculate the Weeks out from the End date so something like
say the Date is 11/1/2015 and the Event is 2/11/2016
I need something like 15 weeks out for people that registered the first week
Then on week 14 the number of people that registered so far from week 15 and week 14. etc.
Anyone know an easy way. I have the registration date in the query pull and I have the event date. just need help with the statement.
January 20, 2016 at 8:57 am
Hi
This code gives you the number of weeks:
select '11/1/2015' Date1, '2/11/2016' Date2, DATEDIFF(week,'11/1/2015','2/11/2016') [Weeks]
then you'd need something like this (maby a bit more complex code, though) to make a count over the weeks:
select count(*) [RegisteredUsers], DATEDIFF(week,t.D1,t.D2) [Weeks]
from YourTable t
group by [Weeks]
order by [Weeks]
Igor Micev,My blog: www.igormicev.com
January 20, 2016 at 9:10 am
Igor's code gives you the number of week boundaries. Which means the number of transitions from either Set to Sun, or from Sun to Mon - I can never remember.
If instead you want the number of full 7-day periods, then change his code - in the DATEDIFF formula use "day" instead of "week" to count the number of day transitions (which occur at midnight), then divide by 7. Using integer division, all remainders will be truncated, so the result will be 1 of the date difference is 7 to 13 days, 2 for 14 to 20 days, etc.
January 20, 2016 at 11:24 am
Awesome. Thank you very much. I was able to tweak the code to fit my needs but that's exactly what I was looking for. Kudo's
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply