SQL where clause that would select specific daterange based on NOW()

I'm having trouble putting together a where clause that would select records base开发者_开发问答d on a column called DatePick based on now().

Basically, what I need is a where clause that would result in the following:

NOW() = today (8/16/11), then breaks up the year based on 1/3 so this example is 3)

1/1/11-4/15/11:1

4/16/11-8/15/11:2

8/16/11 - 12/31/11:3

And another example if now() = 1/1/12 then the result would be 1

1/1/12-4/15/12:1

4/16/12-8/15/12:2

8/16/12 - 12/31/12:3

Then based on that date range (1 or 2 or 3) it would select all records in DatePick for that daterange. I would love this to be dynamic based on year

This will work. It would probably be better if you made a scalar function to return what date grouping (1-3) that a date is in, whether it's the current date or the date created.

SELECT  *
FROM YourTable
WHERE DATEPART(YY, TheDate) = DATEPART(YY, GETDATE())
    AND (CASE   
            WHEN (DATEPART(MM, TheDate) < 4) 
                OR DATEPART(MM, TheDate) = 4 AND DATEPART(DAY, TheDate) < 16
                THEN 1
            WHEN (DATEPART(MM, TheDate) < 8) 
                OR DATEPART(MM, TheDate) = 8 AND DATEPART(DAY, TheDate) < 16
                THEN 2
            ELSE
                3
        END) =
        (CASE   
            WHEN (DATEPART(MM, GETDATE()) < 4) 
                OR DATEPART(MM, GETDATE()) = 4 AND DATEPART(DAY, GETDATE()) < 16
                THEN 1
            WHEN (DATEPART(MM, GETDATE()) < 8) 
                OR DATEPART(MM, GETDATE()) = 8 AND DATEPART(DAY, GETDATE()) < 16
                THEN 2
            ELSE
                3
        END)

With the function to get the date group, it could look like this:

SELECT  *
FROM YourTable
WHERE DATEPART(YY, TheDate) = DATEPART(YY, GETDATE())
    AND dbo.GetDateGrouping(TheDate) = dbo.GetDateGrouping(GETDATE())

Taking a really random guess here just to try and get an example out so we OP can say whether I'm going in the right direction.

The ANSI standard defines windowing/partitioning/analytic functions and one of them is NTILE I pray to the MS Gods but Oracle and other major vendors offer it as well. NTILE will partition sets of data into equal sized chunks. In the following example, I generate all the days for the year in the DEMO_DATE recursive table. I then use those dates in DATA_PARTITION to segment those date values into 3 equal sized buckets. The final query is provided to demonstrate the boundaries it generates

DECLARE 
    @numberOfGroups int
,   @terminal_date datetime

SELECT
    @numberOfGroups = 3
,   @terminal_date = '2012-01-01'
;WITH DEMO_DATES AS
(
    SELECT CAST('2011-01-01' AS datetime) AS zee_date
    UNION ALL
    SELECT DATEADD(d, +1, DD.zee_date)
    FROM
        demo_dates DD
    WHERE 
        DD.zee_date < @terminal_date

)
, DATE_PARTITION as
(
SELECT
    DD.zee_date
    -- http://msdn.microsoft.com/en-us/library/ms175126.aspx
,   NTILE(@numberOfGroups) OVER (ORDER BY DD.zee_date) AS group_number
FROM DEMO_DATES DD
)
SELECT
    MIN(DP.zee_date) AS smallest_date
,   MAX(DP.zee_date) AS largest_date
,   DP.group_number
FROM
    DATE_PARTITION DP
GROUP BY
    DP.group_number    
OPTION(MAXRECURSION 366)

Output

smallest_date               largest_date               group_number
2011-01-01 00:00:00.000     2011-05-02 00:00:00.000    1
2011-05-03 00:00:00.000     2011-09-01 00:00:00.000    2
2011-09-02 00:00:00.000     2012-01-01 00:00:00.000    3

You can adjust the @numberOfGroups value to get different segments (quarterly, half-year, etc). While not a final solution, is this along the lines of the problem you are attempting to solve?