J'ai une table comme

ID    START        END             VALUE
1     2010-01-01   2013-12-31       RED
1     2015-01-01   2015-04-11       BLUE
2     2012-01-01   2013-06-05       YELLOW
2     2013-08-04   2013-12-31       RED
2     2017-01-01   2018-12-31       GREY

Sortie désirée:

ID   YEAR     VALUE
1    2010      RED
1    2011      RED
1    2012      RED
1    2013      RED
1    2015      BLUE
2    2012      YELLOW
2    2013      RED
2    2017      GREY
2    2018      GREY

Ce que j'ai essayé:

select
     case 
     when (START > '2010-01-01' or END < '2010-12-31') = TRUE THEN '2010'
     when (START > '2011-01-01' or END < '2011-12-31') = TRUE THEN '2011'
     ...

Mais cela n'obtient pas toutes les valeurs, par exemple VALUE = RED pour YEAR = 2011. J'obtiens VALUE = RED pour YEAR = 2010, donc juste les dates auxquelles une année est présentée dans la date de début ou de fin.

0
TEC23 17 sept. 2020 à 09:18

4 réponses

Meilleure réponse

Vous pouvez le faire avec un CTE récursif:

WITH cte AS (
  SELECT ID, START, END, VALUE, STRFTIME('%Y', START) + 0 Year
  FROM tablename
  UNION ALL
  SELECT ID, START, END, VALUE, Year + 1
  FROM cte  
  WHERE Year < STRFTIME('%Y', END) + 0
)  
SELECT DISTINCT ID, Year, 
       FIRST_VALUE(VALUE) OVER (PARTITION BY ID, Year ORDER BY END DESC) VALUE
FROM cte
ORDER BY ID, Year

Consultez la démo.

3
forpas 17 sept. 2020 à 07:19

S'il vous plaît, essayez celui-la...

select
     case 
     when (START >= YEAR('2010-01-01') or END <= YEAR('2010-12-31')) = TRUE THEN '2010'
     when (START >= YEAR('2011-01-01') or END <= YEAR('2011-12-31')) = TRUE THEN '2011'
     ...
0
Muhammad Rizwan Munawar 17 sept. 2020 à 06:35

Un CASE WHEN ne considère que la première correspondance. Si la plage va du 01/01/2010 au 31/12/2013, le premier cas correspond et la ligne sera considérée comme «2010» uniquement avec cette approche. De plus, vous avez mal compris les conditions. Une ligne n'est pas en 2010 lorsque le début est après '2010-01-01' ou la fin est avant '2010-12-31', mais lorsque le début est inférieur ou égal à '2010-12-31' et la fin est supérieure ou égale «2010-01-01».

Sélectionnez le cas lorsque (START> '2010-01-01' ou END <'2010-12-31') = TRUE THEN '2010' quand (START> '2011-01-01' ou END <'2011-12-31 ') = VRAI PUIS' 2011 '

Si vous voulez une requête très simple qui ne fonctionne que pendant un ensemble fixe d'années (par exemple, les années 2010 à 2020), vous recherchez UNION ALL.

select id, 2010 as year, value from mytable where start <= '2010-12-31' and end >= '2010-01-01'
union all
select id, 2011 as year, value from mytable where start <= '2011-12-31' and end >= '2011-01-01'
union all
select id, 2012 as year, value from mytable where start <= '2012-12-31' and end >= '2012-01-01'
...
order by id, year;

Cela peut vous donner plusieurs lignes par ID et par année. Si vous ne le souhaitez pas, agrégez ce résultat.

Si vous souhaitez autoriser toutes les années dans la table, peu importe lesquelles et que vous ne voulez pas écrire une requête Union all commençant par l'année 0, vous aurez besoin d'une requête récursive à la place.

0
Thorsten Kettner 17 sept. 2020 à 07:25

Veuillez essayer ceci

DECLARE @MinDate DATE 
DECLARE @MaxDate DATE

set @MinDate = (select top 1 min([START]) from [dbo].[Table_1] )
set @MaxDate = (select top 1 max([END]) from [dbo].[Table_1]  )

SELECT [ID],year(tt.Date) as year , [VALUE] FROM [dbo].[Table_1] as t1
left outer join (SELECT  TOP  (DATEDIFF(YEAR , @MinDate, @MaxDate) + 1) Date = DATEADD(YEAR , ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a CROSS JOIN sys.all_objects b) tt on tt.Date between DATEADD(yy, DATEDIFF(yy, 0,  t1.[START]), 0) and  DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0,  t1.[END]) +1, 0))
0
amer sham 17 sept. 2020 à 07:46