Tutorial SQL

Przykłady, tutorial, gotowe rozwiązania sql.

Kategoria: MS SQL


Tworzenie date table lub tabeli kalendarza


Chciałbym podzielić się kodem t-sql pozwalającym na wygenerowanie tabeli w której będą przechowywane informacje o datach, dniach tygodnia, nazwach dni miesięcy itd. Tabela taka jest przydatna z wielu powodów i każdy powinien takową w bazie posiadać. Pozwala on na wstawianie pustych dni do wyników zapytania oraz ewentualną parametryzację.



Tworzenie kalendarz w t-sql





W kodzie tworzącym kalendarz w ms sql użyje common table oraz rekurencji.






set language Polish;
WITH DatesTable
AS
(
  SELECT CAST('19000101' as date) AS [date],year('19000101') [year],MONTH('19000101') [month], DAY('19000101') [day], DATEpart(dw,'19000101') dw
  , DATEpart(dy,'19000101') dy
  , DATEpart(wk,'19000101') wy
  , DATEpart(q,'19000101') qy
  , datename(month,'19000101') Mname
  , datename(dw,'19000101') Dname
  , 0 endm
  , CAST('19000101' as datetime) StartDay
  , dateadd(s,-1,dateadd(dd,1,CAST('19000101' as datetime))) EndDay
  , cast(CAST('19000101' as datetime) as float) dfloat
  ,YEAR(CAST('19000101' as date))*100000+MONTH(CAST('19000101' as date))*1000+DAY(CAST('19000101' as date)) dint
  UNION ALL
  SELECT DATEADD(dd, 1, [date]), year(DATEADD(dd, 1, [date])) year, MONTH(DATEADD(dd, 1, [date])) month, DAY(DATEADD(dd, 1, [date])) day, 
  DATEpart(dw,DATEADD(dd, 1, [date])) dateofweek
  , DATEpart(dy,DATEADD(dd, 1, [date])) dateofyear
  , DATEpart(wk,DATEADD(dd, 1, [date])) weekofyear
  , DATEpart(q,DATEADD(dd, 1, [date])) quarterofyear
  , datename(month,DATEADD(dd, 1, [date])) Mname
  , datename(dw,DATEADD(dd, 1, [date])) Dname
  , case when  CAST(DATEADD(dd, 1, [date]) as date) = cast(dateadd(s,-1,dateadd(month,1,(CONVERT(datetime,'01-'+right('0'+convert(char(2),MONTH(DATEADD(dd, 1, [date]))),2)+'-'+convert(char(4),YEAR(DATEADD(dd, 1, [date]))),0)))) as date) then 1 else 0 end endm
  , CAST('19000101' as datetime) StartDay
  , dateadd(s,-1,dateadd(dd,1,DATEADD(dd, 1, cast([date] as datetime)))) EndDay
  , cast(CAST( DATEADD(dd, 1, [date]) as datetime) as float) dfloat
  ,YEAR(DATEADD(dd, 1, [date]))*100000+MONTH(DATEADD(dd, 1, [date]))*1000+DAY(DATEADD(dd, 1, [date])) dint
  FROM DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20300101'
)
SELECT * into tDateTable FROM DatesTable
OPTION (MAXRECURSION 0);



go




Krótko omówię w/w kod. SET language Polish pozwala nam na ustawienie języka na polski w tym zapytaniu pozwala nam to na uzyskanie polskich nazw miesięcy.



Common table jest to forma tymczasowego wyniku zdefiniowanego zapytania które możemy wykorzystać w dalszej części naszego kodu powołując się na nazwę tabeli.

W tym przypadku użyłem również rekurencji którą udostępnia nam common table. Rekurencja składa się z dwóch etapów pierwszy to kotwica, a drugi to sama rekurencja i jej warunki. OPTION (MAXRECURSION 0) - określa moment zakończenia pętli.




powrót















SQL-KURSY.pl poleca:

Rozpocznij kolonizacje egzoplanety

Książki
ksikaksikaksikaksikaksika

Copyright 2010-2011 Mariusz Kujawski adres mariuszhk@op.pl

obob ob

Valid HTML 4.01 Transitional