March 11, 2015 at 11:38 am
Any thoughts? I feel like I am really close... 🙁
March 11, 2015 at 5:12 pm
Will this work for you? It does not rely on a lag() which are not available before SQL SERVER 2012.
SELECT
T2.[WEEK] , T2.CAMPUS, T2.BUILDING, T1.[OCCUPIED SUITES] AS OCCUPIED_SUITES_WEEK_START, T2.[OCCUPIED SUITES] AS OCCUPIED_SUTES_WEEK_END
FROM [CENSUS TRACKER] AS T1
RIGHT JOIN [CENSUS TRACKER] AS T2 ON T2.[WEEK] = DATEADD(WK, 1, T1.[WEEK])
----------------------------------------------------
March 12, 2015 at 7:40 am
Thanks Mark, do you know how I would use this as a trigger? The reason i need to use the trigger, is the lightswitch application is just reading the source table.
March 12, 2015 at 7:41 am
Why? Just put it in a VIEW! Wink
The data is editable by the end-user - they can't directly edit a view can they? If yes, then my problem is solved.
March 12, 2015 at 8:20 am
Enjoy_SQL (3/12/2015)
Why? Just put it in a VIEW! Wink
The data is editable by the end-user - they can't directly edit a view can they? If yes, then my problem is solved.
Sure. There are some limitations of course but it is certainly possible.
https://msdn.microsoft.com/en-us/library/ms187956.aspx
Look closely at the section about "Updateable Views".
_______________________________________________________________
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 12, 2015 at 12:57 pm
Most certainly it is possible. Especially if you are basing your view directly on one physical table.
----------------------------------------------------
March 12, 2015 at 1:20 pm
I keep getting a "Cannot create index on view "TABLE NAME" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view."
I am using a lag() over () portion in the SQL, do you think that may be why? I am trying to index the view now, because when it is connected to my application, the view is grayed out and not editable. 🙁
March 12, 2015 at 2:32 pm
The link posted by Sean explains the conditions under which a view is update-able. If the latest DDL you posted is any indication, you are working with one base table. That is a good start, so read through the link carefully and see if the Sql statement I posted is something you can work with. You never replied if it is helpful or not.
----------------------------------------------------
March 13, 2015 at 5:46 am
Thanks MMartin1, I am trying to use your query in an editable view. The only draw back I saw to the query (which I think I can resolve) is the final week causes a blank - i might just add a false week to populate it.
March 13, 2015 at 10:21 am
I ended up creating a view using similar logic to MMartins1, then build a trigger on the main table to reference the view. It works nicely. 🙂
Thanks everyone for your insights...
March 13, 2015 at 11:09 am
Thanks for posting back. Glad to know this worked out and we were able to help.
----------------------------------------------------
March 14, 2015 at 10:56 am
CELKO (3/11/2015)
>> I appreciate the quick response. Apologies, I am new to the forum .. I will try to clarify. <<Thanks for trying, but you have not learned basic data modeling or SQL. What you posted is not tables! No keys, ISO-11179 violations, computed data, etc. You gave no thought to data types; do you really have a campus with a fifty Chinese character name? Hey, if you allow garbage data, it will come. Why did you embed spaces in column names? Answer: you do not know the ISO rules and you want to do display formatting in a query.
Why did you use strings to insert into an INTEGER column? Answer: that is how it looked on a piece of paper.
You do not know ISO-8601 date formats. After the Metric system, this is the most common IT standard on earth. It is also the only one allowed in Standard SQL. A week is not a DATE; it is called a temporal INTERVAL in Standard SQL. ISO weeks are also part of the ISO-8601 standard; they are written as “yyyyWww-d” where we have a year, week number within year and day number within week.
We use a calendar table to convert the week-date to the calendar_date|and to compute a full week interval. For a quick tool, http://www.onlineconversion.com/day_week_number.htm.
CREATE TABLE Rentals
(campus_name VARCHAR(20) NOT NULL,
building_name VARCHAR(20) NOT NULL,
rental_week CHAR(7) NOT NULL
CHECK (rental_week LIKE '120-90-90-9W0-50-9'),
PRIMARY KEY (campus_name, building_name, rental_week)
suite_cnt INTEGER DEFAULT 0 NOT NULL CHECK (suite_cnt >= 0),
rentable_suite_cnt INTEGER DEFAULT 0 NOT NULL CHECK (rentable_suite_cnt >= 0),
occupied_suite_cnt INTEGER DEFAULT 0 NOT NULL CHECK (occupied_suite_cnt>= 0)
);
You also do not know the proper syntax for insertion. The guy that taught you is an old Sybase programmer. SQL Server has be up to standards for years:
INSERT INTO Rentals
VALUES
('Campus A', 'building A', '2015W14', 50, 55, 45),
('Campus A', 'building A', '2015W15', 50, 55, 0);
I also see that you were taught by a punch card programmer. Back then, we put one data element per card (line) so we could rearrange or re-use them . A modern programmer with a video terminal puts related data elements on the same line.
SELECT campus_name, building_name, rental_week,
occupied_suite_cnt,
LAG(occupied_suite_cnt) OVER (ORDER BY rental_week)
AS prior_occupied_suite_cnt
FROM Rentals;
If you run the above, you will see the LAG() helps shift the occupied_suite_cnt to the following rental_week. I am trying to do this in such a way that when the data changes for the rental weeks of 14 and 15 of 2015, it just carries the occupied_suite_cnt to the following rental_week.
Why did you materialize it? SQL is a language that does not mind virtual data. Oh, you said “ 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. ” But rows are not records. So you want to write a needless trigger. Why? Just put it in a VIEW! 😉
I have been at this for a long time; I have written five triggers in my life and I know at least three of them should now be replaced with DRI actions.
The following bit of your code won't actually work, Joe.
CHECK (rental_week LIKE '120-90-90-9W0-50-9'),
For your convenience, here are some links on the subject of LIKE. You should study them and really take advantage of the "Try it yourself" links so that you can learn how LIKE is supposed to be used, especially since it's ANSI compliant. 😉
http://www.w3schools.com/sql/sql_like.asp
http://www.w3schools.com/sql/sql_wildcards.asp
You do not know ISO-8601 date formats. After the Metric system, this is the most common IT standard on earth. It is also the only one allowed in Standard SQL. A week is not a DATE; it is called a temporal INTERVAL in Standard SQL. ISO weeks are also part of the ISO-8601 standard; they are written as “yyyyWww-d” where we have a year, week number within year and day number within week.
That's one of the more ridiculous things about the ISO-8601 standard. That's even worse than storing formatted dates in a database. It's a form of denormalized data that doesn't even meet first normal form and resists direct use for temporal calculations. At best, the only place such rubbish should be stored is in a Calendar table and then only for display purposes or to normalize poorly formatted inputs and then only once. You shouldn't use such a thing to continuously calculate that which will never change once properly normalized and stored in the database.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2015 at 10:59 am
Enjoy_SQL (3/13/2015)
I ended up creating a view using similar logic to MMartins1, then build a trigger on the main table to reference the view. It works nicely. 🙂Thanks everyone for your insights...
Would you post the code for the view and the trigger, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2015 at 6:22 am
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
March 16, 2015 at 7:53 am
CELKO (3/15/2015)
It is not denormalized; it is another temporal scale exactly like the Common Era Calendar is a scale. This is like saying a measurement in meters instead of US customary inches is denormalized.
In that case and as with all else, I'll give it to you that "It Depends". In this case, it would depend on usage. The first thing that most folks I know as is "What dates does that value represent"? Do you know of any SQL engine that has a function that will convert that ISO format directly to a "week start date"?
As for "proprietary square brackets", if they were proprietary, why would a general SQL tutorial site like w3schools teach their use?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply