Para obter uma lista de semanas, basta executar isso (encontrado online):
SET DATEFIRST 7
;with DateCTE as
(
select cast('01/01/2017' as datetime) DateValue
union all
select DateValue + 1
from DateCTE
where DateValue + 1 < '1/1/2018'
)
Select
cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate],
DATEPART(wk, DateValue) AS WeekOfYear,
DATEPART(yy, DateValue) AS CalendarYear
from DateCTE
OPTION (MAXRECURSION 0)
Obviamente, se eu SET DATEFIRST = 1, obtém resultados diferentes.
Preciso comparar os dias em uma tabela, sem usar variáveis de tabela. Isto é o que eu gostaria de alcançar:
Assim, gostaria de ver os mesmos 365 dias que estarão disponíveis em ambos os CTE's,
Eu quero comparar o WeekOfYear com base em diferentes DateFirsts.
Eu não sei como fazer isso de forma eficaz. Só consigo fazer funcionar quando
- Crie a variável de tabela 1 e execute o CTE para DateFirst7
- Crie a variável de tabela 2 e execute o CTE para DateFirst1
Isso é ineficiente e estou procurando uma maneira melhor de fazer isso.
Existem algumas coisas trabalhando contra isso 1. Eu não sei como definir o DateFirst dentro da consulta. Só sei configurar antes do cte. 2. Eu gostaria de usar CTEs aninhados para isso, mas OPTION (MAXRECURSION 0) não funciona bem com CTEs aninhados
Isso é possível - usando CTEs para obter os resultados necessários?
Comecei com uma tentativa de corrigir a resposta de Max Vernon para que funcionasse por qualquer ano, mas acabei com duas abordagens diferentes.
Abordagem 1. Ser inteligente
Nesse método, estou calculando o número da semana com base no domingo diretamente do deslocamento do primeiro domingo da semana com base no domingo do ano.
O primeiro domingo da semana baseado no domingo de um ano pode ser obtido assim:
Você está tomando o dia da semana de 1º de janeiro como um número de dias e subtraindo-o de 1º de janeiro, a menos que essa data caia no domingo, caso em que você não está subtraindo nada (é
… % 7para isso que serve). Para números de semana baseados em segunda-feira, o resultado da fórmula acima sempre será um domingo e, neste caso, será o domingo da primeira semana do ano baseada em domingo.Antes de continuar calculando os números da semana com base no domingo, aqui está outra fórmula para transformar qualquer data em 1º de janeiro do mesmo ano:
Basicamente, pegue a data e subtraia seu valor DAYOFYEAR e adicione um dia.
Agora, aqui está uma consulta que está usando o conjunto de CTEs de Max Vernon, as duas fórmulas acima e outra para obter o número real da semana:
Abordagem 2. Usando a brecha do sistema
Esse método é completamente diferente, pois depende de uma peculiaridade interna da função DATEDIFF.
Como você provavelmente sabe, definir DATEFIRST afeta os resultados de funções como
DATEPART(WEEK)eDATEPART(WEEKDAY). Ao contrário deles,DATEDIFF(WEEK)não respeita a configuração DATEFIRST. Ele sempre calcula a diferença como se DATEFIRST fosse definido como 7, ou seja, sempre trata as semanas como baseadas no domingo.Sabendo disso, você pode calcular a diferença em semanas entre 1º de janeiro e qualquer data do mesmo ano, adicionar 1 e usar isso como o número da semana com base no domingo:
Você pode simular a modificação da
DATEFIRSTconfiguração, pois é uma fórmula bem conhecida em que você está simplesmente alterando o primeiro dia da semana.As primeiras semanas dos resultados:
As alegrias do caso de borda. A solução de Max funciona bem para 2017, porque 2017 começou em um domingo. Se você expandir o intervalo de datas, verá que os anos que começam em um domingo são bons. No entanto, se o ano começar em qualquer outro dia da semana, a semana parcial anterior ao primeiro domingo sempre será exibida como Semana 0. Algumas empresas podem querer que seja a semana 0 (já que é uma semana parcial) ou parte da semana anterior ano, ou o que for - no entanto, semanas parciais para as datas de segunda-feira são a semana 1, não a semana 0, então temos uma discrepância.
A maneira mais simples que vi de resolver foi verificar se o ano começava no domingo. Se isso acontecer, use o código original de Max para
WeekOfYearStartingSunday; se não, basta adicionar um ao que ele tinha:Aqui estão os primeiros e últimos dias dos primeiros seis anos (anos começando em todos os dias, exceto quinta-feira - que vai um pouco mais longe, devido aos anos bissextos):