October 20, 2011 at 11:14 am
Hello all, I got a problem which is to insert rows for everyday's condition(whole year)and derive a new table according to sample table with applied logic. I'm not really familiar with t-sql, please any help appreciate. My platform is using MS sql 2008 r2.
Please see following:
--sample table will be like this
Declare @myTable Table (ID Int, TIME Datetime, type nvarchar(10))
Insert Into @myTable
Select 1, '2011-07-03 18:45:33.570', off Union All
Select 1, '2011-07-03 19:48:41.520', on Union All
Select 2, '2011-07-03 18:45:33.570', on Union All
Select 2, '2011-07-04 18:45:33.570', off Union All
Select 2, '2011-07-04 19:45:33.570', on Union All
Select 2, '2011-07-04 21:45:33.570', off
--according to @myTable , create a new table
CREATE TABLE AState
(ID INT NOT NULL,
TypeDate time,
TYPE nvarchar(10))
--insert records like this, please see logic as following:
ID TypeDate TYPE
1 1/1/2011 on
1 1/2/2011 on
--...(same record in btw with date difference)
1 7/2/2011 on
1 7/3/2011 on
--...(same record in btw with date difference)
1 10/20/2011 on --update until today
2 1/1/2011 off
--...(same record in btw with date difference)
2 7/2/2011 off
2 7/3/2011 on
2 7/4/2011 on
2 7/5/2011 off
--...(same record in btw with date difference)
2 10/20/2011 off
The logic is as followed:
For example, ID=1, the first change is on 7/3 18:45, TYPE=OFF, then we need to insert before 7/3/2011, all types should be ON
On the day 7/3, we consider if there is one time type='ON', then the whole day is ON, no matter how many times off
after 7/3, the last stage change is 7/3 18:49, TYPE=ON, then we need to insert after 7/3/2011, each rows types should be ON.
ID=2, before 7/3/2011 insert all rows TYPE is OFF. (opposite condition)
7/3 is ON. ON 7/4, TYPE IS ON(one time TYPE is on, whole day on), and after 7/4, insert all rows types should be OFF
October 21, 2011 at 1:50 am
memostone86 (10/20/2011)
The logic is as followed:
For example, ID=1, the first change is on 7/3 18:45, TYPE=OFF, then we need to insert before 7/3/2011, all types should be ON
On the day 7/3, we consider if there is one time type='ON', then the whole day is ON, no matter how many times off
after 7/3, the last stage change is 7/3 18:49, TYPE=ON, then we need to insert after 7/3/2011, each rows types should be ON.
ID=2, before 7/3/2011 insert all rows TYPE is OFF. (opposite condition)
7/3 is ON. ON 7/4, TYPE IS ON(one time TYPE is on, whole day on), and after 7/4, insert all rows types should be OFF
I don't understand this. If you explain in a simpler way, I can help you.
- arjun
https://sqlroadie.com/
October 21, 2011 at 7:24 am
Hello Arjun,
Thanks for reply. Let me explain in another way, hope it works:)
The original table only records when the data is changing, now the problem is to insert everyday's record.
Take ID=1 for example,
Select 1, '2011-07-03 18:45:33.570', off Union All
Select 1, '2011-07-03 19:48:41.520', on Union All
First status change is '2011-07-03 18:45:33.570', it's turned off.
So before '2011-07-03 18:45:33.570', everyday it's turned on.
Last status change is '2011-07-03 19:48:41.520', it's turned on. So after this day, everyday it's turned on.
On day 2011-07-03, because the status shows it's turned on once, so we think this day, it's turned on. (If one day turned on once, no matter how many times changes and what's the status, it's always assumed to be ON)
Take ID=2,
Select 2, '2011-07-03 18:45:33.570', on Union All
Select 2, '2011-07-04 18:45:33.570', off Union All
Select 2, '2011-07-04 19:45:33.570', on Union All
Select 2, '2011-07-04 21:45:33.570', off
First change is '2011-07-03 18:45:33.570', it's on. So before '2011-07-03 18:45:33.570', everyday is OFF.
2011-07-03, it's on
2011-07-04, it's on, because one time ON, we consider it's on
after 2011-07-04, the last status is '2011-07-04 21:45:33.570', off. So after this day, same status remains, everyday is OFF until someday there is a data change.
October 24, 2011 at 3:52 am
Memo, I think I understand the logic. I will post a query shortly and I hope it will help you. In the mean time, you may want to learn about Tally table.
Here's an article by Jeff Moden. I love this guy.
http://www.sqlservercentral.com/articles/T-SQL/62867/
- arjun
https://sqlroadie.com/
October 26, 2011 at 6:23 am
Are you all set on this or do you still need help?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2011 at 7:41 am
CELKO (10/24/2011)
>> I'm not really familiar with T-SQL, please any help appreciate. My platform is using MS SQL 2008 r2.<<Yo also do not know much about data modeling, either :)) We have a date data type, Data elements cannot have silly names like “Recording_id”, TIME (served word) or “type” (of what? Blood?). What you seem to have is a copy of a sign-in/sign-out clip board copied directly into a table. We do not do that.
The nature of time is half-open intervals because it is a continuum. That means a start and end time (the end time is the open side). Read Rick Snodgrass if you want to get up to speed with the basics of temporal data.
The name “type_date” is a nightmare of errors. Those are both attribute properties; that mean you can have a “<something>_type” or a “<something>_date”, but never your silly hybrid. Yuri type is a flag that changes the meaning of another column (not 1NF!).
You also started with the correct ISO-8601 timestamp format, then screwed it up in your sample data.
Let's strt over with a valid design:
CREATE TABLE Recordings
(recorder_id INTEGER NOT NULL,
record_start_timestamp DATETIME2 DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (recorder_id, record_start_timestamp),
record_end_timestamp DATETIME2.--- nul is still active
CHECK (record_start_timestamp < record_end_timestamp)
);
I see that you do not know about the row constructors in T-SQKL now:
INSERT INTO Recordings
VALUES
(1, '2011-07-03 18:45:33.570', '2011-07-03 19:48:41.520'),
(2, '2011-07-03 18:45:33.570', '2011-07-04 18:45:33.570'),
(2, '2011-07-04 19:45:33.570', '21:45:33.570');
>> according to Recordings, create a new table <<
SQL programmers want to keep one fact in one base table and avoid redundancy; we would use a VIEW. What you are doing is punch card data processing, where you had to create a new deck of cards.
You should have a Calendar table. To find the days when recordings were being made:
CREATE VIEW Daily_Recordings
AS
SELECT C.cal_date, recorder_id
FROM Calendar AS C
LEFT OUTER JOIN
Recordings AS R
ON C.cal_date BETWEEN CAST (record_start_timestamp AS DATE)
AND CAST(record_end_timestamp AS DATE)
AND C.cal_dAte BETWEEN '2011-01-01' AND '2011-12-31';
Thank you CELKO for the information:-)
October 26, 2011 at 7:42 am
Jeff Moden (10/26/2011)
@memostone86,Are you all set on this or do you still need help?
Hello Jeff, thanks for reply. I am still working on this.
October 26, 2011 at 10:09 am
memostone86 (10/26/2011)
Jeff Moden (10/26/2011)
@memostone86,Are you all set on this or do you still need help?
Hello Jeff, thanks for reply. I am still working on this.
I understand your requirements and I'll try to make some time tonight to demo a solution for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2011 at 5:24 pm
Wow! My appologies. I lost track of this thread. Let me know if you still need help with this and I'll try to get to it before a month passes like it did this last time. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply