January 19, 2018 at 12:19 pm
Hello
1. Trying to modify below query to pull data for Week To Date using date from Sunday 3 AM to Sunday 2:59 AM fixed period.
Report is run daily and should show the Week To Date tons for the current week.
I don't know how to get date portion of the Where clause to pull data for this date range.
2. Also need to modify same query to pull Month To Date from 1st day of month 3 AM to last day of month 2:59 AM
Can you help?
Thanks.
Ron
--- Script to total tonnage Week To Date from Sunday 3 AM to Sun 259AM
DECLARE @pStartDate SMALLDATETIME
SET @pStartDate = GETDATE()
SELECT
[DerivedTodaysHoistDetail].[EQ_Hoist_Date],
SUM([DerivedTodaysHoistDetail].[EQ_Hoist_Tons]) AS [sum tons]
FROM
(
SELECT
EQ_Hoist_Date,
EQ_Hoist_Time,
EQ_Hoist_Tons
FROM
EQ_Hoist_Detail
WHERE
[EQ_Hoist_Detail].[EQ_Hoist_Date] BETWEEN DATEADD(wk, DATEDIFF(d, 1, @pStartDate) / 7, 0) AND DATEADD(wk, DATEDIFF(d, 0, @pStartDate) / 7, 6)
---- need this to pull data from Sunday 3 AM to Sunday 2:59 AM date period for Week To Date data
AND
[EQ_Hoist_Detail].[EQ_Hoist_Tons] != 0 --- do not want to include 0 tonnage records
AND
EQ_Hoist_CoSP = '001HC-BIC'
) DerivedTodaysHoistDetail
GROUP BY
[DerivedTodaysHoistDetail].[EQ_Hoist_Date]
ORDER BY [DerivedTodaysHoistDetail].[EQ_Hoist_Date]
---- Create Table script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EQ_Hoist_Detail]
(
[EQ_Hoist_AutoNumber] [INT] IDENTITY(1,1) NOT NULL, --- this is the actual Identity Key for the table
[EQ_Hoist_Key] [NVARCHAR](50) NULL, --- internal "key" for the application
[EQ_Hoist_Company] [NVARCHAR](50) NULL,
[EQ_Hoist_Date] [DATETIME] NULL, --- the main date field
[EQ_Hoist_Tons] [REAL] NULL,
[EQ_Hoist_SP] [NVARCHAR](50) NULL,
[EQ_Hoist_CoSP] [NVARCHAR](50) NULL,
[EQ_Hoist_UserInit] [NVARCHAR](50) NULL,
[EQ_Hoist_UpdateDate] [SMALLDATETIME] NULL,
[EQ_Hoist_OrigDate] [SMALLDATETIME] NULL, --- date original record was created
[EQ_Hoist_Time] [NVARCHAR](50) NULL CONSTRAINT [DF__EQ_Hoist___EQ_Ho__4464E731] DEFAULT (''),
[EQ_Hoist_PLCRunningValue] [REAL] NULL, --- total running value of tons
[EQ_Hoist_PLCDailyRunningValue] [REAL] NULL, --- total running value of tons
[EQ_Hoist_YPS] [INT] NULL --- internal flag for application
) ON [PRIMARY]
---- Insert Test Data script
CREATE TABLE #temptable ( [EQ_Hoist_AutoNumber] int, [EQ_Hoist_Key] nvarchar(50), [EQ_Hoist_Company] nvarchar(50), [EQ_Hoist_Date] datetime, [EQ_Hoist_Tons] real, [EQ_Hoist_SP] nvarchar(50), [EQ_Hoist_CoSP] nvarchar(50), [EQ_Hoist_UserInit] nvarchar(50), [EQ_Hoist_UpdateDate] smalldatetime, [EQ_Hoist_OrigDate] smalldatetime, [EQ_Hoist_Time] nvarchar(50), [EQ_Hoist_PLCRunningValue] real, [EQ_Hoist_PLCDailyRunningValue] real, [EQ_Hoist_YPS] int )
INSERT INTO #temptable
VALUES
( 46645719, N'00101/19/1812:32:08', N'001', N'2018-01-19T12:32:08.98', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:32:08', 6902, 6902, -1 ),
( 46645718, N'00101/19/1812:32:04', N'001', N'2018-01-19T12:32:04.007', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:32:04', 6901, 6901, -1 ),
( 46645717, N'00101/19/1812:31:58', N'001', N'2018-01-19T12:31:58.977', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:58', 6900, 6900, -1 ),
( 46645716, N'00101/19/1812:31:54', N'001', N'2018-01-19T12:31:54', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:54', 6899, 6899, -1 ),
( 46645715, N'00101/19/1812:31:48', N'001', N'2018-01-19T12:31:48.973', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:48', 6897, 6897, -1 ),
( 46645714, N'00101/19/1812:31:43', N'001', N'2018-01-19T12:31:43.997', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:43', 6896, 6896, -1 ),
( 46645713, N'00101/19/1812:31:38', N'001', N'2018-01-19T12:31:38.97', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:38', 6895, 6895, -1 ),
( 46645712, N'00101/19/1812:31:33', N'001', N'2018-01-19T12:31:33.993', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:32:00', N'2018-01-19T12:32:00', N'12:31:33', 6893, 6893, -1 ),
( 46645711, N'00101/19/1812:31:29', N'001', N'2018-01-19T12:31:29.017', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:29', 6892, 6892, -1 ),
( 46645710, N'00101/19/1812:31:23', N'001', N'2018-01-19T12:31:23.99', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:23', 6891, 6891, -1 ),
( 46645709, N'00101/19/1812:31:19', N'001', N'2018-01-19T12:31:19.013', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:19', 6889, 6889, -1 ),
( 46645708, N'00101/19/1812:31:13', N'001', N'2018-01-19T12:31:13.987', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:13', 6888, 6888, -1 ),
( 46645707, N'00101/19/1812:31:09', N'001', N'2018-01-19T12:31:09.033', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:09', 6887, 6887, -1 ),
( 46645706, N'00101/19/1812:31:04', N'001', N'2018-01-19T12:31:04.02', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:31:04', 6885, 6885, -1 ),
( 46645705, N'00101/19/1812:30:59', N'001', N'2018-01-19T12:30:59.043', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:59', 6884, 6884, -1 ),
( 46645704, N'00101/19/1812:30:54', N'001', N'2018-01-19T12:30:54.063', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:54', 6883, 6883, -1 ),
( 46645703, N'00101/19/1812:30:49', N'001', N'2018-01-19T12:30:49.307', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:49', 6882, 6882, -1 ),
( 46645702, N'00101/19/1812:30:45', N'001', N'2018-01-19T12:30:45.667', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:45', 6880, 6880, -1 ),
( 46645701, N'00101/19/1812:30:39', N'001', N'2018-01-19T12:30:39.123', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:39', 6879, 6879, -1 ),
( 46645700, N'00101/19/1812:30:34', N'001', N'2018-01-19T12:30:34.037', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:31:00', N'2018-01-19T12:31:00', N'12:30:34', 6878, 6878, -1 ),
( 46645699, N'00101/19/1812:30:28', N'001', N'2018-01-19T12:30:28.993', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:28', 6876, 6876, -1 ),
( 46645698, N'00101/19/1812:30:24', N'001', N'2018-01-19T12:30:24.017', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:24', 6875, 6875, -1 ),
( 46645697, N'00101/19/1812:30:18', N'001', N'2018-01-19T12:30:18.99', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:18', 6874, 6874, -1 ),
( 46645696, N'00101/19/1812:30:14', N'001', N'2018-01-19T12:30:14.02', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:14', 6872, 6872, -1 ),
( 46645695, N'00101/19/1812:30:09', N'001', N'2018-01-19T12:30:09.073', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:09', 6871, 6871, -1 ),
( 46645694, N'00101/19/1812:30:04', N'001', N'2018-01-19T12:30:04.033', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:30:04', 6870, 6870, -1 ),
( 46645693, N'00101/19/1812:29:59', N'001', N'2018-01-19T12:29:59.033', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:59', 6868, 6868, -1 ),
( 46645692, N'00101/19/1812:29:54', N'001', N'2018-01-19T12:29:54.003', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:54', 6867, 6867, -1 ),
( 46645691, N'00101/19/1812:29:49', N'001', N'2018-01-19T12:29:49.03', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:49', 6866, 6866, -1 ),
( 46645690, N'00101/19/1812:29:44', N'001', N'2018-01-19T12:29:44', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:44', 6864, 6864, -1 ),
( 46645689, N'00101/19/1812:29:39', N'001', N'2018-01-19T12:29:39.023', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:39', 6863, 6863, -1 ),
( 46645688, N'00101/19/1812:29:33', N'001', N'2018-01-19T12:29:33.997', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:30:00', N'2018-01-19T12:30:00', N'12:29:33', 6862, 6862, -1 ),
( 46645687, N'00101/19/1812:29:29', N'001', N'2018-01-19T12:29:29.023', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:29', 6860, 6860, -1 ),
( 46645686, N'00101/19/1812:29:23', N'001', N'2018-01-19T12:29:23.993', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:23', 6859, 6859, -1 ),
( 46645685, N'00101/19/1812:29:19', N'001', N'2018-01-19T12:29:19.017', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:19', 6858, 6858, -1 ),
( 46645684, N'00101/19/1812:29:13', N'001', N'2018-01-19T12:29:13.99', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:13', 6857, 6857, -1 ),
( 46645683, N'00101/19/1812:29:09', N'001', N'2018-01-19T12:29:09.013', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:09', 6855, 6855, -1 ),
( 46645682, N'00101/19/1812:29:03', N'001', N'2018-01-19T12:29:03.983', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:29:03', 6854, 6854, -1 ),
( 46645681, N'00101/19/1812:28:59', N'001', N'2018-01-19T12:28:59.007', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:59', 6853, 6853, -1 ),
( 46645680, N'00101/19/1812:28:54', N'001', N'2018-01-19T12:28:54.03', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:54', 6851, 6851, -1 ),
( 46645679, N'00101/19/1812:28:49', N'001', N'2018-01-19T12:28:49.003', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:49', 6850, 6850, -1 ),
( 46645678, N'00101/19/1812:28:43', N'001', N'2018-01-19T12:28:43.977', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:43', 6849, 6849, -1 ),
( 46645677, N'00101/19/1812:28:39', N'001', N'2018-01-19T12:28:39', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:39', 6847, 6847, -1 ),
( 46645676, N'00101/19/1812:28:33', N'001', N'2018-01-19T12:28:33.97', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:29:00', N'2018-01-19T12:29:00', N'12:28:33', 6846, 6846, -1 ),
( 46645675, N'00101/19/1812:28:28', N'001', N'2018-01-19T12:28:28.997', 2, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:28:00', N'2018-01-19T12:28:00', N'12:28:28', 6845, 6845, -1 ),
( 46645674, N'00101/19/1812:28:23', N'001', N'2018-01-19T12:28:23.97', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:28:00', N'2018-01-19T12:28:00', N'12:28:23', 6843, 6843, -1 ),
( 46645673, N'00101/19/1812:28:18', N'001', N'2018-01-19T12:28:18.99', 1, N'HC-BIC', N'001HC-BIC', N'SYS', N'2018-01-19T12:28:00', N'2018-01-19T12:28:00', N'12:28:18', 6842, 6842, -1 )
January 19, 2018 at 1:31 pm
Assuming that you have no future dates, you only need to find the begin date and then filter based on that.
DECLARE @pStartDate SMALLDATETIME
SET @pStartDate = DATEADD(HOUR, 3, DATEADD(WEEK, DATEDIFF(DAY, -1, GETDATE())/7, -1))
SELECT *
FROM ...
WHERE EQ_Hoist_Detail.EQ_Hoist_Date >= @pStartDate
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2018 at 3:31 pm
Drew:
Thanks for the reply. This will be a report run daily and needs to include the Week To Date tons for the period from Sunday 3AM to the next following Sunday 2:59 AM.
So when report is run on Monday it will have data from Sun 3AM to the date/time the report is ran on Monday. If run on Tues then data from Sun 3AM to the report is ran on Tues, etc. with the week period end being 2:59 AM that following Sunday.
So there will be future dates.
Your code appears to show the correct start of the Sunday 3AM period but I am not seeing the ending period of the following Sunday at 2:59AM.
I am a SQL novice so part of the problem is that I am not understanding exactly the the date code is doing.
Any further help would be most appreciated.
Thanks.
Ron
January 19, 2018 at 3:49 pm
Drew:
I think I have most of the problem solved especially the end period - I just need to change from Hour 3 to Hour 2 with 59 minutes.
I tried the following
DATEADD(HOUR, 2, MINUTE, 59, DATEADD(WEEK, DATEDIFF(DAY, -7, @pStartDate)/7, -1))
DATEADD(HOUR, 2, MIN, 59, DATEADD(WEEK, DATEDIFF(DAY, -7, @pStartDate)/7, -1))
but got syntax errors or "dateadd function requires 3 arguments" error.
Can you please help?
Thanks
Ron
January 22, 2018 at 7:58 am
rsiem - Friday, January 19, 2018 3:49 PMDrew:I think I have most of the problem solved especially the end period - I just need to change from Hour 3 to Hour 2 with 59 minutes.
I tried the followingDATEADD(HOUR, 2, MINUTE, 59, DATEADD(WEEK, DATEDIFF(DAY, -7, @pStartDate)/7, -1))
DATEADD(HOUR, 2, MIN, 59, DATEADD(WEEK, DATEDIFF(DAY, -7, @pStartDate)/7, -1))
but got syntax errors or "dateadd function requires 3 arguments" error.
Can you please help?
Thanks
Ron
It's exactly what it says. The DATEADD function requires 3 arguments and you've given it 5.
param1 hour
param2 2
param3 min
param4 59
param5 DATEADD(WEEK, DATEDIFF(DAY, -7, @pStartDate)/7, -1)
When Intellisense is working, it shows you what each of the parts should be, but, even so, it's a simple matter to look up the correct. HOWEVER, you are calculating a closed interval, and you should always used a half-closed interval when using datatime data. That means that only one side should include the endpoint, or, in other words your formula should be something like a <=b and b < c (where only one side has an equal part). This allows you to include times that fall between 02:59 and 03:00. (We once had a problem where a record was entered between 23:59 and 00:00 and it was not appearing on our report, because the vendor had used closed intervals.)
Also, you've already calculated @StartDate. It's much easier to use that as the baseline for your end date calculation than to start over from scratch. See if you can figure out for yourself how to add one week to @StartDate, which you will use as the open end of your datetime interval.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply