February 11, 2019 at 9:44 pm
Hi all,
I have a table of records. The table contains a date field and so each record is dated.
So for example, I have 100 records with the date 2019/01/01. I have further set of 100 records with the date incremented by 1 (e.g 2019/01/02) (yyyy/mm/dd)
I have a query that reads this table and identifies the latest set of dated records. So will provide only those 100 records that are 02/01/2019.
Each day I need to copy those latest set of records back into the same table with the date incremented. e.g 2019/01/03)
My SQL server team have advised they can run my query every day at 1am, but I need to provide the stored procedure. Except I don't know how to go about that.
How can I Write a procedure that increments the date by one for every selected record?
Cheers
February 11, 2019 at 10:02 pm
Something like this?
DECLARE @SearchDate DATE = GETDATE();
INSERT INTO MyTable (col1, col2, col3)
SELECT col2, col2, DATEADD(day,1,@SearchDate)
FROM MyTable
WHERE col3 = @SearchDate;
Here's some actually tested code... (before I say something stupid and untested)...CREATE TABLE dummy (id int identity, RecDate date);
GO
INSERT INTO dummy(recdate) VALUES ('1/1/2019'),('1/2/2019'),('1/2/2019');
DECLARE @ThisDate DATE;
SET @ThisDate = DATEFROMPARTS(YEAR(GETDATE()),1,2);
UPDATE dbo.dummy
SET
--id - this column value is auto-generated
dbo.dummy.RecDate = DATEADD(day,1,RecDate)
WHERE dbo.dummy.RecDate = @ThisDate;
SELECT *
FROM dbo.dummy;
The part to note is the variable @ThisDate … you can set it to anything you want and then run your update. I was just making up a very simple example.
February 11, 2019 at 10:14 pm
Thanks PietLinden. This seems very simple and easy to implement.
I will play around with it and see how I go.
thanks
February 12, 2019 at 6:27 am
This seems like it could get out of hand storage-wise very quickly. Why do you need a fresh set of ALL rows every day? If we knew this, we might be able to suggest a better way.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2019 at 2:31 pm
it's a little complex but this is a set of records that maintain customer balances with us. Each day the record remain the same until such a day as the balance changes. It is a company requirement that a running balance is maintained for audit, regardless of server space - which the company is perfectly happy to pay for. In order for me to be as efficient as possible the table was made with only a few fields and so 365 records a year is basically meaningless. We also limit the number of customer for who this requirement applies.
However, if there is a better solution that displays a running balance and modifies the query such that it shows the date of change I'd be very happy to see an example.
February 12, 2019 at 2:45 pm
barry.nielson - Tuesday, February 12, 2019 2:31 PMit's a little complex but this is a set of records that maintain customer balances with us. Each day the record remain the same until such a day as the balance changes. It is a company requirement that a running balance is maintained for audit, regardless of server space - which the company is perfectly happy to pay for. In order for me to be as efficient as possible the table was made with only a few fields and so 365 records a year is basically meaningless. We also limit the number of customer for who this requirement applies.However, if there is a better solution that displays a running balance and modifies the query such that it shows the date of change I'd be very happy to see an example.
To me, a running balance doesn't mean a new balance every day if there were no transactions. To me, a running balance is quite like you might find in a checkbook... there's a new running balance noted ONLY when there is a change to what that balance is. Such changes are what need to be audited and, from that, the running balance at any point in time can easily be selected from the table without having (for example) a hundred rows of (except for the date) of data that are identical and the balance has not changed.
With only a little effort, a "TYPE 6 Slowly Changing Dimension" table and a trigger to populate it would give you easy point-in-time capabilities and offer less chance of tampering.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2019 at 2:56 pm
Ok great. Thanks for the updated description.The logic sounds wonderful and sure I'd be more than willing to push for a better solution than the one we use.
Show me an example of how I can see what the balance was on any given day including changes to the balance on a given day.
e.g
$1.00 | Day 1
$1.00 | Day 2
$2.00 | Day 3
$2.00 | Day 4
$2.00 | Day 5
$3.00 | Day 6
...Etc
This is how it must be displayed.
February 12, 2019 at 3:10 pm
It's not quite in the format you asked for, but it can easily be tweaked.
CREATE TABLE #Customer_Balances
(
Customer_ID INT
, Cur_Balance MONEY
, As_of_Date DATE
)
;
INSERT #Customer_Balances(Customer_ID, Cur_Balance, As_of_Date)
VALUES
(285632, 234, '2018-12-15')
, (285632, 357, '2019-01-11')
, (285632, 480, '2019-02-07')
;
WITH Base AS ( SELECT n FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) c(n) )
, TALLY AS ( SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 AS n FROM Base A CROSS JOIN Base B CROSS JOIN Base C )
, Customer_Balance_Ranges AS
(
SELECT cb.Customer_ID, cb.Cur_Balance, cb.As_of_Date AS From_Date, LEAD(cb.As_of_Date, 1, CAST(GETDATE() + 1 AS DATE)) OVER(PARTITION BY cb.Customer_ID ORDER BY cb.As_of_Date) AS To_Date
FROM #Customer_Balances AS cb
)
SELECT *
FROM Customer_Balance_Ranges cbr
CROSS APPLY
(
SELECT TOP(DATEDIFF(DAY, cbr.From_Date, cbr.To_Date)) DATEADD(DAY, t.n, cbr.From_Date) AS Balance_Date
FROM TALLY t
ORDER BY t.n
) b
DROP TABLE #Customer_Balances
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2019 at 3:17 pm
Hi Drew,
Gee, thanks so much for the effort here. Much appreciated.
I am using SQL 2008 server. not sure if that means very much but i am have difficulty with thisLEAD(cb.As_of_Date, 1, CAST(GETDATE() + 1 AS DATE)) OVER(PARTITION BY cb.Customer_ID
LEAD is not recognised as a built in function name.
How would I overcome that?
P.S. After reading some details about LEAD I see that it's useful in 2012 but was not available in 2008. Not to mention the complexity of emulating it for 2008 - it's way outside my ability to process in my little learner brain.
Thanks again
February 12, 2019 at 8:52 pm
pietlinden - Monday, February 11, 2019 10:02 PMThe part to note is the variable @ThisDate … you can set it to anything you want and then run your update. I was just making up a very simple example.
PietLinden
Thanks for your code. I modified it after I came to understand it and it's beautiful.
So I need to expand on this just a littleDrop Table dummy
CREATE TABLE dummy (id int identity, CID Int, RecDate date);
GO
INSERT INTO dummy(CID, recdate) VALUES (5, '1/1/2019'),(6, '2/2/2019'),(7, '2/2/2019');
DECLARE @ThisDate DATE;
--Set the date to the date value to be searched for and modified
SET @ThisDate = '2019-02-02'
UPDATE dbo.dummy
SET
--ID Column is auto generated.
-- Dateadd (by Day, date difference to be added, column to be added to)
dbo.dummy.RecDate = DATEADD(day,DATEDIFF(day,@ThisDate,getdate()),RecDate)
WHERE dbo.dummy.RecDate = @ThisDate;
SELECT *
FROM dbo.dummy;
The result is
1 | 5 | 2019-01-01
2 | 6 | 2019-02-13
3 | 7 | 2019-02-13
I need this:
2 | 6 | 2019-02-02
3 | 6 | 2019-02-03
4 | 6 | 2019-02-04
...
13 | 6 | 2019-02-13
Which is to say that records must be filled in for every missing day between the last and the current date. This is to fill in days
Is that possible form a single script I can do this in VBA, but am transposing this function into SQL server.
February 13, 2019 at 12:01 pm
barry.nielson - Tuesday, February 12, 2019 3:17 PMHi Drew,Gee, thanks so much for the effort here. Much appreciated.
I am using SQL 2008 server. not sure if that means very much but i am have difficulty with this
LEAD(cb.As_of_Date, 1, CAST(GETDATE() + 1 AS DATE)) OVER(PARTITION BY cb.Customer_ID
LEAD is not recognised as a built in function name.How would I overcome that?
P.S. After reading some details about LEAD I see that it's useful in 2012 but was not available in 2008. Not to mention the complexity of emulating it for 2008 - it's way outside my ability to process in my little learner brain.
Thanks again
Sorry about that. LEAD/LAG were indeed introduced in SQL 2012. I didn't notice that you had posted in a SQL 2008 forum.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 13, 2019 at 2:49 pm
Once you get the code: http://www.sqlservercentral.com/articles/Stored+Procedures/183073/
February 13, 2019 at 3:06 pm
Thank you. Yes, I know the benefit of SP's and this will be helpful.
This issues will be fa more complicated than just this little snippet and many problems need to be overcome, but one step at a time.
Cheers
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply