MULTIPLE Cases in Select Query in SQL Server

Question

I have a Scenario in my SQL query is that I have to find out babies records with given dates but from multiple years Suppose My Selection Date is = 01/Jan/2014 to 31/Jan/2014

I have another parameter that is year, that it asks me randomly to select data of babies born of previous (1~3 Years) Suppose If my current Selected dates are of Years 2014 and my year variable has value 2 then

I must bring data of babies respectively to the current year and also a year less then of current date and so on Below is my Given Scenario

--------------------------------------------
--  BABIES_TABLE
ID |    Name         |  Age |   DOB         |
--------------------------------------------
1           JKL         3       21/Jan/2012
2           DEF         2       09/Jan/2013
3           ABC         1       04/Jan/2014
4           XYZ         1       02/Feb/2014

-- Date Range [01/Jan/2014 - 31/Jan/2014]
-- Year = 2
---------------------------------------------
ID     |    Name    |   Age |   DOB         |
---------------------------------------------
3           ABC         1       04/Jan/2014
2           DEF         2       09/Jan/2013



-- Date Range [01/Jan/2014 - 31/Jan/2014]
-- Year = 3
---------------------------------------------
ID     |    Name    |   Age |   DOB         |
---------------------------------------------
3           ABC         1       04/Jan/2014
2           DEF         2       09/Jan/2013
1           JKL         3       21/Jan/2012

below is my SQl Query which is ambiguous and having error.I have try with single date comparison , Its fine But when i add OR condition, it gives me error

--MY SQL Query is Below

DECLARE @startDate AS DATETIME
DECLARE @enddate AS DATETIME
DECLARE @y AS INT
SET @startDate='2012-10-12'
SET @enddate='2012-10-20'
SET @y=2

SELECT * FROM BABIES_TABLE
WHERE 
        CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= CASE            
            --If entered Year = 1
            WHEN @y = 1 THEN        
            (@startDate)                                     
            --If entered Year = 2
            WHEN @y = 2 THEN
                  CASE
                  (@startDate)
                  END                  
            OR
                  CASE
                  YEAR(@startDate-1)
                  END            

            WHEN @y = 3 THEN
                  CASE
                  (@startDate)
                  END          
            OR
                  CASE
                  YEAR(@startDate-1)
                  END
            OR
                  CASE
                  YEAR(@startDate-2)
                  END
        END
    END
  AND
        CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= CASE
            WHEN @y = 1 THEN        
            (@enddate)                                     

            WHEN @y = 2 THEN
                  CASE
                  (@enddate)
                  END                  
            OR
                  CASE
                  YEAR(@enddate-1)
                  END            

            WHEN @y = 3 THEN
                CASE
                (@enddate)
                END          
            OR
                CASE
                YEAR(@enddate-1)
                END
            OR
                CASE
                YEAR(@enddate-2)
                END
        END
    END

I didn't understand the requirement correct , but here too, I noticed that you are using functions in the where clause. You should have a look at this link http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable — Satheesh Variath, Feb 21 '14 at 07:27
I wanted to Select Data of multiple years with same date range which i have mentioned,The thing which I wan to do is to write down the exact Query that can be select-able from multiple years with User's Year - given — DareDevil, Feb 21 '14 at 07:30
perhaps i'm stupid but i don't understand your case at the end. Case when @y = 2 then ... why starting with another case without condition? Why do you have an or in your answer. What do you want to do with that? you want enddate or enddate -1 right? and one last thing, as your condition is <= if it's smaller than enddate-1 it's always <= than enddate it self so why do you need or clause? — Mathese F, Feb 21 '14 at 07:35

score 4 · Accepted Answer · answered Feb 24 '14 at 05:36

Here is what I have achieved my desired result, but I guess it could be possible using Case in Where.

---------------------------------------------

ID |    Name         |  Age |   DOB         |
--------------------------------------------

1   Abid                 6  2008-01-01
2   Zahid                6  2008-01-10
3   Laila                5  2009-01-15
4   Ali                  5  2010-01-26
5   Kali                 4  2011-01-19
6   Sali                 3  2012-01-08
7   Brone                2  2013-01-11
8   Dilche               2  2013-01-29
9   Alpechino            3  2012-08-20

Here is my stored procedure.

--MY SQL Query is Below
CREATE PROCEDURE BabyUnionData
@Year AS INT,
@Start_Date as varchar(30),
@End_Date as Varchar(30)
AS
BEGIN


DECLARE @startDate AS DATETIME
DECLARE @enddate AS DATETIME
DECLARE @y AS INT
SET @startDate=@Start_Date
SET @enddate=@End_Date
--SELECt * FROM BABIES_TABLE
PRINT 'For One Year'
SET @y=@Year

IF @y = 1       
    BEGIN
    PRINT 'For One Year'
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= @startDate
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= @enddate
    END
ELSE IF @y = 2  
    BEGIN
    PRINT 'For Two Years'
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= @startDate
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= @enddate
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-1,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-1,@enddate)
    END
    ELSE IF @y = 3
    BEGIN
        PRINT 'For Three Years'
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= @startDate
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= @enddate
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-1,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-1,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-2,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-2,@enddate)
    END
    ELSE IF @y = 4
    BEGIN
    PRINT 'For Four Years'
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= @startDate
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= @enddate
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-1,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-1,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-2,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-2,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-3,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-3,@enddate)
    END
    ELSE IF @y = 5
    BEGIN
    PRINT 'For Five Years'
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= @startDate
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= @enddate
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-1,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-1,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-2,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-2,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-3,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-3,@enddate)
        UNION
        SELECT * FROM BABIES_TABLE
        WHERE 
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= DATEADD(Year,-4,@startDate)
            AND
            CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEADD(Year,-4,@enddate)
    END
END

--Here is Use of StoredProcedure

Exec BabyUnionData 5,'01-01-2014','01-31-2014'

vittore · Answer 2 · 2014-02-21T09:06:57.630

0

Normalize your start and end dates, as well as DOB to current year

 SET @startDate=DATEADD(yy, YEAR(@startDate)-YEAR(getdate()), @startDate)
 SET @enddate=DATEADD(yy, YEAR(@enddate)-YEAR(getdate()), @enddate)

 select * from babies where 
     Age >= @y  -- check that baby is old enough 
     and DATEADD(yy, YEAR(dob)-YEAR(getdate()), dob) between @startDate and @enddate - and the DOB is within a range in a year.

edited Feb 21 '14 at 09:06

answered Feb 21 '14 at 07:32

vittore

17,449
6
44
82

**<3** is just for data viewing, Obviously i have data stored in integral format. – DareDevil Feb 21 '14 at 07:34

Mathese F · Answer 3 · 2014-02-21T08:40:48.743

I had the same solution as Vittore and then thinking, the problem is i'm not following your requirement from what i understood. When it's 1 taking between today startdate enddate and same for previous year

Here is an uneleguant solution i have

create table #y (y tinyint)
insert into #y values (1)
insert into #y values (2)
insert into #y values (3)


DECLARE @startDate AS DATETIME
DECLARE @enddate AS DATETIME
DECLARE @y AS INT
SET @startDate='2012-10-12'
SET @enddate='2012-10-20'
SET @y=2

SELECT * 
FROM BABIES_TABLE
cross join #y 
WHERE CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) >= datediff(year, y - 1, @startdate )
AND CAST(CONVERT(VARCHAR, BABIES_TABLE.DOB, 111) AS DATETIME) <= DATEDIFF(year, y - 1, @enddate )
and y <= @y

It's perhaps possible with cte

UPDATE

I had this idea in case you need specific date test not a long between. For example you want dob = 19/02/13 or 19/02/12 or 19/02/11 in case of y=2 but not dob between 19/02/11 and 19/02/13.

here Y = 2 is just an example is that in one case i have to find out the data of two years this value can be varied from 1~4 — DareDevil, Feb 21 '14 at 09:23

MULTIPLE Cases in Select Query in SQL Server

3 Answers3