Help with T-Sql
-
I have a table which looks like this:
USE [DB_render]GO
/****** Object: Table [render].[tblActivities] Script Date: 18/08/2015 15:08:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [render].[tblActivities](
[ActType] [varchar](50) NOT NULL,
[isoTimeStamp] [datetime] NULL,
[isoStart] [datetime] NULL,
[isoEnd] [datetime] NULL,
[keyChart] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 03:42:28.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'ajar', NULL, CAST(N'2015-08-16 09:52:19.000' AS DateTime), CAST(N'2015-08-16 10:52:19.000' AS DateTime), NULL)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'ajar', NULL, CAST(N'2015-08-16 19:17:54.000' AS DateTime), CAST(N'2015-08-16 19:50:54.000' AS DateTime), NULL)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 06:11:21.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 06:42:40.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 07:06:29.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 07:15:23.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 07:15:31.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:20:55.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:21:39.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:38:10.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:38:18.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:38:49.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:39:32.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 08:39:55.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:19:08.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:52:03.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:52:12.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:52:19.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:52:25.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 09:52:33.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 11:26:40.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 12:14:48.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 12:16:27.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 12:16:52.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 13:16:19.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 13:17:07.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 13:17:37.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 15:52:34.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 16:03:06.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 16:13:15.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 16:13:25.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 19:17:48.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 19:17:54.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'motion', CAST(N'2015-08-16 19:18:14.000' AS DateTime), NULL, NULL, 216520)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'ajar', NULL, CAST(N'2015-08-12 00:46:16.000' AS DateTime), CAST(N'2015-08-12 00:48:59.000' AS DateTime), NULL)
INSERT [render].[tblActivities] ([ActType], [isoTimeStamp], [isoStart], [isoEnd], [keyChart]) VALUES (N'ajar', NULL, CAST(N'2015-08-11 06:45:34.000' AS DateTime), CAST(N'2015-08-11 08:48:59.000' AS DateTime), NULL)
I am struggling with my query so I was wondering if someone can help me. I want sql to output two columns hours and points. Hours will be from 0 to 24 and points will be based on the calculation.
Hours: If ActType is 'Motion' hours 0-24 will match the isoTimeStamp and if it is 'Ajar' hours will match the isoEnd - i think this can be achieved by DATEPART function - please correct me if im wrong.
Points: Motion will be 10 points and Ajar will be 150 points. Therefore the query will count all the motions in that same hour and times it by 10 to calculate the total points for that hour. Same for Ajar; query will count all the ajars in that same hour and times it by 150 to calculate the ajar points. Query will do this for every hour which matches with isoTimeStamp for motion and isoEnd for ajar.
So the resultset of the query will look something like this:
Any help or advice how i can achieve this query will be very useful.
Thank you in advance
-
Excellent job on putting together sample data. Can you post up what you expect your output to look like? Cheers.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (8/18/2015)
Excellent job on putting together sample data. Can you post up what you expect your output to look like? Cheers.Hi, Thank you for your reply. I am expecting the output to look something like this:
-
Here's a halfway point:
SELECTa.*,
x.[hours],
Points = COUNT(*) OVER(PARTITION BY a.ActType, x.[hours]) * x.points
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType = 'Ajar' THEN DATEPART(HOUR, a.isoEnd) END,
points = CASE
WHEN a.ActType = 'Motion' THEN 10
WHEN a.ActType = 'Ajar' THEN 150 END
) x
ORDER BY x.[hours]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (8/18/2015)
Here's a halfway point:SELECTa.*,
x.[hours],
Points = COUNT(*) OVER(PARTITION BY a.ActType, x.[hours]) * x.points
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType = 'Ajar' THEN DATEPART(HOUR, a.isoEnd) END,
points = CASE
WHEN a.ActType = 'Motion' THEN 10
WHEN a.ActType = 'Ajar' THEN 150 END
) x
ORDER BY x.[hours]
Hi Chris,
Thank you for taking time and writing the query for me. The query is returning hours like 9 10 12 12 12 because there are 3 records at 12 o clock.
Is there any way i can hide the repeated hours, i tried to just select hours and points from your query but it still gives me the following result set with repeated hour. What I would like is 24 rows with 00 to 23 hours. Is their anyway I can achieve this?
Thank you again for your help, really appreciate it.
-
Sure - try this:
SELECTh.[hours],
a.ActType,
Activities = COUNT(a.ActType),
Points = COUNT(*) * x.points
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType = 'Ajar' THEN DATEPART(HOUR, a.isoEnd) END,
points = CASE
WHEN a.ActType = 'Motion' THEN 10
WHEN a.ActType = 'Ajar' THEN 150 END
) x
RIGHT JOIN (
VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)
) h ([Hours])
ON h.[Hours] = x.[hours]
GROUP BY ActType, h.[hours], x.Points
ORDER BY h.[hours]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thank you so much for the updated query Chris. That's is exactly what I was after, really appreciate your time and help with the query.
-
maxlezious (8/18/2015)
Thank you so much for the updated query Chris. That's is exactly what I was after, really appreciate your time and help with the query.You're welcome. Sample data, a good description of the problem - it doesn't get any better than this. Thank you for putting in the effort and making it easy. 😎
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Chris, I am sorry to bother you again. I am trying to fiddle with the query you gave me to sum up the ajars and motions in same row
For example:
In above picture you can see there are two records on row 1 and 2 both hours '0' for ajar and motion. What I am trying to do is combine the points for both ajar and motion so it will return me: Hours: 0 and Points: 930
I am trying to use the sum function instead of count but it is not really working for me, I have attached the code below and was wondering if you can give me some advice.
Thank you again.
SELECT h.[hours]--,a.ActType
,Activities = sum(a.ActType)
,Points = COUNT(*) * x.points
FROM #temp009988 a
CROSS APPLY (
SELECT [hours] = CASE
WHEN a.ActType = 'Motion'
THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType = 'Ajar'
THEN DATEPART(HOUR, a.isoStart)
END
,points = CASE
WHEN a.ActType = 'Motion'
THEN 10
WHEN a.ActType = 'Ajar'
THEN 150
END
) x
RIGHT JOIN (
VALUES (0)
,(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)
,(11)
,(12)
,(13)
,(14)
,(15)
,(16)
,(17)
,(18)
,(19)
,(20)
,(21)
,(22)
,(23)
,(24)
) h([Hours])
ON h.[Hours] = x.[hours]
GROUP BY --ActType
--,
h.[hours]
,x.Points
ORDER BY h.[hours]
-
-- Sure, just remove ActType and Points from the GROUP BY...SELECT
h.[hours],
Activities = COUNT(a.ActType),
Points = SUM(x.points)
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType = 'Ajar' THEN DATEPART(HOUR, a.isoEnd) END,
points = CASE
WHEN a.ActType = 'Motion' THEN 10
WHEN a.ActType = 'Ajar' THEN 150 END
) x
RIGHT JOIN (
VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)
) h ([Hours])
ON h.[Hours] = x.[hours]
GROUP BY h.[hours]
ORDER BY h.[hours]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Absolute star, i am your fan now. Thank you again 🙂
-
maxlezious (8/20/2015)
Absolute star, i am your fan now. Thank you again 🙂Oh gosh :blush: I'm just a coder. You'll find this stuff as natural as counting on your fingers in no time!
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Really hope I become as good as you soon. Would you recommend me any t-sql books or just practicing on daily basis?
-
maxlezious (8/20/2015)
Really hope I become as good as you soon. Would you recommend me any t-sql books or just practicing on daily basis?I'm planning on buying these at the weekend:
Grant Fritchey’s third edition SQL Server Execution Plans
Wayne Sheffield SQL Server T-SQL Recipes 2015
T-SQL Querying (Developer Reference) Paperback – 6 Mar 2015
Microsoft SQL Server 2014 Query Tuning & Optimization (Nevarez, Benjamin; McGraw-Hill Professional - McGraw-Hill Education – Europe
Hands-on experience is unbeatable. You could always try a few questions on here without posting up your code then compare what you've got with the chosen solutions - until you are more comfortable about posting up your own solutions. This amazing place is a fantastic learning tool and participation keeps up the interest levels.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
I have the Microsoft SQL Server 2014 Query Tuning & Optimization and I will lookup on the other books you mentioned - thank you
I have been a silent reader of the forum but yeah that's a great advice, I will start reading up on the questions and try to solve the problem on my own and then compare my answers to the chosen solutions.
Thank you again for all your help and advice.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply