Default the year based on month value

1
votes

I have a query to display the year value from a table and default the current year. 

select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate())
    union all
    select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder
    order by sOrder asc, syear desc

enter image description here

Here default year is 2015 (with Sorder 0) . Now I Need to display the Default Year based on month value. For e.g if year is 2015 and months are in jan,feb,mar,april then default year is 2015. else default year is 2016. i have tried the below query 

select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder  from [TTS].[dbo].[Class]  where
 year(startdate) <> year(getdate())
union all
select year(getdate())+1 as syear, year(getdate())+1 as eyear, 0 as sOrder where month(getdate()) not in(1,2,3,4) 
union all
select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder where month(getdate())in(1,2,3,4)

order by sOrder asc, syear desc

But year values are getting duplicated. can any help me in this?

3
sqlsql-server

3 Answers

1
votes

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options:

1) Use cte's with distincts

with subq1 (syear, eyear, sOrder) as (select year(getdate())+1 as syear, year(getdate())+1 as eyear, 0 as sOrder where month(getdate()) not in(1,2,3,4) 
union all
select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder where month(getdate())in(1,2,3,4)),
subq2 as (select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder  from [TTS].[dbo].[Class])

Select distinct syear, eyear, sOrder
from subq1
UNION ALL
select syear, eyear, sOrder
from subq2 s2
where s2.syear <> (select syear from subq1)

order by sOrder asc, syear desc

SQL Fiddle for this can be found here: http://sqlfiddle.com/#!6/f89b3a/3/0

2) You could try use a grouping:

select distinct syear, eyear, min(sOrder)
from
(select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder 
from [TTS].[dbo].[Class]
union all
select year(getdate())+1 as syear, year(getdate())+1 as eyear, 0 as sOrder where month(getdate()) not in(1,2,3,4) 
union all
select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder where month(getdate())in(1,2,3,4)) subq

Group by syear, eyear
order by min(sOrder) asc, syear desc

SQL Fiddle for this can be found here: http://sqlfiddle.com/#!6/f89b3a/5/0

I personally would recommend option 1 over option 2.

0
votes

You can use UNION instead of UNION ALL to exclude duplicates:

SELECT  YEAR(startdate) AS syear ,
        YEAR(startdate) AS eyear ,
        1 AS sOrder
FROM    [TTS].[dbo].[Class]
WHERE   YEAR(startdate) <> YEAR(GETDATE())
UNION
SELECT  YEAR(GETDATE()) + 1 AS syear ,
        YEAR(GETDATE()) + 1 AS eyear ,
        0 AS sOrder
WHERE   MONTH(GETDATE()) NOT IN ( 1, 2, 3, 4 )
UNION
SELECT  YEAR(GETDATE()) AS syear ,
        YEAR(GETDATE()) AS eyear ,
        0 AS sOrder
WHERE   MONTH(GETDATE()) IN ( 1, 2, 3, 4 )
ORDER BY sOrder ASC ,
        syear DESC
0
votes

Replace your code (where you tried to find the current year) with the below one should work.

SELECT YEAR(DATEADD(MM,-4,GETDATE())) + 1

like 


SELECT DISTINCT year(startdate) AS syear
    ,year(startdate) AS eyear
    ,1 AS sOrder
FROM [TTS].[dbo].[Class]
WHERE year(startdate) <> year(getdate())

UNION ALL

SELECT YEAR(DATEADD(MM, - 4, GETDATE())) + 1 AS syear
    ,YEAR(DATEADD(MM, - 4, GETDATE())) + 1 AS eyear
    ,0 AS sOrder
ORDER BY sOrder ASC
    ,syear DESC