Creating View with More Records than original table
-
I am not sure if this can be done but I would like to create a view (or 2nd option a new table that gets updated each night).
I have a table that is updated each night from a completely different database and I then want to create a report that shows the total Investments falling due each month based on the assumption that the investment will be renewed for the same term and the same amount.
My logic is to create a view (or table) that will check if the maturity date is less than the current date plus 1,100 (being the approx. number of days in the next 3 years as the maximum term of the investment is 3 years). If the maturity date is less than the current date plus 1100 days it would be added but also the script would then add another record with the lodgement date being the maturity date of the original investment and the maturity date of this new record being the new lodgement date plus the term in days. The script would than check if that maturity date is less than the current date plus 1100 days. If not add another record with the lodgement date being the maturity date of the 2nd record for that certificate and so on.
I imagine that I would use a “Maximum” logic but not sure where to start.
Example:
Current Table View or New Table ResultCert No Lodge Date Mat Date Term Amount Cert No Lodge Date Mat Date Term Amount 1 11/02/2017 11/02/2018 365 $ 5,000 1 11/02/2017 11/02/2018 365 $ 5,000 2 15/06/2017 14/09/2017 91 $ 6,500 2 15/06/2017 14/09/2017 91 $ 6,500 3 30/06/2017 29/09/2017 91 $ 7,500 3 30/06/2017 29/09/2017 91 $ 7,500 4 16/07/2017 14/09/2017 60 $ 15,000 4 16/07/2017 14/09/2017 60 $ 15,000 5 1/08/2017 1/09/2017 31 $ 1,500 5 1/08/2017 1/09/2017 31 $ 1,500 5 1/09/2017 2/10/2017 31 $ 1,500 4 14/09/2017 13/11/2017 60 $ 15,000 2 14/09/2017 14/12/2017 91 $ 6,500 3 29/09/2017 29/12/2017 91 $ 7,500 5 2/10/2017 2/11/2017 31 $ 1,500 5 2/11/2017 3/12/2017 31 $ 1,500 4 13/11/2017 12/01/2018 60 $ 15,000 5 3/12/2017 3/01/2018 31 $ 1,500 2 14/12/2017 15/03/2018 91 $ 6,500 3 29/12/2017 30/03/2018 91 $ 7,500 So my questions are
1. Can I create a view that has MORE records than the table that I am extracting data from?
2. Any suggestions on how to get the system to cycle through the highest maturity date and add another record (if applicable) before going on to the next record from the original table? Any web sites or links that might direct me in the right direction?
Once I have this view or new table I can do the report but just cannot seem to work out how to get the view or new report. A view is the preferred option as it updates automatically as the original table gets updated each night.
Many thanks 🙂 - les.61 - Wednesday, August 23, 2017 8:19 PM
1. Yes. A view is just a predefined SELECT statement, and doesn't have to select from one table, or could return records multiple times from the same one.
2. I'm not entirely sure I understand your question, in all honesty. Someone else might, but It would be good if you could outline your logic in a step by step process for an individual record, it'll definitely help the understanding. Also, please ensure you provide your data in a consumable format. Can you post DDL and DLM please? Have a look at the link in my signature on how to do this.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Like Thom said already, it is quite possible to create such a view.
To select such a result I suggest to use a tally date table. Such a table contains basically all dates between a certain range (like from 2000-1-1 till 2030-1-1). If you combine this tally table with an OUTER JOIN to your base table, it is rather easy to add the missing timerange from your base table to your final result. From your sample it looks like the column [Term] contains the number of days between [Lodge Date] and [Mat Date]. I cant read in your description how to determine the values for column [Mat Date] and [Amount] for those new rows. You can use the query below as base to start building your end solution:. The COALESCE is used to display the value from the row from the base table when available or show an alternative when the row from the base table does not exists.
SELECT COALESCE(base.[Lodge date], tally.[date]) as 'Lodge Date'
, COALESCE(base.[Mat Date], dateadd(day, 1, tally.[date])) as 'Mat Date'
, COALESCE(base.[Term], 1) as 'Term'
, COALESCE(base.[Amount], '$ 50') as 'Amount'
from [your_table] base
right outer join [tally_date] tally
on base.[Lodge Date] = tally.[date]
where tally.[date] > '20170101'
AND tally.[date] < dateadd(day, 1100, getdate())** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Using a Tally table of integers
SET DATEFORMAT DMY;
WITH cte (CertNo,LodgeDate,MatDate,Term,Amount) AS (
SELECT CertNo,LodgeDate,MatDate,Term,Amount
FROM (VALUES (1,CAST('11/02/2017' as date),CAST('11/02/2018' as date),365,5000),
(2,CAST('15/06/2017' as date),CAST('14/09/2017' as date),91,6500),
(3,CAST('30/06/2017' as date),CAST('29/09/2017' as date),91,7500),
(4,CAST('16/07/2017' as date),CAST('14/09/2017' as date),60,15000),
(5,CAST('01/08/2017' as date),CAST('01/09/2017' as date),31,1500)
) a (CertNo,LodgeDate,MatDate,Term,Amount)
)
SELECT CertNo,LodgeDate,MatDate,Term,Amount FROM cte
UNION ALL
SELECT CertNo,DATEADD(day,Term*(N-1),MatDate),DATEADD(day,Term*N,MatDate),Term,Amount
FROM cte
JOIN dbo.Tally ON N BETWEEN 1 AND 35
WHERE DATEADD(day,Term*N,MatDate) < DATEADD(day,1100,GETDATE());Far away is close at hand in the images of elsewhere.
Anon. -
I have looked at all the responses and still having trouble trying to get it to work. I hope I can explain it a bit better.
The table is created and data added as follows:
IF OBJECT_ID('MaturityTest1','U') IS NOT NULL
DROP TABLE MaturityTest1
/****** Object: Table [dbo].[MaturityTest] Script Date: 25/08/2017 12:09:25 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[MaturityTest1](
[CertNo] [float] NULL,
[LodgeDate] [datetime] NULL,
[MatDate] [datetime] NULL,
[TermDays] [float] NULL,
[Amount] [money] NULL
) ON [PRIMARY]
GO
--===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT MaturityTest1 ON--===== Insert the test data into the test table
INSERT INTO [dbo].MaturityTest1
(CertNo, LodgeDate, MatDate, TermDays, Amount)
SELECT '1','Feb 11 2017','Feb 11 2018','365','5000' UNION ALL
SELECT '2','Jun 15 2017','Sep 14 2017','91','6500' UNION ALL
SELECT '3','Jun 30 2017','Sep 29 2017','91','7500' UNION ALL
SELECT '4','Jul 16 2017','Sep 14 2017','60','15000' UNION ALL
SELECT '5','Aug 01 2017','Sep 01 2017','31','1500'--===== Set the identity insert back to normal
--SET IDENTITY_INSERT MaturityTest1 ONI also created a 2nd table as follows:
USE [LesTest]
GO/****** Object: Table [dbo].[Dates2100] Script Date: 25/08/2017 12:35:12 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Dates2100a](
[Date] [datetime] NULL
) ON [PRIMARY]
GO
I filled this with every date from the following (I did not know how to insert this straight into the table so copied to Excel and imported to Table)WITH Dates AS (
SELECT
[Date] = CONVERT(DATETIME,'01/01/2017')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '01/01/2106'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 32765)I then created the following script
select CertNo, LodgeDate, MatDate, TermDays, Amount
,CertNo,DATEADD(day,TermDays,LodgeDate) as Lodgedate,DATEADD(day,TermDays,MatDate) as MatDate,TermDays,Amount
from MaturityTest
left outer join [dbo].[Dates2100]
on lodgedate = date
where LodgeDate < dateadd(day, 1100, getdate()) and certno like '1'The certno like '1' was just to simplify the end result for testing and explanation
The result was
CertNo LodgeDate MatDate TermDays Amount CertNo Lodgedate MatDate TermDays Amount
1 2017-02-11 00:00:00.000 2018-02-11 00:00:00.000 365 5000.00 1 2018-02-11 00:00:00.000 2019-02-11 00:00:00.000 365 5000.00However I want the result to look like
CertNo
LodgeDate
MatDate
TermDays
Amount
1
2017-02-11 00:00:00.000
2018-02-11 00:00:00.000
365
5000.00
1
2018-02-11 00:00:00.000
2019-02-11 00:00:00.000
365
5000.00
1
2019-02-11 00:00:00.000
2020-02-11 00:00:00.000
365
5000.00
1
2020-02-11 00:00:00.000
2021-02-10 00:00:00.000
365
5000.00
As you can see what I am trying to create is a list of the current LodgeDate, MatDay, Term and Amount plus any future dates (assuming the term and amount stays the same) at will occur until the Lodgement Date is greater than the current date plus 1,100 days.Any assistance in pointing me in the right direction is appreciated.
JeffM is this the DDL and DLM that you talked about? Sorry I could not do the insert into Dates2100 but just no idea how to do it.
Many thanks
-
I've completed your query sample to include the filling of the tally table.
From your sample I notice a few mistakes. First you need a RIGHT outer join instead of a LEFT, because the tally table contains all values of the date column you need. Second you don't have any logic in place on how to handle non matching rows. Just showing the logic as additional columns doesn't handle the non-matching rows. You'll need to add them inside a COALESCE function.I have taken your sample, added the filling of the tally and at the bottom I've added two queries. The first one displays all selected rows from the tally table and only includes the matching rows from the MaturityTest1 table. When no match is found, the values remain a NULL. I think the second SELECT is more to your desired solution. This one has put the logic inside the COALESCE, so every row contains non-NULL values. Keep in mind the non-matching rows also doesn't have values for "TermDays" and "Amount", so using these columns inside the replacing logic doesn't make any sense. That's why I have used fixed values instead.
IF OBJECT_ID('MaturityTest1','U') IS NOT NULL
DROP TABLE MaturityTest1
/****** Object: Table [dbo].[MaturityTest] Script Date: 25/08/2017 12:09:25 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [MaturityTest1](
[CertNo] [float] NULL,
[LodgeDate] [datetime] NULL,
[MatDate] [datetime] NULL,
[TermDays] [float] NULL,
[Amount] [money] NULL
) ON [PRIMARY]
GO
--===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT MaturityTest1 ON--===== Insert the test data into the test table
INSERT INTO MaturityTest1
(CertNo, LodgeDate, MatDate, TermDays, Amount)
SELECT '1','Feb 11 2017','Feb 11 2018','365','5000' UNION ALL
SELECT '2','Jun 15 2017','Sep 14 2017','91','6500' UNION ALL
SELECT '3','Jun 30 2017','Sep 29 2017','91','7500' UNION ALL
SELECT '4','Jul 16 2017','Sep 14 2017','60','15000' UNION ALL
SELECT '5','Aug 01 2017','Sep 01 2017','31','1500'-- create a date table and fill it with every day from the year 2017 ÷ 2020
IF OBJECT_ID('Tally_Date') IS NOT NULL
DROP TABLE Tally_Date;
CREATE TABLE Tally_Date (
[Date] datetime2(0)
);
INSERT INTO Tally_Date
SELECT DATEADD(day, Num - 1, '2017-01-01T00:00:00')
FROM (
SELECT TOP 1461 ROW_NUMBER() OVER(ORDER BY i1.TABLE_CATALOG) AS Num
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2
) sub;-- query below shows all dates (taken from the TALLY table) and includes values from matching rows from table #MaturityTest1.
-- The values remain NULL if there is no matching row.
select date, CertNo, LodgeDate, MatDate, TermDays, Amount,CertNo,DATEADD(day,TermDays,LodgeDate) as Lodgedate,DATEADD(day,TermDays,MatDate) as MatDate,TermDays,Amount
from MaturityTest1
right outer join Tally_Date
on lodgedate = date
where date < dateadd(day, 1100, getdate()) --and certno like '1'
order by date;-- query below shows all dates (taken from the TALLY table) and includes values from matching rows from table #MaturityTest1.
-- The NULL values are replaced within the COALESCE function to a derived value (DATEADD) or a fixed value.
SELECT COALESCE(base.[Lodgedate], tally.[date]) as 'Lodge Date'
, COALESCE(base.[MatDate], dateadd(day, 1, tally.[date])) as 'Mat Date'
, COALESCE(base.[TermDays], 1) as 'Term'
, COALESCE(base.[Amount], '$ 50') as 'Amount'
from [MaturityTest1] base
right outer join [tally_date] tally
on base.[LodgeDate] = tally.[date]
where tally.[date] > '20170101'
AND tally.[date] < dateadd(day, 1100, getdate())
order by tally.Date;** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Btw: if you need to fill non-matching rows using information from other (previous) rows, take a look at the LEAD and LAG functions. This article on DatabaseJournal.com has a nice explanation and clear example.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply