SQLCMD command utility requires a sql script file with sql statements or Stored Procedures.SQLCMD will invoke SQL file in command prompt and run the scripts inside the *.SQL file as run normally by using SQL Server management Studio.
-- Go to Command Prompt
-- Go to C drive C:\>
-- Type the following command inorder to run SQL scriptFileName.sql --> Contains either select statement or set of stored procedures-- From local systemsqlcmd -S -i \\ServerName\Foldername\FileName.sql-- Running On Serversqlcmd -S -i C:\FileName.sql Thursday, April 14, 2011
Run SP's or Select Queries by SQLCMD
Execute StoredProcedure By Using SQLCMD
The following example shows how to execute a stored procedure by using sqlcmd
IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL
DROP PROCEDURE dbo.ContactEmailAddress;
GO
USE AdventureWorks2008
CREATE PROCEDURE dbo.ContactEmailAddress
(
@FirstName nvarchar(50)
,@LastName nvarchar(50)
)
AS
SET NOCOUNT ON
SELECT EmailAddress
FROM Person.Person
WHERE FirstName = @FirstName
AND LastName = @LastName;
SET NOCOUNT OFF
At the sqlcmd prompt, enter the following:
C:\sqlcmd
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)
2> GO
EmailAddress
-----------------------------
gustavo0@adventure-works.com
IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL
DROP PROCEDURE dbo.ContactEmailAddress;
GO
USE AdventureWorks2008
CREATE PROCEDURE dbo.ContactEmailAddress
(
@FirstName nvarchar(50)
,@LastName nvarchar(50)
)
AS
SET NOCOUNT ON
SELECT EmailAddress
FROM Person.Person
WHERE FirstName = @FirstName
AND LastName = @LastName;
SET NOCOUNT OFF
At the sqlcmd prompt, enter the following:
C:\sqlcmd
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)
2> GO
EmailAddress
-----------------------------
gustavo0@adventure-works.com
Tuesday, April 12, 2011
DATE Dimension
Hi All,
During last couple of days I was working on how to create a stored procedure in order to populate data into Dimensions Date table. In Stored procedure,used some variables which are not using while inserting into Dimensions.Date table.This stored procedure also handles local language translations for Denmark,Sweden,Finnish,Norway and UK or US countries.
-- Dimensions.Date table structure
CREATE TABLE [Dimensions].[Date](
[Datekey] [int] NOT NULL,
[Date] [date] NULL,
[DateText] [char](10) NULL,
[DateTextDA] [char](20) NULL,
[DateTextEN] [char](20) NULL,
[DateTextSE] [char](20) NULL,
[DateTextNO] [char](20) NULL,
[DateTextFI] [char](20) NULL,
[YearKey] [smallint] NULL,
[Year] [date] NULL,
[HalfYearOfYearKey] [tinyint] NULL,
[HalfYear] [date] NULL,
[HalfYearText] [char](5) NULL,
[HalfYearTextDA] [char](20) NULL,
[HalfYearTextEN] [char](20) NULL,
[HalfYearTextSE] [char](20) NULL,
[HalfYearTextNO] [char](20) NULL,
[HalfYearTextFI] [char](20) NULL,
[QuarterOfYearKey] [tinyint] NULL,
[Quarter] [date] NULL,
[QuarterText] [char](5) NULL,
[QuarterTextDA] [char](20) NULL,
[QuarterTextEN] [char](20) NULL,
[QuarterTextSE] [char](20) NULL,
[QuarterTextNO] [char](20) NULL,
[QuarterTextFI] [char](15) NULL,
[MonthOfYearKey] [smallint] NULL,
[Month] [date] NULL,
[MonthText] [char](5) NULL,
[MonthTextDA] [char](14) NULL,
[MonthTextEN] [char](14) NULL,
[MonthTextSE] [char](14) NULL,
[MonthTextNO] [char](14) NULL,
[MonthTextFI] [char](14) NULL,
[ISOWeek] [int] NULL,
[USWeek] [int] NULL,
[ISOWeekKey] [tinyint] NULL,
[USWeekKey] [tinyint] NULL,
[DaysInYear] [smallint] NULL,
[DaysInYearToDate] [smallint] NULL,
[DaysInHalfYear] [smallint] NULL,
[DaysInHalfYearToDate] [smallint] NULL,
[DaysInQuarter] [smallint] NULL,
[DaysInQuarterToDate] [smallint] NULL,
[DaysInMonth] [smallint] NULL,
[DaysInMonthToDate] [smallint] NULL
)
-- Stored Procedure to populate data
CREATE PROCEDURE [dbo].[uspPopulatesDimensionsDate]
-- Add the parameters for the stored procedure here
@p_start_year date,
@p_end_year date
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @date datetime -- DateTime
Declare @date_end datetime -- DateTime
Declare @calendar_date_id integer
Declare @date_short_name varchar(25)
Declare @date_long_name varchar(50)
Declare @day_in_week smallint
Declare @day_in_month smallint
Declare @day_in_qtr smallint
Declare @day_in_year smallint
Declare @day_of_week_abbr nvarchar(3)
Declare @week_in_year integer
Declare @week_id integer
Declare @padded_week_in_year nvarchar(2)
Declare @year integer
Declare @week_start_date date
Declare @week_end_date datetime
Declare @week_short_name varchar(25)
Declare @week_long_name varchar(50)
Declare @week_in_month smallint
Declare @week_in_qtr smallint
Declare @month_short_name nvarchar(25)
Declare @month_long_name nvarchar(50)
Declare @month_start_date datetime
Declare @half_span smallint
Declare @br_month_start_date datetime
Declare @month_end_date datetime
Declare @month_id integer
Declare @padded_month_in_year nvarchar(2)
Declare @padded_qtr_in_year nvarchar(2)
Declare @month_in_year smallint
Declare @qtr_in_year smallint
Declare @month_in_qtr smallint
Declare @month_span smallint
Declare @qtr_id integer
Declare @qtr_short_name varchar(25)
Declare @qtr_long_name varchar(50)
Declare @qtr_start_date datetime
Declare @qtr_end_date datetime
Declare @qtr_span smallint
Declare @year_short_name varchar(25)
Declare @year_long_name varchar(50)
Declare @year_start_date datetime
Declare @year_end_date datetime
Declare @year_span smallint
Declare @padded_day_in_month char(2)
Declare @date_id integer
Declare @DateTextDA char (20)
Declare @DateTextEN char (20)
Declare @DateTextSE char (20)
Declare @DateTextNO char (20)
Declare @DateTextFI char (20)
Declare @HalfyearText char (5)
Declare @HalfYearOfYearKey tinyint
Declare @HalfYear date
Declare @HalfYearStartDate Date
Declare @HalfYearEndDate Date
Declare @HalfYearSpan TinyInt
Declare @DaysInHalfYear TinyInt
Declare @QuarterOfyearKey TinyInt
Declare @Quarter Datetime
Declare @QuarterText char (5)
Declare @MonthTextDA Char(14)
Declare @MonthTextEN Char(14)
Declare @MonthTextSE Char(14)
Declare @MonthTextNO Char(14)
Declare @MonthTextFI Char(14)
declare @cntr int
set @date = cast(0 as datetime)
set @date = @p_start_year
-- Truncate table Dimensions.Date data before inserting data
Truncate table Dimensions.Date
while @date <= @p_end_year
begin
/* Define standard calendar */
set @date_end = dateadd(ss, -1, dateadd(d, 1, @date))
set @day_in_week = datepart(dw, @date)
set @day_in_month = datepart(d, @date)
set @padded_day_in_month = right('00' + rtrim(cast(@day_in_month as char(2))), 2)
set @qtr_start_date = dateadd(q, datediff(q, 0, @date), 0)
set @day_in_qtr = datediff(d, @qtr_start_date, @date) + 1
set @day_in_year = datepart(dy, @date)
set @day_of_week_abbr = left(datename(dw, @date), 3)
set @week_in_year = datepart(wk, @date)
set @padded_week_in_year = right('00' + rtrim(cast(@week_in_year as char(2))), 2)
set @year = datepart(yy, @date)
set @week_start_date = dateadd(d, 1- @day_in_week, @date)
--set @week_end_date = dateadd(ss, -1, dateadd(wk, 1, @week_start_date))
set @week_end_date = dateadd(DD, -1, dateadd(wk, 1, @week_start_date))
set @week_short_name = @padded_week_in_year + '-' + cast(@year as char(4))
set @week_long_name = @padded_week_in_year + ', ' + cast(@year as char(4))
set @week_in_qtr =
CASE
WHEN datepart(mm, @date) >=1 and datepart(mm, @date) <=3 THEN
datediff(wk, cast('01/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) >=4 and datepart(mm, @date)<=6 THEN
datediff(wk, cast('04/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date)>=7 and datepart(mm, @date)<= 9 THEN
datediff(wk, cast('07/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
ELSE
datediff(wk, cast('10/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
END
set @week_in_month =
CASE
WHEN datepart(mm, @date) = 1 THEN
datediff(wk, cast('01/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 2 THEN
datediff(wk, cast('02/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 3 THEN
datediff(wk, cast('03/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 4 THEN
datediff(wk, cast('04/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 5 THEN
datediff(wk, cast('05/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 6 THEN
datediff(wk, cast('06/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 7 THEN
datediff(wk, cast('07/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 8 THEN
datediff(wk, cast('08/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 9 THEN
datediff(wk, cast('09/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 10 THEN
datediff(wk, cast('10/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 11 THEN
datediff(wk, cast('11/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 12 THEN
datediff(wk, cast('12/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
ELSE
0
END
if left(datepart(MONTH, @date), 3) < 10
set @month_short_name = '0'+ left(datepart(MONTH, @date), 3) + '-' + cast(@year as char(4))
Else
Set @month_short_name = left(datepart(MONTH, @date), 3) + '-' + cast(@year as char(4))
set @month_long_name = datename(m, @date) + ' ' + cast(@year as char(4))
set @month_start_date = dateadd(m, datediff(m, 0, @date), 0)
set @qtr_in_year = datepart(q, @date)
set @month_in_year = datepart(m, @date)
set @month_in_qtr = ((@month_in_year - 1) % 3) + 1
set @month_end_date = dateadd(ss, -1, dateadd(m, 1, @month_start_date))
set @month_span = datepart(d, @month_end_date)
set @padded_month_in_year = right('00' + rtrim(cast(@month_in_year as char(2))), 2)
set @week_id = cast(@year as char(4)) + @padded_week_in_year + @padded_month_in_year
set @month_id = cast(@year as char(4)) + @padded_month_in_year
set @padded_qtr_in_year = right('00' + rtrim(cast(@qtr_in_year as char(2))), 2)
set @qtr_id = cast(@year as char (4)) + @padded_qtr_in_year
set @qtr_short_name = 'Q' +cast(@qtr_in_year as char(1)) + '-' + Right(@year,2) --cast(@year as char(4))
set @qtr_long_name =
case @qtr_in_year
when 1 then '1st'
when 2 then '2nd'
when 3 then '3rd'
when 4 then '4th'
end
+ ' Quarter, ' + cast(@year as char(4))
Set @QuarterText = @qtr_short_name
Set @QuarterOfyearKey = Case
when left(@QuarterText,2) = 'Q1' then 1
when left(@QuarterText,2) = 'Q2' then 2
when left(@QuarterText,2) = 'Q3' then 3
when left(@QuarterText,2) = 'Q4' then 4
END
set @qtr_end_date = dateadd(ss, -1, dateadd(q, 1, @qtr_start_date))
set @qtr_span = datediff(d, @qtr_start_date, @qtr_end_date) + 1
set @year_short_name = @year
set @year_long_name = 'Year - ' + cast(@year as char(4))
set @year_start_date = dateadd(yy, datediff(yy, 0, @date), 0)
set @year_end_date = dateadd(DD, -1, dateadd(yy, 1, @year_start_date))
set @year_span = datediff(d, @year_start_date, @year_end_date) + 1
set @date_id = cast(@year as char(4)) + @padded_month_in_year + @padded_day_in_month
set @date_short_name = @padded_day_in_month + '-' + @month_short_name
set @date_long_name = datename(mm, @date) + ' ' + cast(@day_in_month as varchar(2)) + ', ' + cast(@year as char(4))
set @HalfYearOfYearKey = Case when left(@month_short_name,2) < '07' then 1 else 2 End
SET @HalfYearStartDate =
CASE WHEN @HalfYearOfYearKey = 1 THEN @year_start_date ELSE DATEADD(M,6,@year_start_date) END
SET @HalfYearEndDate = DATEADD(d,-1,dateadd(m,6,@HalfYearStartDate))
SET @HalfYearSpan = Datediff(D,@HalfYearStartDate,@HalfYearEndDate) +1
SET @DaysInHalfYear = Datediff(D,@HalfYearStartDate,@date) +1
Set @HalfYear = Case when left(@month_short_name,2) < '07' then LEFT(@year,4)+ '-01-01' else LEFT(@year,4)+ '-07-01' End
Set @HalfyearText = Case when left(@month_short_name,2) < '07' then '1H-' +right(@year,2) else '2H-' + right(@year,2) End
SET LANGUAGE Danish
SELECT @DateTextDA = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthTextDA = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE British
SELECT @DatetextEN = DATEname(DAY,@date) + ' ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthtextEN = DATENAME(month, @date) + '-' +right(@year,2)
SET LANGUAGE Swedish
SELECT @DateTextSE = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthTextSE = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE Norwegian
Set @DateTextNO = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
Set @MonthTextNO = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE Finnish
Set @DateTextFI = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
Set @MonthTextFI = DATENAME(month, @date) + '-' +right(@year,2)
Set language English
Insert into Dimensions.Date
Select
@Date_id as DateKey,
@date as Date,
left(@date_short_name,2)+'-'+@month_short_name AS DateText,
--@day_in_week,
--@day_of_week_abbr,
@DateTextDA as DateTextDA,
@DateTextEN as DateTextEN,
@DateTextSE as DateTextSE,
@DateTextNO as DateTextNO,
@DateTextFI as DateTextFI,
@year as YearKey,
@Date as Year,
@HalfYearOfYearKey,
@HalfYear,
@HalfyearText,
HalfyearTextDA = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextEN = case
when left(@HalfyearText,2) = '1H' then '1st Half-year' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2nd Half-year' + '-' + right(@year,2)
end,
HalfyearTextSE = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextNO = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextFI = case
when left(@HalfyearText,2) = '1H' then '1. Puolen-vuoden' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Puolen-vuoden' + '-' + right(@year,2)
end,
QuarterOfyearKey = Case
when left(@QuarterText,2) = 'Q1' then 1
when left(@QuarterText,2) = 'Q2' then 2
when left(@QuarterText,2) = 'Q3' then 3
when left(@QuarterText,2) = 'Q4' then 4
END ,
Quarter = case
when left(@QuarterText,2) = 'Q1' then right(@year,4) + '0101'
when left(@QuarterText,2) = 'Q2' then right(@year,4) + '0401'
when left(@QuarterText,2) = 'Q3' then right(@year,4) + '0701'
when left(@QuarterText,2) = 'Q4' then right(@year,4) + '1001'
end,
@QuarterText,
QuarterTextDA = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextEN = case
when left(@QuarterText,2) = 'Q1' then '1st Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2nd Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3rd Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4th Quarter' + '-' + right(@year,2)
end,
QuarterTextSE = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextNO = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextFI = case
when left(@QuarterText,2) = 'Q1' then '1. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Neljännes' + '-' + right(@year,2)
End,
@month_in_year,
Month = Case
when left(@month_short_name,2) = '01' then right(@year,4) + '-01-01'
when left(@month_short_name,2) = '02' then right(@year,4) + '-02-01'
when left(@month_short_name,2) = '03' then right(@year,4) + '-03-01'
when left(@month_short_name,2) = '04' then right(@year,4) + '-04-01'
when left(@month_short_name,2) = '05' then right(@year,4) + '-05-01'
when left(@month_short_name,2) = '06' then right(@year,4) + '-06-01'
when left(@month_short_name,2) = '07' then right(@year,4) + '-07-01'
when left(@month_short_name,2) = '08' then right(@year,4) + '-08-01'
when left(@month_short_name,2) = '09' then right(@year,4) + '-09-01'
when left(@month_short_name,2) = '10' then right(@year,4) + '-10-01'
when left(@month_short_name,2) = '11' then right(@year,4) + '-11-01'
when left(@month_short_name,2) = '12' then right(@year,4) + '-12-01'
end,
MonthText = left(@month_short_name,2)+'-'+right(@year,2),
@MonthTextDA,
@MonthTextEN,
@MonthTextSE,
@MonthTextNO,
@MonthTextFI,
ISOWeek = right(@year,4)+left(@month_short_name,2),
USWeek = right(@year,4)+left(@month_short_name,2),
ISOWeekKey = DATEPART(ISO_WEEK, @date),
USWeekKey = DATEPART(WW, @date),
@year_span as DaysInYear,
@day_in_year as DaysInYearToDate,
@HalfYearSpan,
@DaysInHalfYear,
@qtr_span as DaysInQuarter,
@day_in_qtr as DaysInQuarterToDate,
@month_span as DaysInMonth,
@day_in_month as DaysInMonthToDate
set @date = dateadd(d, 1, @date)
end
END
During last couple of days I was working on how to create a stored procedure in order to populate data into Dimensions Date table. In Stored procedure,used some variables which are not using while inserting into Dimensions.Date table.This stored procedure also handles local language translations for Denmark,Sweden,Finnish,Norway and UK or US countries.
-- Dimensions.Date table structure
CREATE TABLE [Dimensions].[Date](
[Datekey] [int] NOT NULL,
[Date] [date] NULL,
[DateText] [char](10) NULL,
[DateTextDA] [char](20) NULL,
[DateTextEN] [char](20) NULL,
[DateTextSE] [char](20) NULL,
[DateTextNO] [char](20) NULL,
[DateTextFI] [char](20) NULL,
[YearKey] [smallint] NULL,
[Year] [date] NULL,
[HalfYearOfYearKey] [tinyint] NULL,
[HalfYear] [date] NULL,
[HalfYearText] [char](5) NULL,
[HalfYearTextDA] [char](20) NULL,
[HalfYearTextEN] [char](20) NULL,
[HalfYearTextSE] [char](20) NULL,
[HalfYearTextNO] [char](20) NULL,
[HalfYearTextFI] [char](20) NULL,
[QuarterOfYearKey] [tinyint] NULL,
[Quarter] [date] NULL,
[QuarterText] [char](5) NULL,
[QuarterTextDA] [char](20) NULL,
[QuarterTextEN] [char](20) NULL,
[QuarterTextSE] [char](20) NULL,
[QuarterTextNO] [char](20) NULL,
[QuarterTextFI] [char](15) NULL,
[MonthOfYearKey] [smallint] NULL,
[Month] [date] NULL,
[MonthText] [char](5) NULL,
[MonthTextDA] [char](14) NULL,
[MonthTextEN] [char](14) NULL,
[MonthTextSE] [char](14) NULL,
[MonthTextNO] [char](14) NULL,
[MonthTextFI] [char](14) NULL,
[ISOWeek] [int] NULL,
[USWeek] [int] NULL,
[ISOWeekKey] [tinyint] NULL,
[USWeekKey] [tinyint] NULL,
[DaysInYear] [smallint] NULL,
[DaysInYearToDate] [smallint] NULL,
[DaysInHalfYear] [smallint] NULL,
[DaysInHalfYearToDate] [smallint] NULL,
[DaysInQuarter] [smallint] NULL,
[DaysInQuarterToDate] [smallint] NULL,
[DaysInMonth] [smallint] NULL,
[DaysInMonthToDate] [smallint] NULL
)
-- Stored Procedure to populate data
CREATE PROCEDURE [dbo].[uspPopulatesDimensionsDate]
-- Add the parameters for the stored procedure here
@p_start_year date,
@p_end_year date
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @date datetime -- DateTime
Declare @date_end datetime -- DateTime
Declare @calendar_date_id integer
Declare @date_short_name varchar(25)
Declare @date_long_name varchar(50)
Declare @day_in_week smallint
Declare @day_in_month smallint
Declare @day_in_qtr smallint
Declare @day_in_year smallint
Declare @day_of_week_abbr nvarchar(3)
Declare @week_in_year integer
Declare @week_id integer
Declare @padded_week_in_year nvarchar(2)
Declare @year integer
Declare @week_start_date date
Declare @week_end_date datetime
Declare @week_short_name varchar(25)
Declare @week_long_name varchar(50)
Declare @week_in_month smallint
Declare @week_in_qtr smallint
Declare @month_short_name nvarchar(25)
Declare @month_long_name nvarchar(50)
Declare @month_start_date datetime
Declare @half_span smallint
Declare @br_month_start_date datetime
Declare @month_end_date datetime
Declare @month_id integer
Declare @padded_month_in_year nvarchar(2)
Declare @padded_qtr_in_year nvarchar(2)
Declare @month_in_year smallint
Declare @qtr_in_year smallint
Declare @month_in_qtr smallint
Declare @month_span smallint
Declare @qtr_id integer
Declare @qtr_short_name varchar(25)
Declare @qtr_long_name varchar(50)
Declare @qtr_start_date datetime
Declare @qtr_end_date datetime
Declare @qtr_span smallint
Declare @year_short_name varchar(25)
Declare @year_long_name varchar(50)
Declare @year_start_date datetime
Declare @year_end_date datetime
Declare @year_span smallint
Declare @padded_day_in_month char(2)
Declare @date_id integer
Declare @DateTextDA char (20)
Declare @DateTextEN char (20)
Declare @DateTextSE char (20)
Declare @DateTextNO char (20)
Declare @DateTextFI char (20)
Declare @HalfyearText char (5)
Declare @HalfYearOfYearKey tinyint
Declare @HalfYear date
Declare @HalfYearStartDate Date
Declare @HalfYearEndDate Date
Declare @HalfYearSpan TinyInt
Declare @DaysInHalfYear TinyInt
Declare @QuarterOfyearKey TinyInt
Declare @Quarter Datetime
Declare @QuarterText char (5)
Declare @MonthTextDA Char(14)
Declare @MonthTextEN Char(14)
Declare @MonthTextSE Char(14)
Declare @MonthTextNO Char(14)
Declare @MonthTextFI Char(14)
declare @cntr int
set @date = cast(0 as datetime)
set @date = @p_start_year
-- Truncate table Dimensions.Date data before inserting data
Truncate table Dimensions.Date
while @date <= @p_end_year
begin
/* Define standard calendar */
set @date_end = dateadd(ss, -1, dateadd(d, 1, @date))
set @day_in_week = datepart(dw, @date)
set @day_in_month = datepart(d, @date)
set @padded_day_in_month = right('00' + rtrim(cast(@day_in_month as char(2))), 2)
set @qtr_start_date = dateadd(q, datediff(q, 0, @date), 0)
set @day_in_qtr = datediff(d, @qtr_start_date, @date) + 1
set @day_in_year = datepart(dy, @date)
set @day_of_week_abbr = left(datename(dw, @date), 3)
set @week_in_year = datepart(wk, @date)
set @padded_week_in_year = right('00' + rtrim(cast(@week_in_year as char(2))), 2)
set @year = datepart(yy, @date)
set @week_start_date = dateadd(d, 1- @day_in_week, @date)
--set @week_end_date = dateadd(ss, -1, dateadd(wk, 1, @week_start_date))
set @week_end_date = dateadd(DD, -1, dateadd(wk, 1, @week_start_date))
set @week_short_name = @padded_week_in_year + '-' + cast(@year as char(4))
set @week_long_name = @padded_week_in_year + ', ' + cast(@year as char(4))
set @week_in_qtr =
CASE
WHEN datepart(mm, @date) >=1 and datepart(mm, @date) <=3 THEN
datediff(wk, cast('01/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) >=4 and datepart(mm, @date)<=6 THEN
datediff(wk, cast('04/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date)>=7 and datepart(mm, @date)<= 9 THEN
datediff(wk, cast('07/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
ELSE
datediff(wk, cast('10/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
END
set @week_in_month =
CASE
WHEN datepart(mm, @date) = 1 THEN
datediff(wk, cast('01/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 2 THEN
datediff(wk, cast('02/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 3 THEN
datediff(wk, cast('03/01/' + cast(datepart(yyyy, @date) as nvarchar(4)) as smalldatetime), @date) + 1
WHEN datepart(mm, @date) = 4 THEN
datediff(wk, cast('04/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 5 THEN
datediff(wk, cast('05/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 6 THEN
datediff(wk, cast('06/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 7 THEN
datediff(wk, cast('07/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 8 THEN
datediff(wk, cast('08/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 9 THEN
datediff(wk, cast('09/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 10 THEN
datediff(wk, cast('10/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 11 THEN
datediff(wk, cast('11/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
WHEN datepart(mm, @date) = 12 THEN
datediff(wk, cast('12/01/' + cast(datepart(yyyy,@date) as nvarchar(4)) as smalldatetime),@date) + 1
ELSE
0
END
if left(datepart(MONTH, @date), 3) < 10
set @month_short_name = '0'+ left(datepart(MONTH, @date), 3) + '-' + cast(@year as char(4))
Else
Set @month_short_name = left(datepart(MONTH, @date), 3) + '-' + cast(@year as char(4))
set @month_long_name = datename(m, @date) + ' ' + cast(@year as char(4))
set @month_start_date = dateadd(m, datediff(m, 0, @date), 0)
set @qtr_in_year = datepart(q, @date)
set @month_in_year = datepart(m, @date)
set @month_in_qtr = ((@month_in_year - 1) % 3) + 1
set @month_end_date = dateadd(ss, -1, dateadd(m, 1, @month_start_date))
set @month_span = datepart(d, @month_end_date)
set @padded_month_in_year = right('00' + rtrim(cast(@month_in_year as char(2))), 2)
set @week_id = cast(@year as char(4)) + @padded_week_in_year + @padded_month_in_year
set @month_id = cast(@year as char(4)) + @padded_month_in_year
set @padded_qtr_in_year = right('00' + rtrim(cast(@qtr_in_year as char(2))), 2)
set @qtr_id = cast(@year as char (4)) + @padded_qtr_in_year
set @qtr_short_name = 'Q' +cast(@qtr_in_year as char(1)) + '-' + Right(@year,2) --cast(@year as char(4))
set @qtr_long_name =
case @qtr_in_year
when 1 then '1st'
when 2 then '2nd'
when 3 then '3rd'
when 4 then '4th'
end
+ ' Quarter, ' + cast(@year as char(4))
Set @QuarterText = @qtr_short_name
Set @QuarterOfyearKey = Case
when left(@QuarterText,2) = 'Q1' then 1
when left(@QuarterText,2) = 'Q2' then 2
when left(@QuarterText,2) = 'Q3' then 3
when left(@QuarterText,2) = 'Q4' then 4
END
set @qtr_end_date = dateadd(ss, -1, dateadd(q, 1, @qtr_start_date))
set @qtr_span = datediff(d, @qtr_start_date, @qtr_end_date) + 1
set @year_short_name = @year
set @year_long_name = 'Year - ' + cast(@year as char(4))
set @year_start_date = dateadd(yy, datediff(yy, 0, @date), 0)
set @year_end_date = dateadd(DD, -1, dateadd(yy, 1, @year_start_date))
set @year_span = datediff(d, @year_start_date, @year_end_date) + 1
set @date_id = cast(@year as char(4)) + @padded_month_in_year + @padded_day_in_month
set @date_short_name = @padded_day_in_month + '-' + @month_short_name
set @date_long_name = datename(mm, @date) + ' ' + cast(@day_in_month as varchar(2)) + ', ' + cast(@year as char(4))
set @HalfYearOfYearKey = Case when left(@month_short_name,2) < '07' then 1 else 2 End
SET @HalfYearStartDate =
CASE WHEN @HalfYearOfYearKey = 1 THEN @year_start_date ELSE DATEADD(M,6,@year_start_date) END
SET @HalfYearEndDate = DATEADD(d,-1,dateadd(m,6,@HalfYearStartDate))
SET @HalfYearSpan = Datediff(D,@HalfYearStartDate,@HalfYearEndDate) +1
SET @DaysInHalfYear = Datediff(D,@HalfYearStartDate,@date) +1
Set @HalfYear = Case when left(@month_short_name,2) < '07' then LEFT(@year,4)+ '-01-01' else LEFT(@year,4)+ '-07-01' End
Set @HalfyearText = Case when left(@month_short_name,2) < '07' then '1H-' +right(@year,2) else '2H-' + right(@year,2) End
SET LANGUAGE Danish
SELECT @DateTextDA = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthTextDA = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE British
SELECT @DatetextEN = DATEname(DAY,@date) + ' ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthtextEN = DATENAME(month, @date) + '-' +right(@year,2)
SET LANGUAGE Swedish
SELECT @DateTextSE = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
SELECT @MonthTextSE = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE Norwegian
Set @DateTextNO = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
Set @MonthTextNO = DATENAME(month, @date) + '-' + right(@year,2)
SET LANGUAGE Finnish
Set @DateTextFI = DATEname(DAY,@date) + '. ' + DATENAME(month, @date) + ' ' + Datename(YEAR, @date)
Set @MonthTextFI = DATENAME(month, @date) + '-' +right(@year,2)
Set language English
Insert into Dimensions.Date
Select
@Date_id as DateKey,
@date as Date,
left(@date_short_name,2)+'-'+@month_short_name AS DateText,
--@day_in_week,
--@day_of_week_abbr,
@DateTextDA as DateTextDA,
@DateTextEN as DateTextEN,
@DateTextSE as DateTextSE,
@DateTextNO as DateTextNO,
@DateTextFI as DateTextFI,
@year as YearKey,
@Date as Year,
@HalfYearOfYearKey,
@HalfYear,
@HalfyearText,
HalfyearTextDA = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextEN = case
when left(@HalfyearText,2) = '1H' then '1st Half-year' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2nd Half-year' + '-' + right(@year,2)
end,
HalfyearTextSE = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextNO = case
when left(@HalfyearText,2) = '1H' then '1. Halvår' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Halvår' + '-' + right(@year,2)
end,
HalfyearTextFI = case
when left(@HalfyearText,2) = '1H' then '1. Puolen-vuoden' + '-' + right(@year,2)
when left(@HalfyearText,2) = '2H' then '2. Puolen-vuoden' + '-' + right(@year,2)
end,
QuarterOfyearKey = Case
when left(@QuarterText,2) = 'Q1' then 1
when left(@QuarterText,2) = 'Q2' then 2
when left(@QuarterText,2) = 'Q3' then 3
when left(@QuarterText,2) = 'Q4' then 4
END ,
Quarter = case
when left(@QuarterText,2) = 'Q1' then right(@year,4) + '0101'
when left(@QuarterText,2) = 'Q2' then right(@year,4) + '0401'
when left(@QuarterText,2) = 'Q3' then right(@year,4) + '0701'
when left(@QuarterText,2) = 'Q4' then right(@year,4) + '1001'
end,
@QuarterText,
QuarterTextDA = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextEN = case
when left(@QuarterText,2) = 'Q1' then '1st Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2nd Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3rd Quarter' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4th Quarter' + '-' + right(@year,2)
end,
QuarterTextSE = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextNO = case
when left(@QuarterText,2) = 'Q1' then '1. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Kvartal' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Kvartal' + '-' + right(@year,2)
end,
QuarterTextFI = case
when left(@QuarterText,2) = 'Q1' then '1. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q2' then '2. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q3' then '3. Neljännes' + '-' + right(@year,2)
when left(@QuarterText,2) = 'Q4' then '4. Neljännes' + '-' + right(@year,2)
End,
@month_in_year,
Month = Case
when left(@month_short_name,2) = '01' then right(@year,4) + '-01-01'
when left(@month_short_name,2) = '02' then right(@year,4) + '-02-01'
when left(@month_short_name,2) = '03' then right(@year,4) + '-03-01'
when left(@month_short_name,2) = '04' then right(@year,4) + '-04-01'
when left(@month_short_name,2) = '05' then right(@year,4) + '-05-01'
when left(@month_short_name,2) = '06' then right(@year,4) + '-06-01'
when left(@month_short_name,2) = '07' then right(@year,4) + '-07-01'
when left(@month_short_name,2) = '08' then right(@year,4) + '-08-01'
when left(@month_short_name,2) = '09' then right(@year,4) + '-09-01'
when left(@month_short_name,2) = '10' then right(@year,4) + '-10-01'
when left(@month_short_name,2) = '11' then right(@year,4) + '-11-01'
when left(@month_short_name,2) = '12' then right(@year,4) + '-12-01'
end,
MonthText = left(@month_short_name,2)+'-'+right(@year,2),
@MonthTextDA,
@MonthTextEN,
@MonthTextSE,
@MonthTextNO,
@MonthTextFI,
ISOWeek = right(@year,4)+left(@month_short_name,2),
USWeek = right(@year,4)+left(@month_short_name,2),
ISOWeekKey = DATEPART(ISO_WEEK, @date),
USWeekKey = DATEPART(WW, @date),
@year_span as DaysInYear,
@day_in_year as DaysInYearToDate,
@HalfYearSpan,
@DaysInHalfYear,
@qtr_span as DaysInQuarter,
@day_in_qtr as DaysInQuarterToDate,
@month_span as DaysInMonth,
@day_in_month as DaysInMonthToDate
set @date = dateadd(d, 1, @date)
end
END
Subscribe to:
Posts (Atom)