March 1, 2012 at 2:02 pm
Greetings everyone, I will do my best to explain my issue. I apologize if I am missing any required information, I am very new to writing SQL Queries.
I am currently running SQL Server 2008 R2 and I will be inserting this query into a SSRS Report to be published in a SharePoint 2010 Environment.
The goal is to be able to forecast incoming payments from a table that contains a payment schedule. Here is a sample of the information contained in the table:
SELECT
CONVERT(DATE, '2012-02-01', 111) AS 'Posting Date'
,'131224' AS 'Customer ID'
,'John Smith' AS 'Customer Name'
,CONVERT(MONEY, '25') AS 'Amount'
,NULL AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-02-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-02-15', 111) AS 'Posting Date'
,'213234' AS 'Customer ID'
,'George Graham' AS 'Customer Name'
,CONVERT(MONEY, '5') AS 'Amount'
,CONVERT(DATE,'2014-10-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-02-18', 111) AS 'Posting Date'
,'100455' AS 'Customer ID'
,'Janet Cambridge' AS 'Customer Name'
,CONVERT(MONEY, '25') AS 'Amount'
,NULL AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-02-20', 111) AS 'Posting Date'
,'131224' AS 'Customer ID'
,'Mary Tyler' AS 'Customer Name'
,CONVERT(MONEY, '200') AS 'Amount'
,CONVERT(DATE,'2012-05-20', 111) AS 'End Date'
Looking at my sample table above, if I were to filter the query results on "Bob Parkett", I would hope to see the following result: (NOTE: 1 Row Per Month is Expected)
SELECT
CONVERT(DATE, '2012-02-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-03-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-04-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-05-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-06-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-07-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
UNION ALL
SELECT
CONVERT(DATE, '2012-08-15', 111) AS 'Posting Date'
,'331125' AS 'Customer ID'
,'Bob Parkett' AS 'Customer Name'
,CONVERT(MONEY, '10') AS 'Amount'
,CONVERT(DATE, '2012-08-15', 111) AS 'End Date'
To be honest, I have no idea where to start, or if it is even possible. Would anyone be able to offer any suggestions?
Thank you so much in advance!
March 1, 2012 at 2:33 pm
So, you want to look at the number of months between posting date and end date and basically copy data for each month remaining except that posting date should be incremented? Is this correct? Is this for 1 person at a time (WHERE CustomerID = 123456) or do you want to generate it for many customers at a time?
Also, please see the link in my signature about posting questions to get the best help.
Jared
CE - Microsoft
March 1, 2012 at 8:10 pm
Hi Jared, thank you so much for your quick reply.
So, you want to look at the number of months between posting date and end date and basically copy data for each month remaining except that posting date should be incremented? Is this correct?
You are correct. The "day" portion of the "date" would be the same while the month would increase as long as it falls within the "End Date" range. (Example: "2012-02-01" would have "2012-03-01", "2012-04-01", "2012-05-01", etc.)
Is this for 1 person at a time (WHERE CustomerID = 123456) or do you want to generate it for many customers at a time?
This report would run for many people at a time. "Expected Results" table from my Original Post I was just showing one customer to get a better idea of the expected results. Ideally this result would have a row for every month <= [End Date] and I could isolate one Customer from the table by using a Parameter on the SSRS Report.
Thanks again!
March 2, 2012 at 7:06 am
Also, I took a look at the post in your signature about best practices for asking question on these forums. Perhaps I am missing something though, I tried to provide example data and expected results in a format that wouldn't require any formatting.
Is there something particular that I can do to make it easier for others to respond to my post?
Thanks in advance for the advice!
March 2, 2012 at 7:21 am
krhind (3/2/2012)
Also, I took a look at the post in your signature about best practices for asking question on these forums. Perhaps I am missing something though, I tried to provide example data and expected results in a format that wouldn't require any formatting.Is there something particular that I can do to make it easier for others to respond to my post?
Thanks in advance for the advice!
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmptab]') AND type in (N'U'))
DROP TABLE [dbo].[tmptab]
GO
CREATE TABLE [dbo].[tmptab](
[Posting Date] [date] NULL,
[Customer ID] [varchar](6) NOT NULL,
[Customer Name] [varchar](15) NOT NULL,
[Amount] [money] NULL,
[End Date] [date] NULL
)
INSERT INTO [dbo].[tmptab]([Posting Date], [Customer ID], [Customer Name], [Amount], [End Date])
SELECT '20120201', N'131224', N'John Smith', 25.0000, NULL UNION ALL
SELECT '20120215', N'331125', N'Bob Parkett', 10.0000, '20120815' UNION ALL
SELECT '20120215', N'213234', N'George Graham', 5.0000, '20141015' UNION ALL
SELECT '20120218', N'100455', N'Janet Cambridge', 25.0000, NULL UNION ALL
SELECT '20120220', N'131224', N'Mary Tyler', 200.0000, '20120520'
select * from tmptab
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 2, 2012 at 4:01 pm
You have to loop through the dates, personal preference is a WHILE loop. below is a stored procedure where you can pass the string all or a specific customer id and get the results. Also where you see #tmptab is where your customer table would go.
CREATE PROCEDURE usp_FuturePayments (@CustomerID VARCHAR(20)) AS
SET NOCOUNT ON
DECLARE @tempID Table (ID INT) -- Could also be temp table
DECLARE @SortT TABLE (
CustomerID varchar(6) NOT NULL,
CustomerName varchar(15) NOT NULL,
Amount money NULL,
Date SMALLDATETIME NULL
)
DECLARE @CustID INT
DECLARE @Count INT
DECLARE @EndDate DATETIME
IF @CustomerID = 'ALL' -- can use ALL for every customer or a speciffic customer ID
BEGIN
Insert INTO @tempID SELECT CustomerID from #tmptab -- making a table we can delete from with out touching the original data
END
ELSE
INSERT INTO @tempID SELECT @CustomerID
WHILE (SELECT COUNT(*) from @tempID) > 0 -- while there is something our temp table
BEGIN -- while loop
SET @CustID = (SELECT TOP 1 ID FROM @tempID) -- Get the first customer ID out of our temp table
SET @EndDate = (Select CASE WHEN EndDate IS NULL THEN DATEADD(Year,1,GETDATE()) ELSE EndDate END -- Deal with the Null end dates
FROM #tmptab where CustomerID = @CustID)
SET @Count = DATEDIFF(MONTH,GETDATE(),@EndDate) -- How many times do we have to add to the date
WHILE @Count > 0 -- While there is still counting to be done can set it to
BEGIN -- While Count
INSERT INTO @SortT -- insert into another temp table so we can sort it all nice when its done
SELECT CustomerID, CustomerName, Amount, DATEADD(MONTH,(-1*@Count),@EndDate) -- subtract count from end date
FROM #tmptab
WHERE CustomerID = @CustID
SET @Count = @Count - 1 -- change the count so we can end the loop when we are done
END -- While Count
DELETE FROM @tempID where ID = @CustID -- delete the customer id from our temp table so we process each one
END -- While Loop
Select CustomerID,CustomerName,Amount, CONVERT(DATE,date,111) from @SortT ORDER BY Date, CustomerName -- display our output
GO
The only thing im not sure on is if you could call a SP from SSRS. if not you may need to modify the above a bit.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 2, 2012 at 5:31 pm
capn.hector (3/2/2012)
You have to loop through the dates, personal preference is a WHILE loop. below is a stored procedure where you can pass the string all or a specific customer id and get the results. Also where you see #tmptab is where your customer table would go.
CREATE PROCEDURE usp_FuturePayments (@CustomerID VARCHAR(20)) ASSET NOCOUNT ON
DECLARE @tempID Table (ID INT) -- Could also be temp table
DECLARE @SortT TABLE (
CustomerID varchar(6) NOT NULL,
CustomerName varchar(15) NOT NULL,
Amount money NULL,
Date SMALLDATETIME NULL
)
DECLARE @CustID INT
DECLARE @Count INT
DECLARE @EndDate DATETIME
IF @CustomerID = 'ALL' -- can use ALL for every customer or a speciffic customer ID
BEGIN
Insert INTO @tempID SELECT CustomerID from #tmptab -- making a table we can delete from with out touching the original data
END
ELSE
INSERT INTO @tempID SELECT @CustomerID
WHILE (SELECT COUNT(*) from @tempID) > 0 -- while there is something our temp table
BEGIN -- while loop
SET @CustID = (SELECT TOP 1 ID FROM @tempID) -- Get the first customer ID out of our temp table
SET @EndDate = (Select CASE WHEN EndDate IS NULL THEN DATEADD(Year,1,GETDATE()) ELSE EndDate END -- Deal with the Null end dates
FROM #tmptab where CustomerID = @CustID)
SET @Count = DATEDIFF(MONTH,GETDATE(),@EndDate) -- How many times do we have to add to the date
WHILE @Count > 0 -- While there is still counting to be done can set it to
BEGIN -- While Count
INSERT INTO @SortT -- insert into another temp table so we can sort it all nice when its done
SELECT CustomerID, CustomerName, Amount, DATEADD(MONTH,(-1*@Count),@EndDate) -- subtract count from end date
FROM #tmptab
WHERE CustomerID = @CustID
SET @Count = @Count - 1 -- change the count so we can end the loop when we are done
END -- While Count
DELETE FROM @tempID where ID = @CustID -- delete the customer id from our temp table so we process each one
END -- While Loop
Select CustomerID,CustomerName,Amount, CONVERT(DATE,date,111) from @SortT ORDER BY Date, CustomerName -- display our output
GO
The only thing im not sure on is if you could call a SP from SSRS. if not you may need to modify the above a bit.
First, you do not need a while loop for this. Try to avoid those whenever you can, and I believe in this case you most certainly can. I will explain below. Second, J showed above what we really need. Table structures and the code to populate it with the sample data. The way you wanted the results I think was a great way to post it, because most people try to display sets of data in these forums with text and it is really hard to understand, so thanks for that.
Avoid a while loop by developing a TVF (table valued function). This way you can treat each row as an input to the function that will output a table of values according to the function. Are you familiar with TVFs?
Jared
CE - Microsoft
March 2, 2012 at 5:37 pm
i am not familliar with TVF's off to the all mighty google to eliminate my while loop
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 4, 2012 at 9:09 am
SQL KnowItAll thanks for pointing out the TVF's made this alot more efficient. krhind, Here is the function followed by the query that will produce your requested output. to limit by a speciffic customer id just add a where clause to the query.
CREATE FUNCTION udf_DateInc (@CustID INT,
@CustName VARCHAR(64),
@Payment MONEY,
@EndDate Date)
RETURNS @SortTable TABLE
(
CustomerID varchar(6) NOT NULL,
CustomerName varchar(15) NOT NULL,
Amount money NULL,
PayDate date NULL
)
AS
BEGIN
DECLARE @Count INT
IF @EndDate IS NULL
BEGIN
SET @EndDate = DATEADD(YEAR,1,GETDATE())
END
SET @Count = DATEDIFF(Month, GetDate(), @EndDate)
WHILE (@Count > 0)
BEGIN
INSERT INTO @SortTable
SELECT @CustID, @CustName, @Payment, DATEADD(MONTH,(-1*@Count),@EndDate)
SET @Count = @Count - 1
END
RETURN
END
GO
SELECT p.CustomerID,p.CustomerName,p.Amount,d.paydate
FROM DateProblem p
CROSS APPLY udf_DateInc(p.CustomerID, P.CustomerName,p.Amount,p.EndDate) d
ORDER BY d.PayDate
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 13, 2012 at 9:14 am
Hey Hector,
I apologize for the late reply. Just wanted to say thanks for the code you wrote up for me. I had to wait to get the necessary permissions to create functions in our database. This looks like it will work great.
Really appreciate your help as well Jared.
Kyle
March 13, 2012 at 9:23 am
capn.hector (3/4/2012)
SQL KnowItAll thanks for pointing out the TVF's made this alot more efficient. krhind, Here is the function followed by the query that will produce your requested output. to limit by a speciffic customer id just add a where clause to the query.
CREATE FUNCTION udf_DateInc (@CustID INT,@CustName VARCHAR(64),
@Payment MONEY,
@EndDate Date)
RETURNS @SortTable TABLE
(
CustomerID varchar(6) NOT NULL,
CustomerName varchar(15) NOT NULL,
Amount money NULL,
PayDate date NULL
)
AS
BEGIN
DECLARE @Count INT
IF @EndDate IS NULL
BEGIN
SET @EndDate = DATEADD(YEAR,1,GETDATE())
END
SET @Count = DATEDIFF(Month, GetDate(), @EndDate)
WHILE (@Count > 0)
BEGIN
INSERT INTO @SortTable
SELECT @CustID, @CustName, @Payment, DATEADD(MONTH,(-1*@Count),@EndDate)
SET @Count = @Count - 1
END
RETURN
END
GO
SELECT p.CustomerID,p.CustomerName,p.Amount,d.paydate
FROM DateProblem p
CROSS APPLY udf_DateInc(p.CustomerID, P.CustomerName,p.Amount,p.EndDate) d
ORDER BY d.PayDate
You can change your TVF from a multi-statment TVF to an Inline TVF and improve the performance. You don't need a loop or any of the setup code for the loop.
March 13, 2012 at 9:53 am
Here is a rewrite of the TVF as an inline TVF without a loop:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_DateInc]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_DateInc]
GO
CREATE FUNCTION dbo.udf_DateInc (@CustID INT,
@CustName VARCHAR(64),
@Payment MONEY,
@EndDate DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (DATEDIFF(MONTH, GETDATE(), ISNULL(@ENDDATE, DATEADD(YEAR, 1, GETDATE()))))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT
@CustID CustomerID,
@CustName CustomerName,
@Payment Amount,
DATEADD(MONTH,(-1*(DATEDIFF(MONTH, GETDATE(), ISNULL(@ENDDATE, DATEADD(YEAR, 1, GETDATE()))) - N)),ISNULL(@ENDDATE, DATEADD(YEAR, 1, GETDATE()))) PayDate
FROM cteTally
;
GO
March 13, 2012 at 11:57 am
Lynn Pettis (3/13/2012)
Here is a rewrite of the TVF as an inline TVF without a loop:
looking at your code the only thing i dont understand (after looking at it and smacking my self for not knowing about row_number()) is the order by (Select NULL).
does this just say to add a row number in whatever order they come back since in this case its just returning 1's?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 13, 2012 at 12:01 pm
You could probably change null to 1 and get the same effect. It is generating an increasing value (row_number()) for however many rows are being generated for the dynamic tally table.
March 13, 2012 at 12:10 pm
after playing with it in SSMS and figuring out what most of the code did that was the only part i was wondering about. i joined up here to further me on my way to a self taught DBA. im currently in a job where i can slowly wade into the ocean as i learn more. i know enough that i can get functionally correct code but realize i have allot to learn to get efficient code.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply