March 9, 2015 at 8:01 am
Hi there,
I am not sure if a trigger is the solution but I would like to try and illustrate my issue.
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL someone can share as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
Thoughts?
March 9, 2015 at 8:07 am
Not quite sure what you are trying to do here. Are you saying when somebody updates a given row you want to update a certain column from that row using the value of another row? To offer much detailed help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
March 9, 2015 at 8:19 am
I appreciate the quick response. Apologies, I am new to the forum .. I will try to clarify.
CREATE TABLE #mytable(
[Campus] [nvarchar](50) NOT NULL,
[Building] [nvarchar](50) NOT NULL,
[Week] [date] NOT NULL,
[Total Suites] [int] NULL,
[Rentable Suites] [int] NULL,
[Occupied Suites] [int] NULL
)
GO
insert into #mytable ([Campus],
[Building],
[Week],
[Total Suites],
[Rentable Suites],
[Occupied Suites] )
select 'Campus A','Building A', '4/5/2015','50','55','45' UNION ALL
select 'Campus A','Building A', '4/12/2015','50','55',''
select * from #mytable
select
building,
week,
[occupied suites],
lag([Occupied Suites]) over (order by week) as lag
from #mytable
If you run the above, you will see the lag() helps shift the occupied suites to the following week. I am trying to do this in such a way that when the data changes for the week of 4/5 , it just carries the occupied suites to the following week.
March 9, 2015 at 8:52 am
Is there a reason not to calculate these values when you need them? It is best to avoid storing calculated values in the database. It is difficult to always be absolutely certain your data is correct. If you calculate the value when needed it will always be 100% accurate.
_______________________________________________________________
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/
March 9, 2015 at 9:01 am
I am buiding a lightswitch application and it would be easiest i think to have it done right in the table if possible - is it even possible?
March 9, 2015 at 9:09 am
steve000 (3/9/2015)
I am buiding a lightswitch application and it would be easiest i think to have it done right in the table if possible - is it even possible?
It is absolutely possible. Not sure that it would be better in the database because it is a lightswitch application. In my opinion that sort of thing belongs in the procedure to retrieve data instead of being stored in the table. If you really want this in the database the code you posted is 99% of what you would need. You just need to reference the inserted virtual table inside of an insert and update trigger.
Take a shot at it and I will be happy to help fine tune it.
_______________________________________________________________
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/
March 9, 2015 at 9:16 am
Thanks Sean,
That is where I am stumped - i have never created a trigger before. Might you be able to share a sample with the proper syntax? I wills still explore how to do it in LS, but I would like to try this first. It's the last piece i need to launch my app 🙁
It only will ever have 52 weeks of data in it, so the performance lacking from the trigger is ok (at least i think).
March 9, 2015 at 3:35 pm
steve000 (3/9/2015)
Thanks Sean,That is where I am stumped - i have never created a trigger before. Might you be able to share a sample with the proper syntax? I wills still explore how to do it in LS, but I would like to try this first. It's the last piece i need to launch my app 🙁
It only will ever have 52 weeks of data in it, so the performance lacking from the trigger is ok (at least i think).
Sorry been a bit hectic today. I would think you want to create two instead of triggers. One for UPDATE and another for INSERT. Take a look here and see what you can come up with. https://msdn.microsoft.com/en-us/library/ms189799.aspx
_______________________________________________________________
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/
March 9, 2015 at 6:37 pm
Looks like a data smear to me:
Filling In Missing Values Using the T-SQL Window Frame[/url]
As Sean has advised, there's no reason to store that information when there's various ways to calculate it on the fly. You can use a TRIGGER to do it and if after reading that article you decide you absolutely must do so, then post back and one of us can probably oblige. Keep in mind though that the TRIGGER will be more suited to filling in prior rows than future rows.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2015 at 6:57 am
After reading some on the syntax and use, I came up with the following.
CREATE TRIGGER dbo.trig_OccupiedSuites
ON [dbo].[MyTable]
AFTER UPDATE
AS
BEGIN
-- Your SQL goes here
set [Occupied Suites] = (select lag([Occupied Suites]) over (order by week) from [MyTable]) -- too many records, need a way to isolate to current row
END
It doesn't allow me to update any records once applied (saying too many rows to update). I then tried to created a view using the LAG() already in it , to then join everything together on the trigger , so it can pull one from the other but it isn't working. Any nudges on what I am doing wrong here?
March 10, 2015 at 7:02 am
Where to start....
Well, firstly, that trigger won't even create, so it's going to be pretty hard to tell you what's wrong with the trigger that you're running, as it's clearly not the one you posted.
Msg 102, Level 15, State 1, Procedure trig_OccupiedSuites, Line 8
Incorrect syntax near 'Occupied Suites'.
You identify the rows that have been affected by the update by joining to the inserted and/or deleted tables. They contain all the rows affected by the update (*all* the rows, not one row), inserted contains the new values, deleted contains the old values.
Lastly, what do you mean by "saying too many rows to update"? What is saying that? If you're using the "Edit top 100 rows" 'feature' of management studio, please stop doing so as it's likely to cause you more problems. Use UPDATE statements from the query window.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2015 at 7:29 am
steve000 (3/10/2015)
After reading some on the syntax and use, I came up with the following.
CREATE TRIGGER dbo.trig_OccupiedSuites
ON [dbo].[MyTable]
AFTER UPDATE
AS
BEGIN
-- Your SQL goes here
set [Occupied Suites] = (select lag([Occupied Suites]) over (order by week) from [MyTable]) -- too many records, need a way to isolate to current row
END
You need to be using the inserted (to get the new values) or deleted (to get previous values) virtual tables in your trigger. The part where you have the comment about your sql goes here needs to be a complete statement (hint...an update statement). Since you are wanting to change the values being updated it needs to be an INSTEAD of trigger. This also means you need to update every column using inserted values (remember your trigger is INSTEAD of the usual update).
It doesn't allow me to update any records once applied (saying too many rows to update). I then tried to created a view using the LAG() already in it , to then join everything together on the trigger , so it can pull one from the other but it isn't working. Any nudges on what I am doing wrong here?
_______________________________________________________________
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/
March 10, 2015 at 8:00 am
I've made a bit more progress ...
USE [ERDB]
GO
/****** Object: Trigger [dbo].[trig_OccupiedSuites] Script Date: 3/10/2015 10:38:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trig_OccupiedSuites] ON [dbo].[Census Tracker] AFTER INSERT, UPDATE AS
IF UPDATE([Occupied Suites]) BEGIN
SET NOCOUNT ON;
WITH Occupied AS (
SELECT [Census Tracker].campus as campus, [Census Tracker].building as building, [Census Tracker].week as week,
lag([Census Tracker].[Occupied Suites]) over (order by [Census Tracker].week) as [Occupied Suites] from [Census Tracker]
JOIN inserted ON [Census Tracker].Campus = inserted.Campus and [Census Tracker].Building = inserted.Building and [Census Tracker].Week = inserted.Week
--GROUP BY Transactions.InvoiceId
)
UPDATE [Census Tracker]
SET [Occupied Suites] = Occupied.[Occupied Suites]
FROM Occupied
WHERE [Census Tracker].campus = Occupied.campus and [Census Tracker].Building = Occupied.building and [Census Tracker].Week = occupied.week
END
When I update the [occupied suites] column now though with any number, it just updates to a NULL rather than the LAG() value , which is the previous row value. I feel like I am really close , thoughts? It also fires pretty quickly, the table will only ever have 1200 rows.
March 10, 2015 at 8:05 am
Could you post a few more rows of sample data please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2015 at 8:14 am
CREATE TABLE [dbo].[Census Tracker](
[Campus] [nvarchar](50) NOT NULL,
[Building] [nvarchar](50) NOT NULL,
[Week] [date] NOT NULL,
[Total Suites] [int] NULL,
[Rentable Suites] [int] NULL,
[Occupied Suites] [int] NULL,
[Resident Total] [int] NULL,
[Notes] [nvarchar](500) NULL
)
GO
INSERT INTO [Census Tracker]
VALUES
('Campus A','Building A','4/5/2105','50','45','43','43','test note1'),
('Campus A','Building A','4/12/2105','50','45','42','42','test note2'),
('Campus A','Building A','4/19/2105','50','45','45','45','test note3'),
('Campus A','Building A','4/26/2105','50','45','38','38','test note4')
My end goal is to move occupied suites from the previous week, to the current week. So 43 for 4/5/2015, should shift to 4/12/2015 when the 4/12 row is edited.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply