T-Sql - Calculate Each Monday For A Given Time Period. Help??!!
Jul 20, 2005
I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).
Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime
If the user selects a 3 then the begin date is Jan. 1, 2004 and the
end date is June 30, 2004.
Now I need to calculate did any money come in for each week in that
time frame. I need to create a weekly list of all the weeks in that
time frame. Each time frame begins on a Monday. So my list would
look like
1/5/2004
1/12/2004
1/19/2004
1/26/2004
All the way to the end of that time period.
How do I create this weekly list from a given time period using T-SQL?
I would appreciate any and all help on this.
Thanks,
Tony
View 3 Replies
ADVERTISEMENT
Jan 27, 2008
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
Thanks..
View 12 Replies
View Related
Aug 7, 2013
I have a table as seen below and need to calculate the downtime in a given period.
dateandtime Equipment No. Status
1/2/2013 ! 4 ! 0
1/5/2013 ! 3 ! 1
1/8/2013 ! 8 ! 0
1/3/2013 ! 5 ! 1
1/2/2013 ! 1 ! 0
1/4/2013 ! 4 ! 1
The ! is my attempt to make a line between columns...
View 13 Replies
View Related
Jul 17, 2014
I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.
This is the query giving total hours from and to date.
(select sum(t.timespent)/60 from timeitems t
where t.employee = e.code
and t.project = p.code
and t.ndate >= '2014-07-15'
AND t.ndate <= '2014-07-15') as Hours
from projemplink pl
View 1 Replies
View Related
Dec 18, 2007
I need a formula to calculate the time (let's say in minutes) between two dates/times.
The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday).
I will use this in a couple of reports made in Reporting Services.
If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful.
Many thanks!
/Per Lissel
View 3 Replies
View Related
Mar 5, 2008
i have this FUNCTION
and the FUNCTION work OK
now how to add to this FUNCTION another mondy date ?
"last month last monday"
Code Snippet
CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime )
returns table
as
return
(
select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset, 103)
as Date,
[Index],
Number
from
( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all
select 2, 'Second_Monday', 7 union all
select 3, 'Third_Monday', 14 union all
select 4, 'Fourth_Monday', 21 union all
select 5, 'Fifth_Monday', 28
) x
where month(dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7)
= month((dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset)
)
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
now i need to add to the FUNCTION "last month last monday"
like this
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
04/02/2008 last monday 0 ---------------------- i need to add
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
------------------------------
TNX
View 11 Replies
View Related
Dec 15, 2004
Hi,
I want to remove old databases from server.
Any way not running a trace to find if db was not in use for 6 months ?
Thank you
Alex
View 1 Replies
View Related
Jul 20, 2005
HiI am hoping someone might be able to help me out with this.I am writing a helpdesk system which records agents logging in and outof the system.I need to write a stored procedure which will show the peak number ofagents logged in concurrently during a specified time period. Withinthe time period, the person viewing the report should be able tospecify intervals at which to see the data.Eg. There is already a table in the system which holds loggedin/logged out data like22/11/2004 14:02 - 22/11/2004 17:3022/11/2004 09:00 - 22/11/2004 17:1222/11/2004 10:25 - 22/11/2004 16:3022/11/2004 11:02 - 22/11/2004 12:3022/11/2004 16:00 - 22/11/2004 17:30The report user can then say for example they want to view databetween 10th November and 12th November broken down into 15 minutesintervals which would produce a table like this:10/11/2004 00:00 - 10/11/2004 00:1510/11/2004 00:15 - 10/11/2004 00:3010/11/2004 00:30 - 10/11/2004 00:4510/11/2004 00:45 - 10/11/2004 01:00 etc etcAgainst each of these time slots, I need to work out the peak numberof concurrent agents logged in using the first table.Can anyone make any suggestions? The time period the report user canchoose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.Thanks in advance
View 3 Replies
View Related
Apr 15, 2006
There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.
I put together this script to create these functions for several reasons:
1. To allow people to find them on their own without having to post a question.
2. To allow posted questions to be answered with a reference to this script.
3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server.
4. And last, you can actually use them on your application.
The function names created by this script are:
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_X_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )
There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This script was tested with SQL Server 2000 only.
I posted a script for End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Edit 2006-11-01:
Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.
Edit 2007-02-24:
Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code:
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
/*
Functions created by this script:
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )
*/
go
if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_CENTURY end
go
create function dbo.F_START_OF_CENTURY
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_CENTURY
Finds start of first day of century at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000
Returns null if @DAY < 1800-01-01 00:00:00.000
*/
begin
declare @BASE_DAY datetime
select @BASE_DAY = '18000101'
IF @DAY < @BASE_DAY return null
return dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/100)*100,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_DECADE end
go
create function dbo.F_START_OF_DECADE
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_DECADE
Finds start of first day of decade at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000
Returns null if @DAY < 1760-01-01 00:00:00.000
*/
begin
declare @BASE_DAY datetime
select @BASE_DAY = '17600101'
IF @DAY < @BASE_DAY return null
return dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/10)*10,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_YEAR'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_YEAR end
go
create function dbo.F_START_OF_YEAR
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_YEAR
Finds start of first day of year at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(yy,datediff(yy,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_QUARTER'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_QUARTER end
go
create function dbo.F_START_OF_QUARTER
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_QUARTER
Finds start of first day of quarter at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(qq,datediff(qq,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_MONTH'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_MONTH end
go
create function dbo.F_START_OF_MONTH
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_MONTH
Finds start of first day of month at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mm,datediff(mm,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_DAY'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_DAY end
go
create function dbo.F_START_OF_DAY
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_DAY
Finds start of day at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes
*/
begin
return dateadd(dd,datediff(dd,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_HOUR end
go
create function dbo.F_START_OF_HOUR
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_HOUR
Finds beginning of hour
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(hh,datediff(hh,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_30_MIN end
go
create function dbo.F_START_OF_30_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_30_MIN
Finds beginning of 30 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/30)*30,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_20_MIN end
go
create function dbo.F_START_OF_20_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_20_MIN
Finds beginning of 20 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/20)*20,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_15_MIN end
go
create function dbo.F_START_OF_15_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_15_MIN
Finds beginning of 15 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_10_MIN end
go
create function dbo.F_START_OF_10_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_10_MIN
Finds beginning of 10 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/10)*10,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_05_MIN end
go
create function dbo.F_START_OF_05_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_05_MIN
Finds beginning of 5 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/5)*5,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_X_MIN end
go
create function dbo.F_START_OF_X_MIN
(
@DAY datetime,
@INTERVAL int
)
returns datetime
as
/*
Function: F_START_OF_X_MIN
Finds beginning of @INTERVAL minute period
for input datetime, @DAY.
If @INTERVAL = zero, returns @DAY.
Valid for all SQL Server datetimes.
*/
begin
-- Prevent divide by zero error
if @INTERVAL = 0 return @DAY
declare @BASE_DAY datetime
set @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)
return dateadd(mi,(datediff(mi,@BASE_DAY,@Day)/@INTERVAL)*@INTERVAL,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_MINUTE end
go
create function dbo.F_START_OF_MINUTE
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_MINUTE
Finds beginning of minute
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(ms,-(datepart(ss,@DAY)*1000)-datepart(ms,@DAY),@DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_SECOND end
go
create function dbo.F_START_OF_SECOND
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_SECOND
Finds beginning of second
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(ms,-datepart(ms,@DAY),@DAY)
end
go
/*
Start of test script
Load dates to test F_START_OF functions
*/
declare @test_dates table ( DT datetime not null primary key clustered )
declare @today varchar(10)
select @today = convert(varchar(10),getdate(),112)
declare @year varchar(4)
select @year = convert(varchar(4),year(getdate()))
declare @lyear varchar(10)
select @lyear = convert(varchar(10),getdate()-345,112)
insert into @test_dates (DT)
select DT = getdate()union all
select '17530101 00:00:00.000'union all
-- Test start of Decade cutoff
select '17591231 23:59:59.997'union all
select '17600101 23:04:59.997'union all
-- Test start of Century cutoff
select '17991231 23:59:59.997'union all
select '18000101 00:00:00.000'union all
-- Test start of Decade and Century
select '19000101 00:00:00.000'union all
select '19001231 23:59:59.997'union all
select '19400101 00:00:00.000'union all
select '19491231 23:59:59.997'union all
select '19900101 00:00:00.000'union all
select '19991231 23:59:59.997'union all
-- For start of Hour testing
select @lyear+' 00:00:00.000'union all
select @lyear+' 00:59:59.997'union all
select @lyear+' 01:00:00.000'union all
select @lyear+' 01:59:59.997'union all
select @lyear+' 12:00:00.000'union all
select @lyear+' 12:59:59.997'union all
select @lyear+' 17:00:00.000'union all
select @lyear+' 17:59:59.997'union all
select @lyear+' 23:00:00.000'union all
select @lyear+' 23:59:59.997'union all
-- For start of Month, Quarter, and Year testing
select @year+'0101 00:00:00.000'union all
select @year+'0131 23:59:59.997'union all
select @year+'0201 00:00:00.000'union all
select @year+'0228 23:59:59.997'union all
select @year+'0301 00:00:00.000'union all
select @year+'0331 23:59:59.997'union all
select @year+'0401 00:00:00.000'union all
select @year+'0430 23:59:59.997'union all
select @year+'0501 00:00:00.000'union all
select @year+'0531 23:59:59.997'union all
select @year+'0601 00:00:00.000'union all
select @year+'0630 23:59:59.997'union all
select @year+'0701 00:00:00.000'union all
select @year+'0731 23:59:59.997'union all
select @year+'0801 00:00:00.000'union all
select @year+'0831 23:59:59.997'union all
select @year+'0901 00:00:00.000'union all
select @year+'0930 23:59:59.997'union all
select @year+'1001 00:00:00.000'union all
select @year+'1031 23:59:59.997'union all
select @year+'1101 00:00:00.000'union all
select @year+'1130 23:59:59.997'union all
select @year+'1201 00:00:00.000'union all
select @year+'1231 23:59:59.997'union all
-- Test start of 5, 10, 15, 20, and 30 min testing
select @today+' 23:04:59.997'union all
select @today+' 23:09:59.997'union all
select @today+' 23:14:59.997'union all
select @today+' 23:19:59.997'union all
select @today+' 23:24:59.997'union all
select @today+' 23:29:59.997'union all
select @today+' 23:34:59.997'union all
select @today+' 23:39:59.997'union all
select @today+' 23:44:59.997'union all
select @today+' 23:49:59.997'union all
select @today+' 23:54:59.997'union all
select @today+' 23:59:59.997'union all
select '99991231 23:59:59.997'
order by
1
-- Convert dates in @test_dates table to test F_START_OF functions
select
TYPE = 'CENTURY' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_CENTURY( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'DECADE' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_DECADE( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'YEAR' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_YEAR( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'QUARTER' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_QUARTER( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'MONTH' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_MONTH( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'DAY' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_DAY( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'HOUR' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_HOUR( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '30_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_30_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '20_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_20_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '15_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_15_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '10_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_10_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '05_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_05_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'MINUTE' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_MINUTE( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'SECOND' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121)
from
@test_dates
order by
DT
/*
End of test script
*/
CODO ERGO SUM
View 13 Replies
View Related
Jul 12, 2006
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much.
Andrew
View 5 Replies
View Related
Jul 23, 2005
Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks
View 1 Replies
View Related
Apr 17, 2008
Hi,
We are having 8 databases mirrored with witness server, by default automatic failover occure in 10 secs if witness does not get a ping from a principal server.
If there any way to increase the failover time perioed.
Regards
Sufian
View 4 Replies
View Related
Sep 27, 2006
I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.
The dataset I used for a simple test is.....
Trip #
Player
Win
1
1001
1,250
1
1002
50
2
1001
1,450
2
1002
75
3
1001
1,600
3
1002
100
4
1001
2,000
4
1002
175
I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.
Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.
FYI - I have created a working linear regression and a forecasting model off of a cube --- I think I am setting it up correctly.
View 4 Replies
View Related
Aug 29, 2007
Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
Thank you!
View 36 Replies
View Related
Oct 17, 2007
Hello! Given three dimensions in my cube (Product, Calendar, & Sales). I'm trying to create a calculated measure that will sum 8 weeks of sales starting from the third week in which sales started. For instance, weeks 1-4 had zero sales. Week 5 is the first appearance of any sales. Therefore, the measure would sum the sales of weeks 7-14 (8 weeks starting with the 3rd week after sales begin).
Any ideas?? Keep in mind that every product is going to be different (sales start in different weeks).
Thanks in advance for ANY help! Lance
View 1 Replies
View Related
Mar 17, 2004
Hi,
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
what does it means????
Sorry for my english.
Thanks. Eduardo
View 1 Replies
View Related
Nov 8, 2006
Hi
I am getting occasional errors in the event log of our production database where we have some service broker receiving queues running.
The first error is
Lock request time out period exceeded
This is then immediately followed by
An error occurred in dialog transmission: Error: 1222, State: 51.
There are no further errors and everything seems to carry on OK after this error.
Does anybody know what the problem may be?
Anthony
View 1 Replies
View Related
Oct 5, 2006
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
View 5 Replies
View Related
Apr 1, 2008
Hi ,
i am getting error Lock request time-out period exceeded frequently
Could any one explain how can i track the Locking transactions
SP_who,SP_lock helps me partially but i need clear solution
View 3 Replies
View Related
Jul 4, 2006
Hello all,
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
Thanks in advance
View 3 Replies
View Related
Apr 18, 2015
I have a dataset (DATA) like this-
Store Start End Type
XXXX 02-03-2015 10:04:00 02-03-2015 10:08:00 1
XXXX 02-03-2015 10:06:00 02-03-2015 10:10:00 2
XXXX 02-03-2015 10:09:30 02-03-2015 10:12:00 1
YYYY 03-03-2015 20:04:00 03-03-2015 20:12:00 1
YYYY 03-03-2015 20:06:00 03-03-2015 20:10:00 2
YYYY 03-03-2015 20:09:00 03-03-2015 20:16:00 1
YYYY 03-03-2015 20:15:00 03-03-2015 20:18:00 2
YYYY 03-03-2015 20:17:00 03-03-2015 20:22:00 2
YYYY 03-03-2015 20:21:00 03-03-2015 20:27:00 1
The output of this file (RESULT) is-
Store Start End Mins of Type 2 only
XXXX 02-03-2015 10:04:00 02-03-2015 10:12:00 00:01:30
YYYY 02-03-2015 20:04:00 02-03-2015 20:27:00 00:05:00
So for each Store (Store is unique in the table), I am rolling up the intervals with overlaps to create a single interval.
Now, for each store, I want to find the time period for purely type 2. So if there is an overlap, type 1 has the dominance. And I want the sum of time period of whatever is left for type 2.
I have written this code but not able to address the overlap issue:
alter table [DATA] add Outage float;
update [DATA]
set Outage = DATEDIFF(SECOND,[Start],[END])
alter table [RESULT] add [Outage_Type1 (%)] float,[Outage_Type2 (%)] float;
[Code] ....
View 11 Replies
View Related
Oct 11, 2007
I am trying to write a stored procedure that will select information from a SQL table based on a specific time.
For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm.
Any thoughts?
View 21 Replies
View Related
Jan 27, 2009
Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:
[Trade Volume - Tons]
[Trade Volume - Tons MTD]
[Trade Volume - Tons]
is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.
[Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.
If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:
Jan09 Trade Volume Actual: 100 Trade Volume Forecast: 150
Feb09 Trade Volume Actual: 50 Trade Volume Forecast: 200
March09 Trade Volume Actual: 75 Trade Volume Forecast: 225
Then
[Trade Volume - Tons]=100+200+225=525
[Trade Volume - Tons MTD]=100+50=150
This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).
This works well and produces the expected results:
[Trade Volume - Tons]=100+200=350
[Trade Volume - Tons MTD]=100+50=150
However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.To do this we use the following formula for the prior year calculation:
Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))
The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).
View 8 Replies
View Related
Jul 27, 2015
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
View 2 Replies
View Related
Oct 21, 2015
We're running a SQL-Server 2012 and for a while now my accessing records from bigger tables became tricky.There is a Tomcat-8 running which sometimes can't access these tables at all or only after a long delay. As this happened first I went to the Server-Room and opened the Database with the Management Studio to see if there were any issues. open the Database but expanding the directories for "Tables" or "Views" failed after 10 Seconds with the Error 1222.
I turned the Tomcat-8 off to find out whether some unclosed connections are open. Same result, no changes even after one hour.Another 3rd-Party program which we are using seems to connect via other mechanisms to the SQL-Server (Is there a way to list current connections and their types in the Management-Studio, I'm under the impression this program does a lot of caching, it's much faster than the Management-Studio itself.The question is now how can I find out why these time-outs happen? I'm not an expert in SQL-Servers so.
View 8 Replies
View Related
Mar 13, 2014
To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.
The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.
The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.
Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style
This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.
Here is some sample data
USE FF_Winning_Together;
IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL
DROP TABLE #AmendTest;
CREATE TABLE #AmendTest
(
AmendmentIDINT IDENTITY(1,1)NOT NULL,
StyleCHAR(1)NOT NULL,
AmendmentStatusVARCHAR(10)NOT NULL,
DTDATETIMENOT NULL
[code]....
View 7 Replies
View Related
Feb 8, 2015
How to delete the job history for particular runs.
Suppose if a job run 3 times/day. I want to keep the oldest information and delete the recent 2 runs history.
View 5 Replies
View Related
Sep 12, 2013
I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1
Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'
I use Microsoft SQL 2008
View 12 Replies
View Related
Jul 23, 2005
I have an identical SQL database on two machines (my machine and a webserver) that links to a database on a third server (S3). When I executea stored procedure on my machine that accesses a database on S3, italways runs without a problem. However, when I run the same storedprocedure on the webserver (via Query Analyzer on my machine, connectedto the webserver), the stored procedure runs without a problem forabout ten minutes, and then I suddenly receive the error "Login failedfor user '(null)'. Reason: Not associated with a trusted SQL Serverconnection.". If I then connect remotely to the webserver via RemoteDesktop Connection and run the stored procedure there it works, andthen I can run it again on the webserver via Query Analyzer on mymachine, but the same problem will occur again after about ten minutes.The only difference between my machine and the webserver is that thewebserver is running Windows Server 2003, whereas my machine runsWindows XP. All settings in SQL on the two machines are identical, so Iwould love to know if anyone has a possible solution to this seeminglyrandom problem.
View 23 Replies
View Related
Oct 16, 2015
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
View 2 Replies
View Related
Aug 2, 2007
Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.
Thanks
Gary
View 3 Replies
View Related
Jan 24, 2008
Hi all,
I want to raise one question which might not relevant to current discussion.
Prob:-- I have some SSRS reports. I want to know for a given time period
1. How many people hit the report.
2. Who are the users hit the report in that time interval.
Is there any query to get that data?
Or,
should I go somewhere to see the data?
NB:- There is not any table in the application which is storing the data
View 10 Replies
View Related
Mar 21, 2013
What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.
An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.
View 1 Replies
View Related
