Date Intervals

# Thread: Date Intervals

1. Senior Member
Join Date
Dec 1969
Posts
141

## Date Intervals

Hi<BR>we have a table with 2 fields one is the headcount and the date.<BR>We need the sum of headcount for an interval of 2 months<BR>for e.g i have the start date as 01/01/2003 and end date as 01/01/2004, i want the sum of headcount at an interval of 2 months between the start and the end dates mentioned above.<BR>Could you please suggest a sql query for the above.<BR><BR>Thanks<BR>Prashant<BR>

2. Senior Member
Join Date
Dec 1969
Posts
1,212

## the joy of Google

search for the BETWEEN keyword in SQL.<BR><BR>HTH<BR><BR>R

3. Senior Member
Join Date
Dec 1969
Posts
141

## RE: the joy of Google

but the problem is i want to get the month difference from the 2 dates specified and then get the total headcount. If i use between i just get the count between these 2 dates i want month wise count between these 2 dates.<BR><BR>Thanx<BR>Prashant

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Interval of Month is easy...

SELECT Year(datefield), Month(datefield), SUM(headcount) as heads<BR>FROM table<BR>WHERE Year(datefield) = 2003 <BR>GROUP BY Year(datefield), Month(datefield)<BR>ORDER BY Year(datefield), Month(datefield)<BR><BR>I suppose you could play games with an expression based on the Month to get pairs of months together.<BR><BR>Let&#039;s see...<BR><BR>INT( ( Month(datefield) + 1.1 ) / 2 )<BR><BR>Hmmm???<BR><BR>Use that in place off *all* the Month(datefield) instances above?<BR><BR>January --&#062;&#062; 1 --&#062;&#062; 2.1 / 2 --&#062;&#062; 1.05 then INT() --&#062;&#062; 1<BR>February --&#062;&#062;2 --&#062;&#062; 3.1 / 2 --&#062;&#062; 1.55 then INT() --&#062;&#062; 1<BR>March --&#062;&#062; 3 --&#062;&#062; 4.1 / 2 --&#062;&#062; 2.05 then INT() --&#062;&#062; 2<BR>etc.<BR>December --&#062;&#062; 12 --&#062;&#062; 13.1 / 2 --&#062;&#062; 6.55 then INT() --&#062;&#062; 6<BR><BR>So each pair of months would end up with a single INTeger number and you&#039;d thus have them grouped right, no?<BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
141

## RE: Interval of Month is easy...

HI,<BR>Thanks for the solution. We have modified the same for our requirement and it seems to work fine for months. <BR>Now incase we need to have weeks & days as intervals instead of months , how should we proceed with the same query.<BR><BR>INT( ( Month(datefield) + 1.1 ) / 2 )<BR>how did you get this "1.1" value . what does this indicate?<BR><BR>Thanks<BR>Prashant<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Look into DATEPART...

The only intervals that SQL Server supports via special functions are YEAR(), MONTH() and DAY().<BR><BR>But the DATEPART function can be used to get many other kinds of intervals, including weeks, hours, minutes, etc.<BR><BR>The 1.1 was arbitrary. I probably could have just use 1 (that is 1.0), but I tend to be paranoid about floating point arithmetic (for good reason).<BR><BR>You *SAID* you wanted TWO-MONTH intervals, yes? So I needed to convert PAIRS of months into the same integer number, so that the GROUP BY would work to group the pairs.<BR><BR>So I showed how the math worked:<BR> JAN --&#062;&#062; 1 + 1.1 --&#062; 2.1 / 2 --&#062; 1.05 --&#062; INT() --&#062; 1<BR> FEB --&#062;&#062; 2 + 1.1 --&#062; 3.1 / 2 --&#062; 1.55 --&#062; INT() --&#062; 1<BR><BR>I guess I was being too paranoid, though. Adding 1 would be good enough.<BR><BR> JAN --&#062;&#062; 1 + 1 --&#062; 2 / 2 --&#062; 1.0 --&#062; INT() --&#062; 1<BR> FEB --&#062;&#062; 2 + 1 --&#062; 3 / 2 --&#062; 1.5 --&#062; INT() --&#062; 1<BR><BR>I was thinking, originally, of doing it via rounding instead of via INT(). But INT() is simpler, and adding 1 is good enough.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•