• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Select Distinct Values From Subquery

I'm trying to make a view that will fill a year drop down on the front end.  I need this drop down to contain all the years in the BudgetedSales table (column BgtSalesYear) in addition to three years prior to the lowest year in BudgetedSales and three years after the current year.

In the code below I've got it to work three years from today, and I'm sure I could use a min function to do a similar method for the prior years but it seems messy to me with the where clause.

My concern is that If I don't use the WHERE clause I might get duplicates since I'm just adding the years.  It seems I need to apply a distinct clause to the whole query, but I'm not a sql guru by any means.
SELECT DISTINCT BgtSalesYear FROM BudgetedSales 
WHERE BgtSalesYear <> YEAR(GETDATE()) AND BgtSalesYear <> YEAR(GETDATE()) + 1 AND BgtSalesYear <> YEAR(GETDATE()) + 2
UNION
SELECT YEAR(GETDATE()) as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 1 as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 2 as BgtSalesYear

Open in new window

0
cardguy1000
Asked:
cardguy1000
  • 2
1 Solution
 
brad2575Commented:
this should work for you:

select DISTINCT BgtSalesYear
from BudgetedSales
where BgtSalesYear NOT between YEAR(getDate()) AND (YEAR(getDate()) + 2)
AND YEAR(BgtSalesYear) between
(select MIN(BgtSalesYear) FROM BudgetedSales) AND (select MIN(BgtSalesYear)) + 3 FROM BudgetedSales)
UNION
SELECT YEAR(GETDATE()) as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 1 as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 2 as BgtSalesYear
select DISTINCT BgtSalesYear 
from BudgetedSales 
where BgtSalesYear NOT between YEAR(getDate()) AND (YEAR(getDate()) + 2)
AND YEAR(BgtSalesYear) between
(select MIN(BgtSalesYear) FROM BudgetedSales) AND (select MIN(BgtSalesYear)) + 3 FROM BudgetedSales)
UNION
SELECT YEAR(GETDATE()) as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 1 as BgtSalesYear
UNION
SELECT YEAR(GETDATE()) + 2 as BgtSalesYear

Open in new window

0
 
HainKurtSr. System AnalystCommented:
you dont need to do anything, your query looks ok

first select gives you years other than current, current+1, current+2 years
second one gives current
third current + 1
fourt current + 2

so it should be correct, no need to add anything...

do you get any duplicates when you run this?
0
 
cardguy1000Author Commented:
I didnt' get any duplicates it just seems messy, like there should be a more efficient way.
0
 
HainKurtSr. System AnalystCommented:
this query is fine ;) and not so messy...
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now