none
Insert a Record From Master Schedule to Child Table

    질문

  • Hello,

    I need to create a debt record for recurring assessments. 
    The assessments will occur daily, weekly, monthly, quarterly, twice-per-year, and annually; each assessment will have a start date. 
    Assessments (the recurring schedule for each assessment) are attached to matters; 

    The assessments' start date & recurring schedule etc. are in the MattersQCOLLECTION2 table.

    USE [ProLaw_Test]
    GO

    /****** Object:  Table [dbo].[MattersQCOLLECTION2]    Script Date: 7/13/2018 9:27:30 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[MattersQCOLLECTION2](
    [MattersQCOLLECTION2] [varchar](36) NOT NULL,
    [QASAMOUNT] [numeric](12, 2) NULL,
    [QASDEBTTYPE] [varchar](40) NULL,
    [QASSTARTDATE] [datetime] NULL,
    [QASCYCLE] [varchar](40) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [MattersQCOLLECTION2] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    INSERT INTO MattersQCOLLECTION2 VALUES ('36dac023-994e-4724-86e4-36b9c7fd691b', 125, 'Assessment','10/1/2017 0:00','Daily')
    INSERT INTO MattersQCOLLECTION2 VALUES ('30ec210a-0e82-4ded-921c-604509f9034c', 10, 'Late Fee','10/15/2017 0:00','Daily')
    INSERT INTO MattersQCOLLECTION2 VALUES ('7663a4cb-c9c0-405c-bd19-414e3fc5a3b2', 500, 'Assessment','10/1/2017 0:00','Monthly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('3249D429-CAA0-4BE7-A1DD-7022764D5BF3', 50, 'Late Fee','3/15/2018 0:00','Half-yearly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('31C88B9D-A6A7-4CCF-8DE3-49B8EDCB2E84', 10, 'NULL','5/1/2018 0:00','Half-yearly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('31D78C16-E920-4678-A7F5-482444BC9AE4', 190, 'Assessment','5/1/2018 0:00','Yearly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('3209D744-9A90-4603-A132-1AD6E47BBB74', 739.53, 'Special Assessment','5/1/2018 0:00','Daily')
    INSERT INTO MattersQCOLLECTION2 VALUES ('3221E914-F86D-4519-BE88-CD55A8A8B6DB', 195, 'Assessment','5/1/2018 0:00','Monthly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('3236685A-F917-4587-A855-BEB2C87B940E', 50, 'Assessment','6/1/2018 0:00','Monthly')
    INSERT INTO MattersQCOLLECTION2 VALUES ('3236970F-720F-4A04-A57F-9EC16C1675FD', 198, 'Assessment','6/1/2018 0:00','Monthly')

    Sno MattersQCOLLECTION2 QASAMOUNT QASDEBTTYPE QASSTARTDATE QASCYCLE
    1 36dac023-994e-4724-86e4-36b9c7fd691b 125 Assessment 10/1/2017 0:00 Daily
    2 30ec210a-0e82-4ded-921c-604509f9034c 10 Late Fee 10/15/2017 0:00 Daily
    3 7663a4cb-c9c0-405c-bd19-414e3fc5a3b2 500 Assessment 10/1/2017 0:00 Monthly
    4 3249D429-CAA0-4BE7-A1DD-7022764D5BF3 50 Late Fee 3/15/2018 0:00 Half-yearly
    5 31C88B9D-A6A7-4CCF-8DE3-49B8EDCB2E84 10 NULL 5/1/2018 0:00 Half-yearly
    6 31D78C16-E920-4678-A7F5-482444BC9AE4 190 Assessment 5/1/2018 0:00 Yearly
    7 3209D744-9A90-4603-A132-1AD6E47BBB74 739.53 Special Assessment 5/1/2018 0:00 Daily
    8 3221E914-F86D-4519-BE88-CD55A8A8B6DB 195 Assessment 5/1/2018 0:00 Monthly
    9 3236685A-F917-4587-A855-BEB2C87B940E 50 Assessment 6/1/2018 0:00 Monthly
    10 3236970F-720F-4A04-A57F-9EC16C1675FD 198 Assessment 6/1/2018 0:00 Monthly

    I need to create a t-sql that would insert record(s) checking the schedule (daily, monthly, ...) in QASCYCLE field and QASSTARTDATE , if it is daily the t-sql will insert a record daily into a table. 

    If it is monthly the current date needs to be checked to see if it is after a month on the same day, if so add a record. LIkewise for quarterly, half-yearly and annually records needs to be inserted.

    Thanks

    Senthil



    2018년 7월 13일 금요일 오후 4:28

답변

  • It is difficult to give a complete statement since there is no information about the table to insert to.

    But here is a CASE expression you can work form. You will need to add some cycles I did not include.

    SELECT
    CASE QASCYCLE WHEN 'Daily' THEN 1
         WHEN 'Weekly' THEN IIF (datepart(dw, QASTARTDATE) = datepart(dw, getdate()), 1, 0)
         WHEN 'Monthly' THEN IIF (day(QASTARTDATE) = day(getdate()), 1, 0)
         WHEN 'Half-yearly' THEN IIF(day(QASTARTDATE) = day(getdate()) AND datediff(MONTH, QASTARTDATE, getdate()) % 6 = 0, 1, 0)
    END
    
    Note that it will not work that well with QASTARTDATE that falls on the 31st. It will also fail in February if the date is on the 29th or 30th.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2018년 7월 13일 금요일 오후 10:10

모든 응답

  • It is difficult to give a complete statement since there is no information about the table to insert to.

    But here is a CASE expression you can work form. You will need to add some cycles I did not include.

    SELECT
    CASE QASCYCLE WHEN 'Daily' THEN 1
         WHEN 'Weekly' THEN IIF (datepart(dw, QASTARTDATE) = datepart(dw, getdate()), 1, 0)
         WHEN 'Monthly' THEN IIF (day(QASTARTDATE) = day(getdate()), 1, 0)
         WHEN 'Half-yearly' THEN IIF(day(QASTARTDATE) = day(getdate()) AND datediff(MONTH, QASTARTDATE, getdate()) % 6 = 0, 1, 0)
    END
    
    Note that it will not work that well with QASTARTDATE that falls on the 31st. It will also fail in February if the date is on the 29th or 30th.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2018년 7월 13일 금요일 오후 10:10
  • I love reading your posting because it reminds me of how I used a program in the 1960s. A row in RDBMS is nothing whatsoever like a record; a master file is nothing like a table in RDBMS, and the term child was part of network databases in the late 1960s or early 1970s!

    >> Insert a Record [sic] from Master [sic] Schedule to Child [sic] Table. 

    No no no; we insert rows into tables. Your whole vocabulary and mindset are 50 years out of date.

    >> I need to create a debt record [sic] for recurring assessments. <<

    There's no such thing as a debit by itself; you have to take a debit from an account. The debit will be an attribute of your assessment transaction. The assessments will occur daily, weekly, monthly, quarterly, twice-per-year, and annually; each assessment will have a start date. << 

    NO. A start date of some event implies that there is an end date (possibly null to show that is in the future) to match it into what is called an interval data type.  Do you need that end date? 

    Have you read the Rick Snodgrass PDF book on temporal data in SQL? If not, you really need to do so. It can be downloaded for free as a PDF from the University of Arizona the ANSI-ISO standards, which require that the only a format allowed in SQL for dates is “yyyy-mm-dd”; we laugh and people that don’t know this because, next to the metric system, the date formats are the most common display formats on earth. It’s how you tell us you’re not really a database person.

    >> The assessments' start date & recurring schedule etc. are in the MattersQCOLLECTION2 table.<<

    no, the schedule is its own kind of entity, not an attribute of something else.

    While not automatically wrong, table names with numbers in them have a bad code smell. All too often, they are not really tables, but structured arrays hidden in SQL by non-SQL programmers were still speaking their old language.

    CREATE TABLE Mattersqcollection2
    (matters_q_collection_2 VARCHAR(36) NOT NULL PRIMARY KEY, –- probably wrong!!
    qas_amount NUMERIC(12, 2) NOT NULL CHECK (qas_amount >= 0.00), 
    qas_debt_type VARCHAR(40) NOT NULL
     CHECK (qas_debt_type IN (( 'Assessment', 'Late Fee', 'Special Assessment')) ), 
    qas_date DATE NOT NULL
     REFERENCES Something_Periods (period_start_date)
    );

    Are you familiar with normalization in RDBMS? Why didn’t you do it? there should be a table like this in a correct schema
    ;
    CREATE TABLE Something_Periods
    (period_start_date DATE NOT NULL PRIMARY KEY,
     period_end_date DATE NOT NULL,
      CHECK (period_start_date < period_end_date)
     period_type CHAR(12) NOT NULL 
     CHECK (period_type) IN ('Daily', 'Half-yearly', 'Yearly', 'Monthly'));

    Just off hand, it looks like your insanely complicated named column “matters_q_collection_2” is some kind GUID or UUID. This is something no database person would do, because we know the purpose of GUIDs is to identify things external to the database. The letter “G” stands for global, and not local.

    >> I need to create a t-SQL that would insert record(s) [sic]: records are not rows) checking the schedule (daily, monthly, ...) in QAS_CYCLE field [sic] and QAS_START_DATE , if it is daily the T-SQL will insert a record [sic] daily into a table. <<

    >> If it is monthly the current date needs to be checked to see if it is after a month on the same day, if so add a record [sic]. Likewise, for quarterly, half-yearly and annually records [sic] needs to be inserted. <<

    A database person would simply allocate a calendar table for 50 or hundred years one time and be done with it. But you old procedural programming people, will want to use all kinds of temporal computations which are proprietary to your particular SQL. And you’ll keep doing the computations over and over and over. UGH!

    --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

    2018년 7월 15일 일요일 오후 8:59