locked
Update Current,Previous Dates in Date Dimension Using SQL RRS feed

  • Question

  • i have a dimDate table with dates for all months.I have a process which updates the currentMonth column in DImDate on a monthly basis .My result set should be :
    If i am running my Process today then ,

    CurrentMonth =1 where calendarMonthYear ='Jun-2016'

    CurrentMonth=0 where calendarMonthYear =May-2016'

    CurrentMonth=-1 where calendarMonthYear =April-2016' and so on.


    If i am running my job in July then :

    CurrentMonth =1 where calendarMonthYear ='Jul-2016'

    CurrentMonth=0 where calendarMonthYear =jun-2016'

    CurrentMonth=-1 where calendarMonthYear =May-2016' and so on.

    CREATE TABLE [dbo].[sampleDate]( [Date] [datetime] NOT NULL, [calendarMonthYear] [varchar](15) NOT NULL, [CurrentMonth] [int] NULL ) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-05-01 00:00:00.000' AS DateTime), N'May-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-05-02 00:00:00.000' AS DateTime), N'May-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-05-03 00:00:00.000' AS DateTime), N'May-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-05-04 00:00:00.000' AS DateTime), N'May-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-05-05 00:00:00.000' AS DateTime), N'May-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-01 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-02 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-03 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-04 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-05 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-06-30 00:00:00.000' AS DateTime), N'Jun-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-01 00:00:00.000' AS DateTime), N'Jul-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-02 00:00:00.000' AS DateTime), N'Jul-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-03 00:00:00.000' AS DateTime), N'Jul-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-04 00:00:00.000' AS DateTime), N'Jul-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-05 00:00:00.000' AS DateTime), N'Jul-2016', NULL) INSERT [dbo].[sampleDate] ([Date], [calendarMonthYear], [CurrentMonth]) VALUES (CAST(N'2016-07-31 00:00:00.000' AS DateTime), N'Jul-2016', NULL)


    Monday, June 27, 2016 7:44 PM

Answers

  • Hi,

      Thanks for the table struucture and sample data

       Try the below code

    SELECT Date
    	,CalendarMonthyear
    	,DATEDIFF(MM,GETDATE(),Date) + 1 [CurrentMonth]
      FROM [sampleDate]


    Best Regards Sorna

    Monday, June 27, 2016 8:03 PM

All replies

  • Hi,

      Thanks for the table struucture and sample data

       Try the below code

    SELECT Date
    	,CalendarMonthyear
    	,DATEDIFF(MM,GETDATE(),Date) + 1 [CurrentMonth]
      FROM [sampleDate]


    Best Regards Sorna

    Monday, June 27, 2016 8:03 PM
  • Thanks Sorna.It Worked :)

    Monday, June 27, 2016 8:10 PM
  • Let us clean up your DDL first. Do not use camel case; it does not work. Your I jumps to the uppercase letter, then back to the start of the word which adds about 8% more time to reading the code. Next, do not assume that the whole world is based on English three letter abbreviations for month names; they they do not sort correctly or port. Next you need the idea of an ordinal month, over your whole calendar table. Finally, you need to learn the current syntax for an insertion statement; we have row constructors today and have for the last decade.

    We also have a date data type! I like the MySQL convention of using the ISO 8601 format with added double zeros for a month within a year, or the day within a month. They been proposed for in ISO standard, they sort and they are language independent. Here is a rewrite of your DDL:


    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
     cal_month CHAR(10) NOT NULL,
     ordinal_month INTEGER NOT NULL);


    INSERT INTO Calendar 
    VALUES 
    ('2016-05-01', '2016-05-00', 1),
    ('2016-05-02', '2016-05-00', 1),
    ('2016-05-03', '2016-05-00', 1),
    ('2016-05-04', '2016-05-00', 1),
    ('2016-05-05', '2016-05-00', 1),
    ('2016-06-01', '2016-06-00', 2),
    ('2016-06-02', '2016-06-00', 2),
    ('2016-06-03', '2016-06-00', 2),
    ('2016-06-04', '2016-06-00', 2),
    ('2016-06-05', '2016-06-00', 2),
    ('2016-06-30', '2016-06-00', 2),
    ('2016-07-01', '2016-07-00', 3),
    ('2016-07-02', '2016-07-00', 3),
    ('2016-07-03', '2016-07-00', 3),
    ('2016-07-04', '2016-07-00', 3),
    ('2016-07-05', '2016-07-00', 3),
    ('2016-07-31', '2016-07-00', 3);


    >> have a dimDate table with dates for all months.I have a process which updates the currentMonth column in DImDate on a monthly basis. <<

    Why? We try to avoid needless disk access, especially on static data. Simply add an ordinal month column to give a sequence number two all the data in your calendar table. The query is now trivial

    ;WITH
    Current_Month
    AS
    (SELECT cal_month, ordinal_month
     FROM Small_Calendar
     WHERE cal_date = CAST(CURRENT_TIMESTAMP AS DATE))

    SELECT DISTINCT C.cal_month, 
            (C.ordinal_month - M.ordinal_month) AS displacement
      FROM current_month AS M, Small_Calendar AS C;

    Please notice the this will set the current month zero, and give relative positive or negative displacements to the rest of the calendar, not just two months. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, June 27, 2016 9:56 PM