Counts per Day and HIGH and AVG

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counts per Day and HIGH and AVG

Author

Topic

pora
Starting Member

15 Posts

Posted - 2009-03-23 : 14:13:10

Hi,
I am using SQL server 2005 and trying to write a query to count the lblCnt per Day and also need to count AVG and HIGH for the Day.
Please note that my LblCnt column coming from LTrack table and i have Start and Stop columns also which are coming from SSTrack table.
Unfortinately, my Start and Stop columns defined as NVARCHAR instead of Date data type.
The Data coming in this two columns in a different time time zone.
Please see the sample data for these two columns.
I trying to write following query but not sure it will give me correct data as syntex i have found from this forum.

Query:


SELECT  COUNT(LblCnt) AS Total Count per Day, 
          AVG(LblCnt) "AVG Count per Day ",
          MAX(blLCnt) "HIGH",
Dateadd(day, DateDiff(Day, 0, SUBSTRING(Start,1, 19)), 0) As "Num of Days"
FROM     LTrack INNER JOIN
		         PTrack ON   LTrack.PTrackid   =   PTrack.PTrackid INNER JOIN
		         SSTrack ON   PTrack.STrackid =   SSTrack.STrackid
group by 
Dateadd(Day, DATEDIFF(day, 0, SUBSTRING(Start,1, 19)), 0)

Sample Start nad Stop Data stored into table:


Start					Stop

2008-09-01T09:00:01			2008-09-01T10:00:02
2008-09-01T16:00:00			2008-09-01T16:42:42
2008-09-01T17:00:00			2008-09-01T17:05:00

2008-09-04T09:00:01			2008-09-05T08:00:02
2009-01-27T14:32:37.7389657-08:00	2009-01-27T14:32:37.7389657-08:00
2009-01-27T14:32:37.7389657-08:00	2009-01-27T14:32:37.7389657-08:00
2009-01-27T14:32:37.7389657-08:00	2009-01-27T14:32:37.7389657-08:00

2009-01-27T14:32:35.6295907-08:00	2009-01-27T14:32:35.6295907-08:00
2009-01-27T14:32:35.6295907-08:00	2009-01-27T14:32:35.6295907-08:00

Please let me know if you need Table and Insert script.
Thanks for your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 14:39:48

>>I trying to write following query but not sure it will give me correct data...

Isn't easier for you to test the results than for us? Regarding the different timezones, is there anything in your data that will allow you derive what timezone a given entry is from?

Be One with the Optimizer
TG

pora
Starting Member

15 Posts

Posted - 2009-03-23 : 14:56:06

Thanks TG.
I tried to run the query but looks like its not showing correct results:

Count AVG HIGH Days
33 37 100 2008-09-04 00:00:00.000
1 1 1 2009-01-05 00:00:00.000
1 4 4 2009-01-12 00:00:00.000
1 1 1 2009-01-16 00:00:00.000
9 57 250 2009-02-04 00:00:00.000
28 43 220 2009-02-05 00:00:00.000
29 66 570 2009-02-06 00:00:00.000
20 26 266 2009-02-09 00:00:00.000
18 19 151 2009-02-10 00:00:00.000
6 14 51 2009-02-11 00:00:00.000
17 39 142 2009-02-12 00:00:00.000
10 40 213 2009-02-13 00:00:00.000
1 1 1 2009-02-15 00:00:00.000
14 101 427 2009-02-16 00:00:00.000
8 41 210 2009-02-17 00:00:00.000
18 10 56 2009-02-18 00:00:00.000
3 16 37 2009-02-19 00:00:00.000
2 1 2 2009-02-20 00:00:00.000
13 57 500 2009-02-23 00:00:00.000
13 62 500 2009-02-24 00:00:00.000
5 10 41 2009-02-25 00:00:00.000
14 56 362 2009-02-26 00:00:00.000
9 58 259 2009-02-27 00:00:00.000
9 46 189 2009-03-02 00:00:00.000
7 95 313 2009-03-03 00:00:00.000
12 17 56 2009-03-04 00:00:00.000
8 1 4 2009-03-05 00:00:00.000
12 19 105 2009-03-06 00:00:00.000
1 3 3 2009-03-08 00:00:00.000
49 12 89 2009-03-09 00:00:00.000
17 156 1419 2009-03-10 00:00:00.000
28 76 720 2009-03-11 00:00:00.000
26 24 249 2009-03-12 00:00:00.000

and also i am not sure this time about the aTimeZone entry as it will be coming from any country where user use this s/w and we have to keep Track for the user.

Thanks lot.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-23 : 16:08:42

Please provide a sample table with data and desired resutls, it will be easier to assist.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

pora
Starting Member

15 Posts

Posted - 2009-03-24 : 17:02:10

Thanks Vinnie.
Please see the table and insert scritps.


[1]

GO
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]

[2]


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]

[3]


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]




[4]

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]

Please see the Insert script:


DTrack.sql
INSERT INTO  [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;

SStrack_Insert.sql
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;

Ptrack_Insert.sql
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;

LTrack.sql

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'
COMMIT;

Now i need to get the results following:



For PLCount
1a) # of PLCount Counts, 
1b) # of PLCount Counts High and AVG per Day
1c)Range by Group Like:
1. 0
2. 1-4
3. 5-10
4. 11-20
5. 20-50
6. 50-99
7. 100-500
8. above

[2]
2nd Report:PTrack Usage
2a) No of Days Used - AVG 
2b) By Group:
  
1. 0
2. 1
3. 2
4. 3
5. 4
6. 5-9
7. 10-15
8. 16-20
9. 21-25

2c) No of Times used per Day
2c1) AVg
2c2) By Group:

1. 0
2. 1
3. 2
4. 3
5. 4
6. 5-9
7. 10-15
8. 16-20
9. 21-25

Please let me know if you need further information.
Thanks for your help and time!

pora
Starting Member

15 Posts

Posted - 2009-03-26 : 16:20:38

Hi,
If someone can help me, really appreciated.

Regards,

pora
Starting Member

15 Posts

Posted - 2009-04-08 : 16:59:33

Still waiting if some one has time to look into, really appreciated.

Thanks in advance!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 17:11:32

can you please try the script yourself first it has too many problems, then post back.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-08 : 22:41:00

Dear Sir,

Please alter your code below

LTrack.sql
--SET IDENTITY_INSERT  [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'
--SET IDENTITY_INSERT  [LTrack] OFF
COMMIT;

Error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_LTrack_PTrack'. The conflict occurred in database 'superdb_livegitl3', table 'PTrack', column 'PTrackID'.
The statement has been terminated.

It's not working. I'll look into it after you ammend above code Thank you.

Regards,
Michelle

pora
Starting Member

15 Posts

Posted - 2009-04-13 : 13:52:51

Thanks Michelle for your time and efforts and sorry about the having problem with code.
I have tested my code and changed again so please see the create table and insert script.
If you get the error with identity column error, please run the following as a example for specific table:

SET IDENTITY_INSERT [dbo].[LTrack] on;

Script is in sequence:
Create Table Script:
====================
[1]
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]

[2]

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]

[3]
drop table PTrack
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]

[4]

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 Script:
===============
[1]
--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 once again for valuable time and great support.

Regards,
Pora

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources