October 13, 2014 at 11:24 am
Hi,
I have a date column where the data is loaded every wednesday night,like next 2 weeks data.
For eg: today is 13 monday,the week starts from sunday to saturday.
only current week records should be editable..not next week records.
In my database now the data column has the data until 10/26.so till 10/18 the records should be editable
from 10/19 to 10/26 should be in read mode.
The idea is that next week data 10/19 to 10/26 will be loaded again on wednesday in order to avoid overwritten the records in database.we are able to edit only current week records.
Based on date difference how can i write the logic?
October 13, 2014 at 12:54 pm
Well, you haven't told us a few things:
1. Where you want this enforced, the application, the database, or both?
2. How you are controlling updates? Is it SQL passed in by the application or a stored procedure?
For defense-in-depth purposes I'd consider putting an INSTEAD OF UPDATE trigger on the table(s) in question. With logic something like this:
DECLARE @baseDate DATE = '1900-01-01',
@startOfThisWeek DATETIME,
@startOfNextWeek DATETIME;
SELECT
@startOfThisWeek = DATEADD(DAY, -(DATEPART(WEEKDAY, GETDATE())%7) + 1, DATEADD(WEEK, DATEDIFF(WEEK, @baseDate, GETDATE()), @baseDate)),
@startOfNextWeek = DATEADD(DAY, -(DATEPART(WEEKDAY, GETDATE())%7) + 1, DATEADD(WEEK, DATEDIFF(WEEK, @baseDate, GETDATE()) + 1, @baseDate))
UPDATE baseTable
SET columns = I.columns
FROM
inserted AS I
WHERE
baseTable.PrimaryKey = I.PrimaryKey AND
baseTable.dateColumn >= @startOfThisWeek AND
baseTable.dateColumn < @startOfNextWeek
I'd also consider adding similar logic to my application to mark rows as read only and if I'm using a stored procedure I'd add the logic to that so only rows that will pass the trigger will be updated anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2014 at 1:07 pm
Hi Champion, Thank you.
You are right,this needs to be done in application. we are using jqgrid,the data which is populated in the grid has
id description date
34 'present' 2014/10/13
The description column has edit options like dropdown ,now based on dates as per i mentioned next week data rows should be read only...
and the current week should be editable....
how can i approach this?
October 13, 2014 at 1:25 pm
mcfarlandparkway (10/13/2014)
Hi Champion, Thank you.You are right,this needs to be done in application. we are using jqgrid,the data which is populated in the grid has
id description date
34 'present' 2014/10/13
The description column has edit options like dropdown ,now based on dates as per i mentioned next week data rows should be read only...
and the current week should be editable....
how can i approach this?
Since this is a SQL Server forum and not a jQuery or .NET forum this probably isn't the best place to ask this question and expect to get a working answer.
I'd probably add an isEditable bit column to the query, using logic similar to that in the code example I provided, that returns the data to the application and use that column to control the appearance and editability of the row in the application. Otherwise you need to do the date manipulation, that I did in T-SQL, in the application code. Which is find the start date for this week and the start date for next week and only allow rows where the date is >= the start date for this week and < the start date for next week.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply