November 3, 2015 at 2:46 pm
Hallo,
I have a table as below.
Timestamp SourceIdChannelIdValue
2015-11-01 16:45:59.95311223,4923
2015-11-01 16:46:00.12712224,9357 *
2015-11-01 16:46:00.32713227,4183 *
2015-11-01 16:46:00.52714221,025 *
2015-11-01 13:17:14.17711223,0304 *
I only want the first value per channel in a new table ( the values marked with *)
I tried with sql below without succes.
set nocount on;
declare @today date;
set @today = CURRENT_TIMESTAMP;
Select * into new_table from (
select @today as 'Now', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth, Timestamp, SourceId,
ChannelId, Unit, Value, DeltaValue, SourceTag, ChannelTag';
if day(@today) = 1
select Top 10 * from RecordedValues where Timestamp >= DATEADD(month, -1, @today) and Timestamp < @today
order by Timestamp;
else
select Top 10 * from RecordedValues where Timestamp >= dateadd(day, -(day(@today)) + 1, @today) and Timestamp < @today
order by Timestamp;) as Maandtabel
Best Regards Erwin
November 3, 2015 at 6:08 pm
This should get you started. Ask questions if you aren't sure what the code is doing. Also, remember that Books Online is your friend.
create table #Channels (
ChannelDate datetime,
SourceId int,
ChannelId int,
Value varchar(128));
insert into #Channels
values
('2015-11-01 16:45:59.953',1,1,'223,4923'),
('2015-11-01 16:46:00.127',1,2,'224,9357'), --*
('2015-11-01 16:46:00.327',1,3,'227,4183'), --*
('2015-11-01 16:46:00.527',1,4,'221,025'), --*
('2015-11-01 13:17:14.177',1,1,'223,0304'); --*
with basedata as (
select
ChannelDate,
SourceId,
ChannelId,
Value,
rn = row_number() over (partition by ChannelId order by ChannelDate asc)
from
#Channels
)
select
ChannelDate,
SourceId,
ChannelId,
Value
from
basedata
where
rn = 1;
November 4, 2015 at 12:01 am
Hallo Lynn,
Was it so simple.
Maybe i told it wrong, but i have at the moment almost 47000 rows of data and i want to filter only the first data of the first day of the month for each channel and placed that in a new table.
Thanks
Erwin
November 4, 2015 at 8:49 am
EJGAJG (11/4/2015)
Hallo Lynn,Was it so simple.
Maybe i told it wrong, but i have at the moment almost 47000 rows of data and i want to filter only the first data of the first day of the month for each channel and placed that in a new table.
Thanks
Erwin
Well, then do what I did, provide DDL for the table, INSERT statements with sample data, and provide the expected results based on the sample data.
It really wouldn't take much to filter the results to the first day of each month. Better idea, why don't you see if you can figure out what changes need to be made and show us what you try.
Writing a SELECT query first is the first step in doing what you want to accomplish. It shows you the data before you do anything else with it.
November 4, 2015 at 8:57 am
FYI, I have a solution already. Just curious what change(s) you think need to be made to the query.
November 5, 2015 at 11:40 am
Haven't heard back, so I thought I'd provide my updated solution:
create table #Channels (
ChannelDate datetime,
SourceId int,
ChannelId int,
Value varchar(128));
insert into #Channels
values
('2015-11-01 16:45:59.953',1,1,'223,4923'),
('2015-11-01 16:46:00.127',1,2,'224,9357'), --*
('2015-11-01 16:46:00.327',1,3,'227,4183'), --*
('2015-11-01 16:46:00.527',1,4,'221,025'), --*
('2015-11-01 13:17:14.177',1,1,'223,0304'), --*
('2015-11-02 14:34:16.337',1,1,'223,1234'),
('2015-12-01 16:45:59.953',1,1,'323,4923'),
('2015-12-01 16:46:00.127',1,2,'324,9357'), --*
('2015-12-01 16:46:00.327',1,3,'327,4183'), --*
('2015-12-01 16:46:00.527',1,4,'321,025'), --*
('2015-12-01 13:17:14.177',1,1,'323,0304'); --*
with basedata as (
select
ChannelDate,
SourceId,
ChannelId,
Value,
rn = row_number() over (partition by ChannelId,dateadd(month,datediff(month,0,ChannelDate),0) order by ChannelDate asc)
from
#Channels
where
ChannelDate >= dateadd(month,datediff(month,0,ChannelDate),0) and ChannelDate < dateadd(month,datediff(month,0,ChannelDate),1)
)
select
ChannelDate,
SourceId,
ChannelId,
Value
from
basedata
where
rn = 1
order by
ChannelDate, ChannelId;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply