April 2, 2013 at 10:09 am
I have created a stored procedure that will return approved purchase orders when executed. However, if I have it kickoff several times a day I do not want to return purchase order records that were returned on the previous executions. Any ideas on the best way to set this up? I thought about putting the data over into a temp table to do the comparisons or maybe have it look at time stamp info. This will actually be kicked off from a SharePoint workflow. Any help or suggestions will be greatly appreciated.
April 2, 2013 at 10:26 am
Maybe just add a DateSent column to your table? Then you can update that when you send the notification (good for historical purposes) and then your main logic only gets those that have DateSent is null. There are number of ways to do this type of thing. A lot of the decision comes down to what you need the process to do etc etc etc...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 10:43 am
Thanks for the reply. I was hoping to accomplish this using some dynamic code, rather than maintain a static table, but that may be my best option. Thanks again.
April 2, 2013 at 12:39 pm
bpowers (4/2/2013)
Thanks for the reply. I was hoping to accomplish this using some dynamic code, rather than maintain a static table, but that may be my best option. Thanks again.
Yeah if you want to know if something was sent in the past you have to store it somewhere. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2013 at 9:11 am
I built a staging table and I am writing the last execution time of the sp to that table. How do I now use the execution time in the WHERE caluse of my sp, when the staging table has no logical join to the tables in my sp? Any help will be appreciated.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN dbo.STOREDPROC.EXECUTED_TIME AND GETDATE()
April 3, 2013 at 9:45 am
bpowers (4/3/2013)
I built a staging table and I am writing the last execution time of the sp to that table. How do I now use the execution time in the WHERE caluse of my sp, when the staging table has no logical join to the tables in my sp? Any help will be appreciated.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN dbo.STOREDPROC.EXECUTED_TIME AND GETDATE()
Would need to know the structure of the table and the data stored, but I would see a select on the table returning a single value to be used in the WHERE clause.
April 3, 2013 at 9:46 am
bpowers (4/3/2013)
I built a staging table and I am writing the last execution time of the sp to that table. How do I now use the execution time in the WHERE caluse of my sp, when the staging table has no logical join to the tables in my sp? Any help will be appreciated.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN dbo.STOREDPROC.EXECUTED_TIME AND GETDATE()
This is why in my original post I suggested adding a DateSent column to your PO table. You don't really need another table for this, you just need to know what rows have already had a notification sent.
WHERE PURCHASE_ORDER.DateNotificationSent IS NULL
The other way would be to use a subquery.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN (Select MAX(EXECUTED_TIME) from HistoryTable) AND GETDATE()
The downside of this is that you don't account for execution errors and some have the ability of getting missed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply