FILL dim.Date

Question

Post reply

FILL dim.Date

GG_BI_GG

SSCrazy

Points: 2627
More actions
May 5, 2015 at 3:22 am

#317919

My script work always but today it give me a NOT NULL error;
USE [xxx]
GO
/****** Object: Table [DIM].[Date] Script Date: 05/05/2015 11:45:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [DIM].[Date](
[DAY_WID] [int] NOT NULL,
[DAY_DATE] [datetime] NULL,
[DATE_NAME_NL] [varchar](44) NULL,
[DATE_NAME_EN] [varchar](44) NULL,
[WEEKDAY_NUMBER] [int] NOT NULL,
[WEEKDAY_NAME_NL] [varchar](30) NULL,
[WEEKDAY_NAME_EN] [varchar](30) NULL,
[MONTHDAY_NUMBER] [int] NULL,
[YEARDAY_NUMBER] [int] NULL,
[WEEK_WID] [int] NULL,
[WEEK_NUMBER] [smallint] NULL,
[WEEK_NAME_EN] [varchar](30) NULL,
[BUSINESS_WEEK] [int] NULL,
[BUSINESS_WEEK_EN] [varchar](25) NULL,
[BUSINESS_YEAR_WEEK] [int] NULL,
[BUSINESS_YEAR_WEEK_NAME] [varchar](7) NULL,
[BUSINESS_YEAR_WEEK_NAME_DESC] [varchar](7) NULL,
[YEAR_WEEK_NAME] [varchar](9) NULL,
[MONTH_WID] [int] NULL,
[MONTH_NUMBER] [int] NULL,
[MONTH_NAME_NL] [varchar](30) NULL,
[MONTH_NAME_EN] [varchar](30) NULL,
[YEAR_MONTH_NAME_NL] [varchar](41) NULL,
[YEAR_MONTH_NAME_EN] [varchar](41) NULL,
[YEAR_MONTH_NAME] [varchar](9) NULL,
[QUARTER_WID] [int] NULL,
[YEAR_QUARTER_NAME] [varchar](9) NULL,
[QUARTER_NUMBER] [varchar](1) NULL,
[QUARTER_NAME] [varchar](2) NULL,
[QUARTER_NAME_EN] [varchar](30) NULL,
[QUARTER_NAME_NL] [varchar](10) NULL,
[YEAR_NUMBER] [varchar](10) NULL,
[INDICATOR_CURRENT] [smallint] NULL,
[INDICATOR_DELETED] [smallint] NULL,
[DATE_INSERTED] [datetime] NULL,
[DATE_UPDATED] [datetime] NULL,
[DATE_ENDED] [datetime] NULL,
[DATE_DELETED] [datetime] NULL,
[CURRENT_SUMMER] [smallint] NULL,
[CURRENT_WINTER] [smallint] NULL,
[LAST_SUMMER] [smallint] NULL,
[LAST_WINTER] [smallint] NULL,
[BWEEKDAY_NUMBER] [int] NULL,
[QUARTERDAY_NUMBER] [int] NULL,
[FQUARTERDAY_NUMBER] [int] NULL,
[SEASONDAY_NUMBER] [int] NULL,
[FYEARDAY_NUMBER] [int] NULL,
[DAY_CURR_F] [int] NULL,
[DAY_PREV_F] [int] NULL,
[DAY_PREV_YEAR_CURR_F] [int] NULL,
[DAY_PREV_YEAR_PREV_F] [int] NULL,
[DAY_OFFSET] [int] NULL,
[WEEK_CURR_F] [int] NULL,
[WEEK_PREV_F] [int] NULL,
[WEEK_PREV_YEAR_CURR_F] [int] NULL,
[WEEK_PREV_YEAR_PREV_F] [int] NULL,
[WEEK_OFFSET] [int] NULL,
[WTD_CURR_F] [int] NULL,
[WTD_PREV_F] [int] NULL,
[WTD_PREV_YEAR_CURR_F] [int] NULL,
[WTD_PREV_YEAR_PREV_F] [int] NULL,
[WTD_OFFSET] [int] NULL,
[BUSINESS_WEEK_WID] [int] NULL,
[BWEEK_CURR_F] [int] NULL,
[BWEEK_PREV_F] [int] NULL,
[BWEEK_PREV_YEAR_CURR_F] [int] NULL,
[BWEEK_PREV_YEAR_PREV_F] [int] NULL,
[BWEEK_OFFSET] [int] NULL,
[BWTD_CURR_F] [int] NULL,
[BWTD_PREV_F] [int] NULL,
[BWTD_PREV_YEAR_CURR_F] [int] NULL,
[BWTD_PREV_YEAR_PREV_F] [int] NULL,
[BWTD_OFFSET] [int] NULL,
[MONTH_CODE] [varchar](2) NULL,
[FMONTH_WID] [int] NULL,
[FMONTH_NUMBER] [int] NULL,
[FMONTH_CODE] [varchar](2) NULL,
[FYEAR_FMONTH_NAME] [varchar](9) NULL,
[MONTH_CURR_F] [int] NULL,
[MONTH_PREV_F] [int] NULL,
[MONTH_PREV_YEAR_CURR_F] [int] NULL,
[MONTH_PREV_YEAR_PREV_F] [int] NULL,
[MONTH_OFFSET] [int] NULL,
[MTD_CURR_F] [int] NULL,
[MTD_PREV_F] [int] NULL,
[MTD_PREV_YEAR_CURR_F] [int] NULL,
[MTD_PREV_YEAR_PREV_F] [int] NULL,
[MTD_OFFSET] [int] NULL,
[QUARTER_CURR_F] [int] NULL,
[QUARTER_PREV_F] [int] NULL,
[QUARTER_PREV_YEAR_CURR_F] [int] NULL,
[QUARTER_PREV_YEAR_PREV_F] [int] NULL,
[QUARTER_OFFSET] [int] NULL,
[QTD_CURR_F] [int] NULL,
[QTD_PREV_F] [int] NULL,
[QTD_PREV_YEAR_CURR_F] [int] NULL,
[QTD_PREV_YEAR_PREV_F] [int] NULL,
[QTD_OFFSET] [int] NULL,
[FQUARTER_WID] [int] NULL,
[FYEAR_FQUARTER_NAME] [varchar](9) NULL,
[FQUARTER_NUMBER] [varchar](1) NULL,
[FQUARTER_NAME] [varchar](4) NULL,
[FQUARTER_NAME_EN] [varchar](30) NULL,
[FQUARTER_NAME_NL] [varchar](30) NULL,
[FQUARTER_CURR_F] [int] NULL,
[FQUARTER_PREV_F] [int] NULL,
[FQUARTER_PREV_FYEAR_CURR_F] [int] NULL,
[FQUARTER_PREV_FYEAR_PREV_F] [int] NULL,
[FQUARTER_OFFSET] [int] NULL,
[FQTD_CURR_F] [int] NULL,
[FQTD_PREV_F] [int] NULL,
[FQTD_PREV_FYEAR_CURR_F] [int] NULL,
[FQTD_PREV_FYEAR_PREV_F] [int] NULL,
[FQTD_OFFSET] [int] NULL,
[YEAR_WID] [int] NULL,
[YEAR_CODE] [varchar](2) NULL,
[YEAR_CURR_F] [int] NULL,
[YEAR_PREV_F] [int] NULL,
[YEAR_OFFSET] [int] NULL,
[YTD_CURR_F] [int] NULL,
[YTD_PREV_F] [int] NULL,
[YTD_OFFSET] [int] NULL,
[FYEAR_WID] [int] NULL,
[FYEAR_NUMBER] [varchar](10) NULL,
[FYEAR_CODE] [varchar](2) NULL,
[FYEAR_CURR_F] [int] NULL,
[FYEAR_PREV_F] [int] NULL,
[FYEAR_OFFSET] [int] NULL,
[FYTD_CURR_F] [int] NULL,
[FYTD_PREV_F] [int] NULL,
[FYTD_OFFSET] [int] NULL,
[SEASON_WID] [int] NULL,
[SEASON_NUMBER] [int] NULL,
[SEASON_NAME] [varchar](10) NULL,
[FYEAR_SEASON_NAME] [varchar](15) NULL,
[SEASON_CURR_F] [int] NULL,
[SEASON_PREV_F] [int] NULL,
[SEASON_PREV_FYEAR_CURR_F] [int] NULL,
[SEASON_PREV_FYEAR_PREV_F] [int] NULL,
[SEASON_OFFSET] [int] NULL,
[STD_CURR_F] [int] NULL,
[STD_PREV_F] [int] NULL,
[STD_PREV_FYEAR_CURR_F] [int] NULL,
[STD_PREV_FYEAR_PREV_F] [int] NULL,
[STD_OFFSET] [int] NULL,
CONSTRAINT [PK_DIM_DAY] PRIMARY KEY CLUSTERED
(
[DAY_WID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DIM_TABLES]
) ON [DIM_TABLES]
GO
SET ANSI_PADDING OFF
GO
(100000 row(s) affected)
Msg 515, Level 16, State 2, Line 27
Cannot insert the value NULL into column 'WEEKDAY_NUMBER', table 'xxx.DIM.Date'; column does not allow nulls. INSERT fails.
TRUNCATE TABLE DIM.Date
-- 3. First create DateKey and Date fields.
When its going to insert I get the error!!!
INSERT INTO DIM.Date (DAY_WID, DAY_Date)
SELECT CONVERT(int,CONVERT(varchar(8),DATEADD(day, number_big, '2000-01-01'),112)),
DATEADD(day, number_big, '2000-01-01') as Date
FROM numbers_big
WHERE DATEADD(day, number_big, '2000-01-01') BETWEEN '2000-01-01'
AND '2020-12-31'
ORDER BY number_big;
/*In above step, the date dimension records will be created for year
2000 to 2020. Change the dates in the above statement to change the
range.
--4. Update all other fields with appropriate data.
*/
INSERT INTO DIM.Date (DAY_WID, DAY_Date)
VALUES ('19000101','19000101') --9999-12-31 failed
GO
INSERT INTO DIM.Date (DAY_WID, DAY_Date)
VALUES ('19010101','19010101')
INSERT INTO DIM.Date (DAY_WID, DAY_Date)
VALUES ('18000101','18000101')
INSERT INTO DIM.Date (DAY_WID, DAY_Date)
VALUES ('99991230','99991230')
UPDATE DIM.Date
SET
MONTH_WID = CONVERT(int,LEFT(CONVERT(varchar(8),DAY_DATE,112), 6)),
WEEK_NUMBER= DATEPART(WK,DAY_DATE),
WEEK_NAME_EN = 'Week ' + convert(char(2),(datepart(wk, DAY_DATE))),
BUSINESS_WEEK= dbo.f_isoweek(DAY_DATE),
BUSINESS_WEEK_EN = 'Week ' + convert(char(2),dbo.f_isoweek(DAY_DATE)),
MONTH_NUMBER = MONTH(DAY_DATE),
MONTH_NAME_EN= DATENAME(MONTH,DAY_DATE),
Ect,ect,ect

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

The table you gave us has

create table

.

[WEEKDAY_NUMBER] [int] NOT NULL,

.

INSERT INTO Date (DAY_WID, DAY_Date)

VALUES ('19000101','19000101') --9999-12-31 failed

Since you are not inserting into weekday_number, the insert must break.

This will not break if you have a default constraint on weekday_number.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 2

I fix this problem with a update storeprocedure;

-- Set to date flags

MERGE DIM.DATE AS target

USING

(

SELECT

DATE.DAY_WID

-- Week

,CASE WHEN DATE.WEEK_CURR_F = 1 AND

DATE.WEEKDAY_NUMBER <= CWN.WEEKDAY_NUMBER THEN 1 ELSE 0 END AS

WTD_CURR_F

,CASE WHEN DATE.WEEK_PREV_F = 1 AND

DATE.WEEKDAY_NUMBER <= CWN.WEEKDAY_NUMBER THEN 1 ELSE 0 END AS

WTD_PREV_F

,CASE WHEN DATE.WEEK_PREV_YEAR_CURR_F = 1 AND

DATE.WEEKDAY_NUMBER <= CWN.WEEKDAY_NUMBER THEN 1 ELSE 0 END AS

WTD_PREV_YEAR_CURR_F

,CASE WHEN DATE.WEEK_PREV_YEAR_PREV_F = 1 AND

DATE.WEEKDAY_NUMBER <= CWN.WEEKDAY_NUMBER THEN 1 ELSE 0 END AS

WTD_PREV_YEAR_PREV_F

,CASE WHEN DATE.WEEKDAY_NUMBER <=

CWN.WEEKDAY_NUMBER THEN DATE.WEEK_OFFSET ELSE NULL END AS

WTD_OFFSET