October 30, 2014 at 11:06 am
SELECT c.cust_fullnameAS Name,
c.cust_membership_id AS Account,
t.c_amount AS Amount,
t.i_ticket_id AS Doc_Num,
t.s_credit_tran_type AS Detail_Account,
CONVERT(varchar(10),t.dt_when, 110) AS SaleDate,
'FOOD' As ItemSold
FROM Transactions AS t
INNER JOIN Customers AS c
ON t.s_ref_num = c.cust_id
WHERE s_credit_tran_type = 'House Account'
AND b_cancel = 0
AND t.[dt_when] > = dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101') -- Date greater than or equal to Monday of last week
AND t.[dt_when] < = dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7),'17530101') -- Date before Monday of this week
GROUP BY c.cust_fullname,
c.cust_membership_id,
t.c_amount,
t.i_ticket_id,
t.s_credit_tran_type,
t.dt_when
ORDER BY t.dt_when ASC
I have a query here that will give me the previous weeks House Account sales no matter what day of the (following) week you run it.
How can I have this same code simply give me the data from the last time it was ran? (removing the DATEADD lines)
Use Case: User runs House Account query, but then doesn't run it for 3 weeks...I need code so SQL will know the last time it was ran and give the data that was previously missed. (so the data will be the 3 weeks missed, not just the 'previous week' only)
Thanks...
Chris
October 30, 2014 at 11:52 am
You're pretty much looking at some kind of control table for that purpose, unless you have some way of controlling it in the application. Triggers won't work for this due to not responding to SELECT actions.
You COULD do something weird with the index scan/seek statistics I suppose, but that's still going to require some work on your part to determine the query had been run (and assumes nothing else uses that index, which seems unlikely.)
October 30, 2014 at 12:44 pm
Thanks. I am attempting to do this without having to code anything C#.
October 30, 2014 at 2:55 pm
Ever thought of putting the query inside a stored Proc, then you would just have an Update to some control table within the proc ie:
Create Proc dbo.P_query as
Set Nocount ON
Declare @DateLastRun datetime
Select @DatelastRun = datelastrun from ControlTable
Select ....... where -- and insert your @DateLastRun in here.
Update ControlTable set datelastrun = getdate()
go
Obviously there are a couple of ways to do this and it'll depend on how you want to do your query, you could skip declaring the variable @DateLastRun and do it as a subquery. You may have a number of values in your ControlTable, so you'll need a where clause to get the right one.
All achievable in T-SQL no C# required. I constantly need to do the same thing as you and I use the Control Table method.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply