3
votes

Requête SQL pour obtenir les 4 derniers trimestres avec les 2 derniers chiffres de l'année

J'ai besoin d'obtenir les 4 derniers enregistrements d'une table et elle contient des enregistrements avec une valeur de colonne comme «19Q1», «18Q4».

en utilisant la requête ci-dessous, je peux obtenir les 4 derniers trimestres, mais existe-t-il une forme la plus simple de cette requête?

1Q19
4Q18
3Q18
2Q18

O / P: p>

SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,0,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,0,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-1,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-1,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-2,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-2,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-3,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-3,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period

sql-server sql-server-2008-r2 sql-server-2008 sql-server-2012

0 commentaires

3 Réponses :

5
votes

En supposant que vous utilisez 2012 (parce que vous avez balisé 3 versions différentes de SQL Server ...) Que diriez-vous de:

SELECT CONCAT(DATEPART(QUARTER,Q.D),'Q',RIGHT(DATEPART(YEAR,Q.D),2))
FROM (VALUES(0),(-1),(-2),(-3)) V(i)
     CROSS APPLY (VALUES(DATEADD(QUARTER,V.i,GETDATE()))) Q(D);

0 commentaires

1
votes

SELECT CAST( DATEPART(q, DATEADD(q, -v.num, d.CurrentDate) ) AS VARCHAR(1)) 
+ 'Q'
+ RIGHT( CAST( DATEPART(yyyy, DATEADD(q, -v.num, d.CurrentDate)) AS VARCHAR(4) ), 2 )
FROM ( VALUES ( GETDATE() ) ) d(CurrentDate)
    CROSS JOIN ( VALUES (0), (1), (2), (3) ) v(num)
ORDER BY DATEADD(q, -v.num, d.CurrentDate) DESC

0 commentaires

0
votes

Obtient la date de début du trimestre courant remontant à 5 trimestres plus Qx - format année

--QCurrent
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 0, 0) as QCurrentStartDate, 
'Q' + cast(datepart(quarter,( DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 0, 0))) as nvarchar(6)) + ' - ' 
+ cast(datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 0, 0))) as nvarchar(6)) as QLast

--LastQuarter
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) as LastQuarterStartDate, 
'Q' + cast(datepart(quarter,( DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0))) as nvarchar(6)) + ' - ' 
+ cast(datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0))) as nvarchar(6)) as QCurrent

--SecondQuarterBack
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 2, 0) as SecondQuarterStartDate, 
'Q' + cast(datepart(quarter,( DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 2, 0))) as nvarchar(6)) + ' - ' 
+ cast(datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 2, 0))) as nvarchar(6)) as SecondCurrent

--ThreeQuarterBack
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 3, 0) as SecondQuarterStartDate, 
'Q' + cast(datepart(quarter,( DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 3, 0))) as nvarchar(6)) + ' - ' 
+ cast(datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 3, 0))) as nvarchar(6)) as SecondCurrent

--FourQuarterBack
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 4, 0) as SecondQuarterStartDate, 
'Q' + cast(datepart(quarter,( DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 4, 0))) as nvarchar(6)) + ' - ' 
+ cast(datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -4, 0))) as nvarchar(6)) as SecondCurrent

select datepart(year, (DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -4, 0)))

select format(getdate(), 'yy')

0 commentaires