March 20, 2009 at 4:16 pm
OK, if you wish to report on the number of rows grouped by day then you need to use an aggregate query and strip out the time component from the date/time column. The following expression can be used to strip the time component from a datetime vaue
DATEADD(day, DATEDIFF(day, 0, StartTime), 0)
...and the aggregate query would be something like:
SELECT
DATEADD(day, DATEDIFF(day, 0, StartTime), 0) AS StartDate,
COUNT(*) AS DailyCount
FROM MyTable
GROUP BY DATEADD(day, DATEDIFF(day, 0, StartTime), 0)
ORDER BY DATEADD(day, DATEDIFF(day, 0, StartTime), 0)
However, you have several complications because your date/time values are stored as character strings, and from the test data you have provided there are at least 4 different formats, e.g.
'2008-09-04T09:00:01'
'2009-01-27T14:32:37.7389657-08:00'
'2009-01-28T12:27:10.910625-08:00'
'2009-01-28T13:07:07.80125-08:00'
Here is your test data reformatted as an INSERT statement.
CREATE TABLE #testData (
Start varchar(40),
Stop varchar(40)
)
INSERT #testData (Start, Stop)
SELECT '2008-09-01T09:00:01', '2008-09-01T10:00:02' UNION ALL
SELECT '2008-09-01T16:00:00', '2008-09-01T16:42:42' UNION ALL
SELECT '2008-09-01T17:00:00', '2008-09-01T17:05:00' UNION ALL
SELECT '2008-09-04T09:00:01', '2008-09-05T08:00:02' UNION ALL
SELECT '2009-01-27T14:32:37.7389657-08:00', '2009-01-27T14:32:37.7389657-08:00' UNION ALL
SELECT '2009-01-27T14:32:37.7389657-08:00', '2009-01-27T14:32:37.7389657-08:00' UNION ALL
SELECT '2009-01-27T14:32:37.7389657-08:00', '2009-01-27T14:32:37.7389657-08:00' UNION ALL
SELECT '2009-01-27T14:32:35.6295907-08:00', '2009-01-27T14:32:35.6295907-08:00' UNION ALL
SELECT '2009-01-27T14:32:35.6295907-08:00', '2009-01-27T14:32:35.6295907-08:00' UNION ALL
SELECT '2009-01-28T12:27:10.910625-08:00', '2009-01-28T12:27:37.42625-08:00' UNION ALL
SELECT '2009-01-28T13:07:07.80125-08:00', '2009-01-28T13:07:38.504375-08:00' UNION ALL
SELECT '2009-01-22T10:48:57.9266883-08:00', '2009-01-22T10:49:19.5967602-08:00' UNION ALL
SELECT '2009-01-22T10:49:30.0178731-08:00', '2009-01-22T10:50:43.0458513-08:00' UNION ALL
SELECT '2009-01-26T11:51:22.9243673-08:00', '2009-01-26T11:52:46.7363309-08:00' UNION ALL
SELECT '2009-01-26T11:54:00.6108581-08:00', '2009-01-26T12:03:19.0291592-08:00' UNION ALL
SELECT '2009-01-26T12:06:13.2467942-08:00', '2009-01-26T13:08:10.285505-08:00' UNION ALL
SELECT '2009-01-26T13:12:15.6589346-08:00', '2009-01-26T13:12:31.5494579-08:00' UNION ALL
SELECT '2009-01-26T15:25:05.4829274-08:00', '2009-01-26T15:54:57.3152096-08:00' UNION ALL
SELECT '2009-01-26T15:55:14.4557249-08:00', '2009-01-26T16:27:32.6933201-08:00' UNION ALL
SELECT '2009-01-26T16:28:24.4429889-08:00', '2009-01-26T16:28:29.0523344-08:00'
You need to do the following as part of the query:
1) Convert the strings to datetime values
2) Adjust the datetime values so that they are relative to the same time zone
Some of the values from your test data is missing time zone information.
Do you know from which time zone these data were collected, or are they UTC times?
The query below parses the date/time strings in any of the 4 formats and adjusts the derived datetime values so that they are relative to a reference time zone that is specified in the @ReferenceTimeZoneOffset parameter. I have made the assumption that any strings missing time zone information are local times relative to the same reference time zone.
DECLARE @ReferenceTimeZoneOffset int /* minutes */
SELECT @ReferenceTimeZoneOffset = -8 * 60 /* Pacific time */
;WITH cteParseDate AS (
SELECT
DATEADD(minute, (
CASE WHEN (RIGHT(Start, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(Start, 5)))
WHEN (RIGHT(Start, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(Start, 5)))
ELSE 0 END),
CONVERT(datetime, SUBSTRING(Start, 1, 19))) AS StartTime
FROM #testData
)
SELECT
DATEADD(day, DATEDIFF(day, 0, StartTime), 0) AS StartDate,
COUNT(*) AS DailyCount
FROM cteParseDate
GROUP BY DATEADD(day, DATEDIFF(day, 0, StartTime), 0)
ORDER BY DATEADD(day, DATEDIFF(day, 0, StartTime), 0)
March 20, 2009 at 9:56 pm
Thanks lot.
I will try to implement and let you know.
These are all test data but i will get soon real data from the user once they start to test the application.
I will be geeting data from different format as i will be getting data from different country where they use our s/w.
Thank you very much for your all efforts to help me put and try to understand my questions and very timely response.
You are such a great man!
Really appreciate your help!
Thanks,
March 30, 2009 at 11:58 am
Thanks Andrew.
I have tried your query and its works fine when i am running by itself but when i am including with SP_SEND_DBMAIL then it throwing follwoing error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.
I have modified query as:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'ps@psco.com',
@body = 'Daily AVG_High',
@query = 'DECLARE @ReferenceTimeZoneOffset int /* minutes */
SELECT @ReferenceTimeZoneOffset = -8 * 60 /* Pacific time */
;WITH cteParseDate AS
(
SELECT
DATEADD(minute, (
CASE WHEN (RIGHT(STOP, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(STOP, 5)))
WHEN (RIGHT(STOP, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(STOP, 5)))
ELSE 0 END),
CONVERT(datetime, SUBSTRING(STOP, 1, 19))) AS STOPTime,
DATEADD(minute, (
CASE WHEN (RIGHT(START, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(START, 5)))
WHEN (RIGHT(START, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(START, 5)))
ELSE 0 END),
CONVERT(datetime, SUBSTRING(START, 1, 19))) AS STARTTime, PLCount
FROM LTrack INNER JOIN
PTrack ON LTrack. PTrackid = PTrack. PTrackid INNER JOIN
SSTrack ON PTrack. SSTrackid = SSTrack. SSTrackid
)
SELECT DATEADD(day, DATEDIFF(day, 0, STARTTime), 0) AS STARTDate,
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0) AS STOPDate,
AVG(PLCount) "AVG Count",
MAX(PLCount) "MAX Count",
COUNT(PLCount) AS DailyCount
FROM cteParseDate
GROUP BY DATEADD(day, DATEDIFF(day, 0, STARTTime), 0),
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0)
ORDER BY
DATEADD(day, DATEDIFF(day, 0, STARTTime), 0),
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0)',
@query_result_width = '250',
@subject = 'AVG_High_PerDay',
@query_attachment_filename = 'AVG_High_PerDay_txt.txt',
@query_result_separator = ',',
@body_format = 'HTML',
@exclude_query_output = '1',
@attach_query_result_as_file = 1;
Please advice me.
Thanks once again for your nice help!
Regards,
April 1, 2009 at 2:00 pm
Your problem may be due to the single quotes in the T-SQL within the @query parameter. To fix this problem you have to double up each single quote character that is in the quoted query string.
For example, if the T-SQL query that you wanted to encode in the @query parameter was:
SELECT Name, Birthdate FROM dbo.Person WHERE Surname = 'SMITH'
then you would need to do the following:
DECLARE @query nvarchar(max)
SELECT @query = 'SELECT Name, Birthdate FROM dbo.Person WHERE Surname = ''SMITH'''
However, to avoid these complications, why not turn your query into a stored procedure and put the T-SQL to call the stored procedure in the @query parameter of the msdb.dbo.sp_send_dbmail stored procedure.
CREATE PROCEDURE Report_Avg_High_PerDay
AS
DECLARE @ReferenceTimeZoneOffset int /* minutes */
SELECT @ReferenceTimeZoneOffset = -8 * 60 /* Pacific time */
;WITH cteParseDate AS (
SELECT
DATEADD(minute, (
CASE WHEN (RIGHT(STOP, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(STOP, 5)))
WHEN (RIGHT(STOP, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(STOP, 5)))
ELSE 0 END), CONVERT(datetime, SUBSTRING(STOP, 1, 19))) AS STOPTime,
DATEADD(minute, (
CASE WHEN (RIGHT(START, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(START, 5)))
WHEN (RIGHT(START, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(START, 5)))
ELSE 0 END), CONVERT(datetime, SUBSTRING(START, 1, 19))) AS STARTTime,
PLCount
FROM LTrack
INNER JOIN PTrack ON LTrack.PTrackid = PTrack.PTrackid
INNER JOIN SSTrack ON PTrack.SSTrackid = SSTrack.SSTrackid
)
SELECT
DATEADD(day, DATEDIFF(day, 0, STARTTime), 0) AS STARTDate,
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0) AS STOPDate,
AVG(PLCount) AS [AVG Count],
MAX(PLCount) [MAX Count],
COUNT(PLCount) AS DailyCount
FROM cteParseDate
GROUP BY
DATEADD(day, DATEDIFF(day, 0, STARTTime), 0),
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0)
ORDER BY
DATEADD(day, DATEDIFF(day, 0, STARTTime), 0),
DATEADD(day, DATEDIFF(day, 0, STOPTime), 0)
GO
----------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'ps@psco.com',
@body = 'Daily AVG_High',
@query = 'EXEC Report_Avg_High_PerDay',
@query_result_width = '250',
@subject = 'AVG_High_PerDay',
@query_attachment_filename = 'AVG_High_PerDay_txt.txt',
@query_result_separator = ',',
@body_format = 'HTML',
@exclude_query_output = '1',
@attach_query_result_as_file = 1;
April 6, 2009 at 11:15 am
Andrew,
Thanks once again for your such a great help!
I really appreciate your time and help to simplify my problem.
Its really magic for new for sql server guys like me.
It runs fien without any error with the EXEC msdb.dbo.sp_send_dbmail and it says: Command(s) completed successfully but its not generating any text file.
I know i am having this issue before to as it runs fine with the stand alone procedure or sql but when i include in my EXEC msdb.dbo.sp_send_dbmail then it says: Command(s) completed successfully but its not generating any text file.
Any idea?
Thanks for your help!
Regards,
April 8, 2009 at 3:02 pm
Hi Andrew,
If you get a chance to look into, really appreciated your help!
April 8, 2009 at 5:03 pm
Hi poratips,
Sorry, I don't have much experience in using Database Mail since I don't use this feature at work.
--Andrew
April 13, 2009 at 10:48 am
Hi Andrew,
Thanks for your help anyway.
April 17, 2009 at 6:32 am
Andrew,
Would you mind to show me how to Bucket (Group Data Range) the data?
I would like to make a group Data Range from my two tables data which counts how many times Printed per Day.
Data is coming from following tables:
SSTrack and PTrack
Please note that my Start and Stop time stored as VARCHAR not DateTime.
I need to count Print Count which is coming from PTrack table and for Counting Days, Start and Stop fields coming from SSTrack table.
Scripts:
CREATE TABLE DTrack](
[DtrackID] [int] IDENTITY(1,1) NOT NULL,
[Tver] [nvarchar](10) NULL,
[Sver] [nvarchar](20) NOT NULL,
[Local] [nvarchar](6) NULL ,
[OS] [nvarchar](50) NOT NULL,
[OSLocal] [nvarchar](6) NULL ,
[PCID] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_DTrack] PRIMARY KEY CLUSTERED
(
[DtrackID] 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
CREATE TABLE [SSTrack](
[STrackID] [int] IDENTITY(1,1) NOT NULL,
[DtrackID] [int] NULL,
[Start] [nvarchar](50) NULL,
[Stop] [nvarchar](50) NULL,
[Srcs] [nvarchar](20) NULL,
CONSTRAINT [PK_SSTrack] PRIMARY KEY CLUSTERED
(
[STrackID] 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
ALTER TABLE [SSTrack] WITH CHECK ADD CONSTRAINT [FK_SSTrack_DTrack] FOREIGN KEY([DtrackID])
REFERENCES [DTrack] ([DtrackID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [SSTrack] CHECK CONSTRAINT [FK_SSTrack_DTrack]
GO
CREATE TABLE [PTrack](
[PTrackID] [int] IDENTITY(1,1) NOT NULL,
[Model] [nvarchar](50) NOT NULL,
[Loc_Flag] [nchar](5) NULL,
[STrackID] [int] NULL,
CONSTRAINT [PK_PTrack] PRIMARY KEY CLUSTERED
(
[PTrackID] 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
ALTER TABLE [PTrack] WITH CHECK ADD CONSTRAINT [FK_PTrack_SSTrack] FOREIGN KEY([STrackID])
REFERENCES [SSTrack] ([STrackID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [PTrack] CHECK CONSTRAINT [FK_PTrack_SSTrack]
GO
CREATE TABLE LTrack](
[LTrackID] [int] IDENTITY(1,1) NOT NULL,
[LType] [nvarchar](50) NOT NULL,
[PLCount] [int] NOT NULL,
[PJCount] [int] NOT NULL,
[PTrackID] [int] NOT NULL,
CONSTRAINT [PK_LTrack] PRIMARY KEY CLUSTERED
(
[LTrackID] 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
ALTER TABLE [LTrack] WITH CHECK ADD CONSTRAINT [FK_LTrack_PTrack] FOREIGN KEY([PTrackID])
REFERENCES [PTrack] ([PTrackID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [LTrack] CHECK CONSTRAINT [FK_LTrack_PTrack]
Insert Scripts:
--SET IDENTITY_INSERT dbo.DTrack ON;
INSERT INTO [dbo].[DTrack]([DTrackID], [TVer], [SVer], [Local], [OS], [OSLocal], [PCID])
SELECT '33', '1.0', '8.0.0.123', 'US', 'MS XP 5.01.2600 Service Pack 2', 'US', '1C64C021E162DCA' UNION ALL
SELECT '34', '1.0', '8.0.1.1', 'US', 'MS NT 5.1.2600 Service Pack 2', 'US', '48659de7abf8' UNION ALL
SELECT '35', '1.0', '8.0.1.1', 'US', 'MS NT 5.1.2600 Service Pack 2', 'US', '48659de7abf8' UNION ALL
SELECT '36', '1.0', '8.0.1.1', 'US', 'MS NT 5.1.2600 Service Pack 2', 'US', '48659de7abf8' UNION ALL
SELECT '37', '1.0', '8.0.1.1', 'US', 'MS NT 5.1.2600 Service Pack 2', 'US', '48659de7abf8'
commit;
[2]
--SET IDENTITY_INSERT [dbo].[SSTrack] on;
INSERT INTO [SSTrack]([STrackID], [DTrackID], [Start], [Stop], [Srsc])
SELECT '95', '33', '2008-09-01T09:00:01', '2008-09-01T10:00:02', 'Regular' UNION ALL
SELECT '96', '33', '2008-09-01T16:00:00', '2008-09-01T16:42:42', 'Merge' UNION ALL
SELECT '97', '33', '2008-09-01T17:00:00', '2008-09-01T17:05:00', 'Regular' UNION ALL
SELECT '98', '33', '2008-09-04T09:00:01', '2008-09-05T08:00:02', 'Regular' UNION ALL
SELECT '99', '34', '2009-01-27T14:32:37.7389657-08:00', '2009-01-27T14:32:37.7389657-08:00', 'Regular' UNION ALL
SELECT '100','34', '2009-01-27T14:32:35.6295907-08:00', '2009-01-27T14:32:35.6295907-08:00', 'Regular' UNION ALL
SELECT '101','34', '2009-01-27T14:32:37.7389657-08:00', '2009-01-27T14:32:37.7389657-08:00', 'Regular'
COMMIT;
[3]
--SET IDENTITY_INSERT [dbo].PLTrack] on;
INSERT INTO [PTrack]([PTrackID], [Model], [Loc_Flag], [STrackID])
SELECT '102', 'Label', 'true ', '95' UNION ALL
SELECT '103', 'Tape ', 'true ', '95' UNION ALL
SELECT '104', 'Label', 'true ', '96' UNION ALL
SELECT '105', 'Label', 'true ', '98' UNION ALL
SELECT '106', 'Label', 'false', '98'
COMMIT;
[4]
--SET IDENTITY_INSERT [dbo].[LTrack] on;
INSERT INTO [LTrack]([LTrackID], [LType], [PLCount], [PJCount], [PTrackID])
SELECT '119', 'Address', '10', '2', '102' UNION ALL
SELECT '120', 'Shipping', '1', '1', '102' UNION ALL
SELECT '121', 'Tape', '1', '1', '103' UNION ALL
SELECT '122', 'Shipping', '1', '1', '104' UNION ALL
SELECT '123', 'Address', '10', '5', '105'
Thanks for your great help!
April 17, 2009 at 7:22 am
To ensure that everyone can understand your requirements properly, can you also show your expected results when run against the test data you have supplied.
Also, how do you want to handle the cases where the Start and Stop date/times in the SSTrack table span a day boundary, e.g.
Start = '2008-09-04T09:00:01', Stop = '2008-09-05T08:00:02'
Thanks,
Andrew
April 20, 2009 at 3:31 pm
Thanks Andrew.
I am not much worried about time boundry which you have asked but its a nice consideration. I am more interested to make it in a Range/Group bucket.
Please see my following requirements:
PLbl info:
----------
Report [a].# of total PLCount (Comes from Ltrack Table -LType)
Report [a]. By Ltype
Report [c].# of Plbl printed in a day
C(i).Average, high (Calculation)
C(ii).Range by group (Calculation)
Ex.
Group range
----- -----
1.0
2.1-4
3.5-10
4.11-20
5.20-50
Report [a] and , i can do but i am having trouble with Report [c].
I will really appreciate your help.
Thanks,
April 21, 2009 at 6:09 am
Sorry but I still don't understand your requirements, such as which columns should be counted and which columns should be grouped / binned. Rather than making a guess as to what you want and getting it wrong, can you show the results you want given the test data you have already supplied. I did give an example of how to bin data in my original post on this thread.
--Andrew
April 30, 2009 at 1:13 pm
Thanks Andrew and sorry for the little delay.
I need to count the column - PLcount
Ex.
When you join these tables then it will show following data:
StartStopPLCount
2008-09-01T09:00:012008-09-01T10:00:0214
2008-09-01T16:00:002008-09-01T16:42:423
2008-09-01T17:00:002008-09-01T17:05:002
2008-09-04T09:00:012008-09-05T08:00:021
Here it will be total count20
2009-01-27T14:32:37.7389657-08:002009-01-27T14:32:37.7389657-08:003
2009-01-27T14:32:37.7389657-08:002009-01-27T14:32:37.7389657-08:003
2009-01-27T14:32:37.7389657-08:002009-01-27T14:32:37.7389657-08:001
2009-01-27T14:32:35.6295907-08:002009-01-27T14:32:35.6295907-08:001
2009-01-27T14:32:35.6295907-08:002009-01-27T14:32:35.6295907-08:002
Here it will be total count10
2009-01-28T12:27:10.910625-08:002009-01-28T12:27:37.42625-08:001
2009-01-28T13:07:07.80125-08:002009-01-28T13:07:38.504375-08:001
Here it will be total count2
Now if yo usee that each there are different counts like 20 on 01, 10 on 27th and 2 on 28th so when i will groupand pu into range bin then i will see each day how many counts and then i will put that total into each individual group like:
GroupRange
10
21 to 4
35 to 10
4 11 to 20
Then our total count will fall into reports like:
DayTotal countsGroup
2008-09-01T09:00:0122
2009-01-27T14:32:37.7389657-08:00103
2009-01-28T12:27:10.910625-08:00204
May 1, 2009 at 3:55 pm
I couldn't reproduce your "joined" data when I ran the following query against your previously supplied base table data (see post on 17th April).
SELECT S.Start, S.Stop, L.PLCount
FROM dbo.LTrack L
JOIN dbo.PTrack P ON (L.PTrackID = P.PTrackID)
JOIN dbo.SSTrack S ON (P.STrackID = S.STrackID)
ORDER BY S.Start
However, assuming that the above table joins are correct, then the following query will...
1) In the CTE cteParseDate, convert the date/time strings in the Start column of the dbo.SSTrack table to a datetime value relative to the reference timezone offset specified in @ReferenceTimeZoneOffset (disregarding any daylight savings).
2) Aggregate the dataset produced by joining the CTE, and the base tables dbo.PTrack and dbo.LTrack, grouping by the date component of the datetime value calculated in the CTE, and summing the PLCount column (TotalCount).
3) The data bin (GroupId) is calculated in a subquery from the aggregate sum of the PLCount column using the data bins defined in the table variable @dataBin.
/* Define data bins */
DECLARE @dataBin TABLE (
BinId int NOT NULL PRIMARY KEY,
MinValue int NOT NULL,
MaxValue int NOT NULL
)
INSERT INTO @dataBin (BinId, MinValue, MaxValue)
SELECT 1, 0, 0 UNION ALL
SELECT 2, 1, 4 UNION ALL
SELECT 3, 5, 10 UNION ALL
SELECT 4, 11, 20 UNION ALL
SELECT 5, 21, 2147483647
/* Define reference time zone */
DECLARE @ReferenceTimeZoneOffset int /* minutes */
SELECT @ReferenceTimeZoneOffset = -8 * 60 /* Pacific time */
;WITH cteParseDate AS (
SELECT
STrackID,
DATEADD(minute, (
CASE WHEN (RIGHT(Start, 6) LIKE '+[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset - DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(Start, 5)))
WHEN (RIGHT(Start, 6) LIKE '-[0-2][0-9]:[0-5][0-9]') THEN
@ReferenceTimeZoneOffset + DATEDIFF(minute, 0, CONVERT(datetime, RIGHT(Start, 5)))
ELSE 0 END),
CONVERT(datetime, SUBSTRING(Start, 1, 19))) AS StartTime
FROM dbo.SSTrack
)
SELECT
DATEADD(day, DATEDIFF(day, 0, StartTime), 0) AS StartDate,
SUM(L.PLCount) AS TotalCount,
(SELECT R.BinId FROM @dataBin R WHERE SUM(L.PLCount) BETWEEN R.MinValue AND R.MaxValue) AS GroupId
FROM cteParseDate S
JOIN dbo.PTrack P ON (S.STrackID = P.STrackID)
JOIN dbo.LTrack L ON (P.PTrackID = L.PTrackID)
GROUP BY DATEADD(day, DATEDIFF(day, 0, S.StartTime), 0)
ORDER BY DATEADD(day, DATEDIFF(day, 0, S.StartTime), 0)
This query produced the following result when run against the base table data specified in your post on 17th April.
StartDate TotalCount GroupId
2008-09-01 00:00:00.000 13 4
2008-09-04 00:00:00.000 10 3
Is this result what you are expecting?
May 8, 2009 at 6:17 am
Thanks once again Andrew.
I will try it and let you know.
Thank you very much for your kind help!
Regards,
Pora
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply