February 10, 2014 at 6:29 am
Hi,
I am relatively new to SQL programming and have the following issue:
I want to insert a number of records in a table depending on the value in a field (numberofperiods) in my projects subtable.
These records have several fields and one of the of the end-of-month date. The first record that has to be inserted will have the end date of the month from the startdate of my projects table.
An example will make this more clear:
My projects subtable (aliased "Np" below) has a field "numberofperiods". Let's say it has a value of 18.
The start date is in field "initialstartdate" of my Projects-table (PRProject) and has a value of "2013-03-15".
I would like to insert 18 rows in my table "ProjectTemp" (which is an actual table) with the following values:
- Field "Projectno" -> Projectnumber from my Project table
- Field "End of month" -> Enddate for all 18 months beginning with 2013-03-31 for the first row; 2013-04-30 for the second; 2013-05-31 for the third row, 2013-06-30 for fourth row etc. until all 18 rows are populated with the end date.
- Field "Projectstatus" -> Status from my Project table.
- Field "Amount" -> calculated value
So below for the query I have made so far.
The end-of-month date is now populated by getdate() because I don't know how to insert the end-of-month (eom) date dynamically.
I have made a function with generates the number of periods and applies it to the query using CROSS APPLY.
My point is how to insert the eom date for all (in this example) 18 rows in order to replace the getdate() that is in the query now.
See below. Every record has now the getdate, but should be the eom-date.
INSERT INTO ProjectTemp
(
Projectno,
EndOfMonth,
Status,
Amount
)
(
SELECT
a.Projectnumber,
GETDATE() AS EndOfMonth,
p.Status AS Status,
ROUND(Ap.SUBAmountRequested / Np.NumberOfPeriods, 2) AS Amount
FROMAbsences a (nolock)
LEFT OUTER JOIN PRProject p ON a.ProjectNumber = p.ProjectNr
LEFT OUTER JOIN CSNobEntYesNoFields YNp ON p.ProjectNr = YNp.ProjectNr
LEFT OUTER JOIN CSNobEntNumberFields Np ON p.ProjectNr = Np.ProjectNr
LEFT OUTER JOIN CSNobEntAmountFields Ap ON p.ProjectNr = Ap.ProjectNr
LEFT OUTER JOIN CSNobEntListFields Lp ON p.ProjectNr = Lp.ProjectNr
CROSS APPLY dbo.CSNob_FN_GenerateLinesPaymentschema (ISNULL(CONVERT(INT, Np.SUBTerms), 12))
WHEREa.ProjectNumber = 'SUB59'
AND a.Type = 9011
)
The function dbo.CSNob_FN_GenerateLinesPaymentschema to generate the 18 terms looks like this:
USE [Test]
GO
/****** Object: UserDefinedFunction [dbo].[CSNob_FN_GenerateLinesPaymentschema] Script Date: 10-2-2014 10:18:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CSNob_FN_GenerateLinesPaymentschema](@quantity AS int )
RETURNS @NumberLines TABLE (Number FLOAT) AS
BEGIN
DECLARE @NumberStart AS int
SELECT @NumberStart = CASE WHEN @quantity < 0 THEN (@quantity*-1) ELSE @quantity END
INSERT INTO @NumberLines (Number)
VALUES (@NumberStart)
BEGIN
DECLARE @counter INT
SET @counter = @NumberStart
WHILE @counter <> 1
BEGIN
SET @counter = @counter - 1
INSERT INTO @NumberLines (Number)
VALUES (@counter)
END
END
RETURN;
END
GO
Thanks!
Regards,
Michiel
February 10, 2014 at 6:58 am
I'm not sure where to start here. I suppose I'll go with the function.
The function you have won't have a good performance and you should replace it with a set based approach to get an inLine-Table Function. Here's one option that will return up to 10,000 rows.
CREATE FUNCTION [dbo].[GenerateNumbers](@quantity AS int )
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH E1(N) AS(
SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b -- 10 * 10 = 100 lines
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b -- 100 * 100 = 10,000 lines
),
cteTally(N) AS(
SELECT TOP(@quantity) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E4
)
SELECT *
FROM cteTally
Note that your original function was working with integers but returning float values. That is wrong and should be avoided, especially if you only need integer values.
For the date is easier, you just need to use the date functions.
--Adds the number of months plus one between date zero(1900-01-01) and today
--to the day before date zero(1989-12-31)
SELECT DATEADD( MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)
If you have any questions, feel free to ask.
PS. Maybe you could remove CSNobEntYesNoFields from your query.
February 10, 2014 at 7:12 am
;WITH MyData AS (
SELECT SUBTerms = 2, initialstartdate = CAST('2013-03-15' AS DATE) UNION ALL SELECT 4, '2013-01-15' UNION ALL SELECT 6, '2013-05-15'
)
-- MyData is a mockup of your existing result set
SELECT
Np.SUBTerms, Np.initialstartdate,
x.number,
newdate = DATEADD(MONTH,number+DATEDIFF(MONTH,0,np.initialstartdate),-1)
FROM MyData np
CROSS APPLY (
SELECT TOP(Np.SUBTerms)
number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 10, 2014 at 12:42 pm
As a bit of a sidebar, I would never use Month END dates... not on a bet. I would always use the first of the next month as a non-inclusive cutoff date which is also the best way to structure temporal WHERE clauses, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply