May 7, 2019 at 8:58 pm
hi,
I have table with loans and different steps for processing ,
| loan_number | template_name | step_setup_date | expiration_date | activeindicator |
|---|---|---|---|---|
| 12345 | Step1 | 6/7/2016 | 1/30/2017 | 0 |
| 12345 | Step2 | 6/7/2016 | 1/30/2017 | 0 |
| 12345 | Step3 | 1/30/2017 | NULL | 1 |
| loan_number | template_name | step_setup_date | expiration_date | activeindicator |
|---|---|---|---|---|
| 4567 | Step1 | 12/31/2016 | 1/30/2017 | 0 |
| 4567 | Step2 | 1/30/2017 | 10/12/2017 | 0 |
| 4567 | Step3 | 10/12/2017 | NULL | 1 |
I am working on expiration_date,active_record_indicator.
How to get the these two fields calculated as above?
Active indicator is 1 based on latest stepdate. Expiration date for taht step is stepdate with latest date.
"Expiration date " is the date when the step(step1, step 2 , step 3) is expired i.e if setup_step_Date is in past , compared to other steps ( i.e in this case step 1 for loan 12345 has 6/7/2016 , step 2 has 6/7/2016 when compared with step3 which has latest date 1/30/2017, so the expiration_date for step1 and step 2 will be '1/30/2017' which is the lastest step date out of 3 step dates for laon 12345 ). But for Loan 4567 , step 1 has 12/31/2016 will have expiration date of step2 "setupdate" as step2 has latest date comapred to step1 , step 2 will get the expirastion date from step3 stepsetupdate.FYI. both expiration date and active indicator are computed columns.
Here is the point. For "expirationdate" column for that step - compare the dates from below immediate occurring steps. If the immediate occurring step has same date as its "stepsetupdate" , then compare with the next occurring stepsetupdate.
CREATE TABLE #Loan( Loan_number int
,template_name varchar(10)
, setup_step_Date date
,expiration_date date
,activeindicator int )
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (12345, 'Step1','6/7/2016')
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (12345, 'Step2','6/7/2016')
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (12345, 'Step3','1/30/2017')
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (4567, 'Step1','12/31/2016')
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (4567, 'Step2','1/30/2017')
insert into #Loan (Loan_Number, template_name,Setup_step_date)
values (4567, 'Step3','10/12/2017')
May 7, 2019 at 11:42 pm
If you have a process that is adding steps, then that is the best place to maintain the records. First, update the existing current step to end the process:
UPDATE{{tablename}}
SETexpiration_date = GETDATE(),
activeindicator = 0
WHEREloan_number = {{loannumber}}
ANDactiveindicator = 1
Then add the new record, with step_setup_date = GETDATE() and activeindicator = 1.
May 8, 2019 at 2:19 pm
sometimes two or more steps come into table on same day. As we load data from files. So the above query does not work. 🙁
May 8, 2019 at 2:44 pm
What is latest, and what is "expiration is latest step date"? You don't have a step date in your data.
It's a little unclear what you are asking for here, and also, what data you have to work with. If you get multiple steps on the same date, then your sample data ought to include that.
You also ought to provide DDL and sample data, as in:
CREATE TABLE Loan( Loan_number int, setup_step_Date date, ...)
insert Loan (Loan_Number, Setup_step_date) values (1, '20170101'), (2, '20180201'), ...
?
May 8, 2019 at 2:54 pm
This was removed by the editor as SPAM
May 8, 2019 at 3:32 pm
This was removed by the editor as SPAM
May 8, 2019 at 3:33 pm
If you have these as computed columns, then your DDL should explain that.
When you write " if it is in past we expire it meaning stamp it with latest "Stepsetupdate"", this makes no sense to us. We don't work in your industry, so what is "it"? which column? It is helpful for us to work with you if you more clearly state, with an example, what you mean. Don't write "in example", write "for loan 12345, when the date is past June 7 for Step 1, we should have the expiration date marked as June 7, 2016 and the activeindicator set to 0".
Right now, it is unclear from outside your problem domain what you mean.
May 8, 2019 at 4:32 pm
just FYI. I just edited my post with the dates and tried to explain more about the "Expirationdate" computed column. I dont know why i cannot see my reply to your post , which has all details. So , i just edited my actual post.
May 8, 2019 at 5:39 pm
>> I have table with loans and different steps for processing , <<
Really? Where is the DDL for this table? Why don't you follow Netiquette? Why did you use a local format for dates?? If you had read any book on SQL , you would know that the ONLY format in ANSI-ISO Standard SQL is ISO-8601 "yyyy-mm-dd".
Try this:
CREATE TABLE Loans
(loan_number CHAR(5) NOT NULL,
template_name CHAR(7) NOT NULL,
PRIMARY KEY (loan_number, template_name),
step_setup_date DATE NOT NULL,
expiration_date DATE,
CHECK (step_setup_date <= expiration_date),
non_relational_flg SMALLINT NOT
CHECK (non_relational_flg IN (0,1))
);
INSERT INTO Loans
VALUES
('12345', 'Step1', '2016-06-07', '2016-06-07', 0)
('12345', 'Step2', '2016-06-07', '2016-06-07', 0),
('12345', 'Step3', '2017-01-30', NULL, 1);
>> How to get the these two fields [sic: columns are not fields!] calculated as above? <<
WHY? This 1960's assembly language flag is redundant and non-relational. If we have an expiry date, then we know the step is complete.
Please Google an article of mine on state transition constraints.
>> I have table with loans and different steps for processing , <<
Really? Where is the DDL for this table? Why don't you follow Netiquette? Why did you use a local format for dates?? If you had read any book on SQL , you would know that the ONLY format in ANSI-ISO Standard SQL is ISO-8601 "yyyy-mm-dd".
Try this:
CREATE TABLE Loans
(loan_number CHAR(5) NOT NULL,
template_name CHAR(7) NOT NULL,
PRIMARY KEY (loan_number, template_name),
step_setup_date DATE NOT NULL,
expiration_date DATE,
CHECK (step_setup_date <= expiration_date),
non_relational_flg SMALLINT NOT
CHECK (non_relational_flg IN (0,1))
);
INSERT INTO Loans
VALUES
('12345', 'Step1', '2016-06-07', '2016-06-07', 0)
('12345', 'Step2', '2016-06-07', '2016-06-07', 0),
('12345', 'Step3', '2017-01-30', NULL, 1);
>> How to get these two fields [sic: columns are not fields!] calculated as above? <<
WHY? This 1960's assembly language flag is redundant and non-relational. If we have an expiry date, then we know the step is complete.
Please Google an article of mine on state transition constraints.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 8, 2019 at 6:07 pm
JUST FYI. This sql was just an example i provided to explain others , who is indeed trying to help me. I am not really at this moment concentrating on writing entire formatted SQL just to explain others and That is one of the reason i have it in temp table. Also, This is not my actual table , how i built in my actual Database. I just created a table to explain with examples. I normally follow all ANSI standards to creating DB objects. Hope you understand . Instead of jumping and pointing to others , If you please help in getting answer to my question that will be really grateful.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply