March 16, 2015 at 10:23 am
Enjoy_SQL (3/16/2015)
Below is the source table, the view, and the trigger on the source table.What I am finding out now (and someone mentioned before with caution :)) is that any retroactive updates, do not update the future weeks data. Is there any way around this? It would be silly to think no one would ever update historical weeks ....
table
USE [ERDB]
GO
/****** Object: Table [dbo].[Census Tracker] Script Date: 3/16/2015 9:19:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Census Tracker](
[Id] [int] IDENTITY(1,1) NOT NULL,
[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,
[NetChange] AS ((((((((((((isnull([Admissions Regular],(0))+isnull([Admissions Short-Term],(0)))+isnull([Admissions Guests],(0)))+isnull([Internal Transfers In],(0)))-isnull([Discharged Deceased],(0)))-isnull([Discharged LTC],(0)))-isnull([Discharged Hospital],(0)))-isnull([Discharged Family/Community],(0)))-isnull([Discharged Winter Stay/STS],(0)))-isnull([Discharged Respite],(0)))-isnull([Discharged Guest],(0)))-isnull([Discharged Transfer],(0)))-isnull([Internal Transfers Out],(0))) PERSISTED,
[Admissions Regular] [int] NULL,
[Admissions Short-Term] [int] NULL,
[Admissions Guests] [int] NULL,
[Internal Transfers In] [int] NULL,
[Discharged Deceased] [int] NULL,
[Discharged LTC] [int] NULL,
[Discharged Hospital] [int] NULL,
[Discharged Competitor] [int] NULL,
[Discharged Family/Community] [int] NULL,
[Discharged Winter Stay/STS] [int] NULL,
[Discharged Respite] [int] NULL,
[Discharged Guest] [int] NULL,
[Discharged Transfer] [int] NULL,
[Internal Transfers Out] [int] NULL,
[Deposits New this Period] [int] NULL,
[Desposits Wait Listed] [int] NULL,
[Deposits for Suits] [int] NULL,
[Deposits Move-In Time < 30 Days] [int] NULL,
[[Deposits Move-In Time < 60 Days] [int] NULL,
[Waitlisted for Special Care (NB)] [int] NULL,
[Pending Internal Transfers In] [int] NULL,
[[Pending Internal Transfers Out] [int] NULL,
[Waitlist for LTC] [int] NULL,
[Notices (Move-Out Time <30 Days] [int] NULL,
[Resident Total] [int] NULL,
[Notes] [nvarchar](500) NULL,
CONSTRAINT [PK_Census Tracker] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
view
CREATE VIEW [dbo].[vw_CensusTracker] WITH SCHEMABINDING
AS
SELECT Id, Campus, Building, Week, [Total Suites], [Rentable Suites], [Occupied Suites], CASE WHEN lag([Occupied Suites]) OVER (ORDER BY campus, building, week) IS NULL THEN [Occupied Suites] ELSE lag([Occupied Suites])
OVER (ORDER BY campus, building, week) END AS OccupiedCalc,
NetChange, [Admissions Regular], [Admissions Short-Term], [Admissions Guests], [Internal Transfers In], [Discharged Deceased],
[Discharged LTC], [Discharged Hospital], [Discharged Competitor], [Discharged Family/Community], [Discharged Winter Stay/STS], [Discharged Respite], [Discharged Guest], [Discharged Transfer], [Internal Transfers Out],
[Deposits New this Period], [Desposits Wait Listed], [Deposits for Suits], [Deposits Move-In Time < 30 Days], [[Deposits Move-In Time < 60 Days], [Waitlisted for Special Care (NB)], [Pending Internal Transfers In],
[[Pending Internal Transfers Out], [Waitlist for LTC], [Notices (Move-Out Time <30 Days], [Resident Total], Notes
FROM dbo.[Census Tracker]
GO
trigger on table
CREATE TRIGGER [dbo].[trig_OccupiedSuites] ON [dbo].[Census Tracker] AFTER UPDATE AS
IF ( UPDATE([Admissions Regular]) or UPDATE([Admissions Short-Term]) or UPDATE([Admissions Guests]) or UPDATE([Internal Transfers In]) or UPDATE([Discharged Deceased]) or UPDATE([Discharged LTC]) or UPDATE([Discharged Hospital]) or UPDATE([Discharged Competitor]) or UPDATE([Discharged Family/Community]) or UPDATE([Discharged Winter Stay/STS]) or UPDATE([Discharged Respite]) or UPDATE([Discharged Guest]) or UPDATE([Discharged Transfer]) or UPDATE([Internal Transfers Out]) or UPDATE([Admissions Short-Term]) )BEGIN
SET NOCOUNT ON;
WITH Occupied AS (
SELECT vw_CensusTracker.campus as campus, vw_CensusTracker.building as building, vw_CensusTracker.week as week,
vw_CensusTracker.[OccupiedCalc] from vw_CensusTracker
JOIN inserted ON vw_CensusTracker.Campus = inserted.Campus and vw_CensusTracker.Building = inserted.Building and vw_CensusTracker.Week = inserted.Week
--GROUP BY Transactions.InvoiceId
)
UPDATE [Census Tracker]
SET [Occupied Suites] = Occupied.OccupiedCalc + [Census Tracker].NetChange
FROM Occupied
WHERE [Census Tracker].campus = Occupied.campus and [Census Tracker].Building = Occupied.building and [Census Tracker].Week = occupied.week
END
GO
It looks like you are doing a join on the week numbers. Did you mean to join on
vw_CensusTracker.Week = dateadd(wk, -1, inserted.Week ) instead? Or something to that effect?
----------------------------------------------------
March 16, 2015 at 11:04 am
lag([Occupied Suites]) OVER (ORDER BY campus, building, week)
This is the part I ended up using ... the trigger then takes the value and adds it into the current week. I used lag instead with a case statement to prevent the final week being blank or NULL.
March 16, 2015 at 4:50 pm
CELKO (3/16/2015)
Do you know of any SQL engine that has a function that will convert that ISO format directly to a "week start date"?
DB2, Ingres, Postgres and Oracle.
As for "proprietary square brackets", if they were proprietary, why would a general SQL tutorial site like w3schools teach their use?
Because they are teaching Microsoft dialect. Read your copy of the ANSI/ISO Standards. Single quotes are used for string and only for strings; double quotes are used for data element names and only for data element names.
Read:
5.2 <token> and <separator>
<delimited identifier> ::=
<double quote> <delimited identifier body> <double quote>
The stuff with Unicode is a little harder to follow. 😉
Thanks for the information, Joe. I'll take a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2015 at 11:34 am
If a trigger fires, it works assuming people enter the data each week in order ... if they go back and edit a previous week, it breaks the integrity for future weeks. Any ideas to work around this?
Is there such a thing a cascading or dynamic trigger? Something that fires a few rows back and checks each future row rather than the affected row in isolation.
March 21, 2015 at 2:10 pm
Enjoy_SQL (3/20/2015)
If a trigger fires, it works assuming people enter the data each week in order ... if they go back and edit a previous week, it breaks the integrity for future weeks. Any ideas to work around this?Is there such a thing a cascading or dynamic trigger? Something that fires a few rows back and checks each future row rather than the affected row in isolation.
This is why the suggestion to handle this information display at runtime by using a view.
----------------------------------------------------
March 23, 2015 at 8:56 am
I added a column that is set aside for a random number, the random number is generated in the trigger to all rows, which then triggers my calculation fire on each row. This is what i added to the existing trigger.
--added to reapply a change to all records
DECLARE @i As int
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @Random
SET @i = (@Random)
UPDATE [Census Tracker]
SET @i= Reindex = @i+1
I believe this worked ... still sifting through the data. I could not allow entry directly into the view as lightswitch reads the table as 'read-only' without a primary key ... I cannot add a primary key with a view that has an aggregrate. It views a LAG() function as an aggregate, hence my need to have to do this.
Lightswitch reads a table as it is, I cannot do fancy SQL to account for what I am trying to do ... it has to be done within the table itself.
Viewing 6 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply