Merge Half an Hour slot data into Hour Slot Data

Question

Post reply

Merge Half an Hour slot data into Hour Slot Data

SPtrt

SSChasing Mays

Points: 603
More actions
March 23, 2011 at 1:47 am

#248538

Need to merge the data which in 30min data to near hour data.
Eg: Data @ 12:30 should be sum with 12:00 PM or AM data and store it in 12:00
USE [Tempdb]
GO
/****** Object: Table [dbo].[tblCount] Script Date: 03/23/2011 15:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCount](
[TrafficId] [int] NOT NULL,
[CtDate, CtValue] [datetime] NOT NULL,
[CtValue] [int] Null
) ON [PRIMARY]
GO
INSERT INTO tblCounts(CtDate, CtValue, CtValue) values('3/10/11 2:00 PM',10)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 1:30 PM',20)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 1:00 PM',1)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 12:30 PM',4)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 12:00 PM',34)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 11:30 AM',23)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 11:00 AM',12)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 10:30 AM',54)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 10:00 AM',0)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 9:30 AM',98)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 9:00 AM',12)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 8:30 AM',54)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 8:00 AM',23)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 7:30 AM',65)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 7:00 AM',65)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 6:30 AM',76)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 6:00 AM',12)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 5:30 AM',54)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 5:00 AM',64)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 4:30 AM',09)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 4:00 AM',88)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 3:30 AM',12)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 3:00 AM',54)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 2:30 AM',65)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 2:00 AM',44)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 1:30 AM',32)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 1:00 AM',22)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 12:30 AM',12)
INSERT INTO tblCounts(CtDate, CtValue) values('3/10/11 12:00 AM',11)
INSERT INTO tblCounts(CtDate, CtValue) values('3/11/11 12:00 AM',15)
INSERT INTO tblCounts(CtDate, CtValue) values('3/11/11 12:30 AM',26)
INSERT INTO tblCounts(CtDate, CtValue) values('3/12/11 12:00 AM',10)
INSERT INTO tblCounts(CtDate, CtValue) values('3/12/11 12:30 AM',18)
INSERT INTO tblCounts(CtDate, CtValue) values('3/13/11 12:00 AM',20)
INSERT INTO tblCounts(CtDate, CtValue) values('3/13/11 12:30 AM',34)
I need to merge the CtValue for particular time
FOr instance, Need to merge 12-12:30 AM or PM data for all DATE
ie (1) 3/12/11 => 12-12:30 => CtValue should be 28 and it should be store on 3/12/11 12:00 AM this time
(2) 3/13/11 => 12=12:30 ==> ctValue should be 54 and it should be store in 3/13/11 12:00 AM
Any help
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

I had to tweak your DDL and test data insert statements to get them to work for me...

CREATE TABLE tblCount (

TrafficId int NOT NULL IDENTITY(1,1) PRIMARY KEY,

CtDate datetime NOT NULL,

CtValue int] NULL

)

GO

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T14:00:00',10)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T13:30:00',20)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T13:00:00',1)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T12:30:00',4)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T12:00:00',34)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T11:30:00',23)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T11:00:00',12)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T10:30:00',54)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T10:00:00',0)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T09:30:00',98)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T09:00:00',12)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T08:30:00',54)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T08:00:00',23)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T07:30:00',65)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T07:00:00',65)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T06:30:00',76)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T06:00:00',12)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T05:30:00',54)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T05:00:00',64)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T04:30:00',09)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T04:00:00',88)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T03:30:00',12)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T03:00:00',54)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T02:30:00',65)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T02:00:00',44)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T01:30:00',32)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T01:00:00',22)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T12:30:00',12)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-10T12:00:00',11)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-11T12:00:00',15)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-11T12:30:00',26)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-12T12:00:00',10)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-12T12:30:00',18)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-13T12:00:00',20)

INSERT INTO tblCount(CtDate, CtValue) VALUES('2011-03-13T12:30:00',34)

This query sums the CtValue column with rows grouped by the hour of the CtDate column.

SELECT

DATEADD(hour, DATEDIFF(hour, 0, CtDate), 0) AS CtHour,

SUM(CtValue) AS CtValueSum

FROM tblCount

GROUP BY DATEADD(hour, DATEDIFF(hour, 0, CtDate), 0)

ORDER BY CtDateHour

SPtrt SSChasing Mays Points: 603 More actions · Answer 2

Thanks Andrew.

Your script more helpful. Your answer reaching nearer.

I need to merge only particular hour. ie i need to merge everyday 2330 hrs data with 2300 hrs data and summed data should be store in 2300 hrs.

Thanks,