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?
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
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]....
Hi,
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
Thanks for any help.
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!
Hi All
I want to select a certainnumber of rows
select custno, amt, balance from customer where custno='customerno'
when showcust='r' then select rows where amt<balance
when showcust='c' then amt>balance etc
if showcust='' then show everything
Any insight will be greatly apprecaited.
Thanks
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
OK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
TIA
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)
Right now i'm developing a time attendance system.
So in that case i'll be playing with time where that time represents what time an employee come to office and go home.
I'm a little bit confuse which data type i'll be used in this system.
Because there will be a lot of calculation according time data inside the database, such as: how many times an employee comes before 08.00, how much hours that he/she spend in the office that they, etc.
Can somebody help me with this problem, what data type most suitable with this kind of system and how can I do the calculation more easily?
thanx before,
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Hi all,
I have two tables (staging and Cdate) and neither objects has any constraints.
staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
Anyone can help to fix it? Thank you for the help!
update s
set s.date=c.date
FROM cdate c join staging s on(s.id=c.id)
Where s.date is null and id=2
Frank writes "Any suggestion on the best design to store data for resource management so that data can be displayed afterwards in something like a gantt chart.
For example, you have to asign resources, say personID 1 to 100 to fill positions A, B, C, D, E and F
A position must always be filled. A person can only fill one position at a time. You want to be able to detect overlaps when you do your planning.
So if person 2 is in position B from 1 Jan to 15 March, he is currently unavailable, but he should be availabe in my planning for any position from 16 March onwards etc.
Possible queries -
1) list all positions not filled during period XXXX to YYYY
2) List all persons available to fill a certain postion during period XXXX to YYYY
3) List any overlaps where a person is assigned to different postions during overlapping time frames..."
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies View RelatedHi,
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
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..
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 RelatedThere 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
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
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 RelatedI am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All 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 View RelatedHi,
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
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.
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
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
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
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?
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
Hello. I have a SP. I am passing two variables to it that are comma seperated lists (ids) so that I can use an IN clause. But since I cannot use these CSV in an IN clause directly I wrote a temp table that first parses the CS list and creates a temp table with a single column of IDS.Then in a second temp table I am selecting the data using enrollment_id IN (Select enrollment_id From #temptable)Then the SP selects data from the second temp table.This works exactly the way it is supposed to work. But when I call the SP from ASP.NET it doesn't return any data. I just cant seem to return data by selecting from a temp table. And my ASP.NET code is correct because I commented out everything and just returned the parameters I was passing and that worked. So does anyone know how to select data from a temp table in a SP when executed from ASP.NET?I even tried Table Variables and same thing. It works great in SQL but when called from ASP.NET it doesnt return any data. I am guessing it's probably losing its scope. Any quick help would be appreciated. Thank you.Here is a snippet of the SP (even this fails):CREATE PROCEDURE [dbo].[returnEnrollments](@organization_id int, @test_item_list ntext, @enrollment_list ntext)As--select @test_item_list as tilist, @enrollment_list as elistDeclare @report_start_date datetime;Declare @report_end_date datetime;Declare @test_item_id_list varchar(8000);Declare @enrollment_id_list varchar(8000);DECLARE @TestItemID varchar(10), @Pos intDECLARE @EnrollmentID varchar(10)--Set @IDList = @test_item_id_list;--Set @EnrollmentIDList = @enrollment_id_list;Set @test_item_id_list = Cast(@test_item_list As varchar(8000))Set @enrollment_id_list = Cast(@enrollment_list As varchar(8000))Select @report_start_date = report_start_date, @report_end_date = report_end_date From organization Where organization_id = @organization_id;-- Create a temp table to store all the enrollment idsIF OBJECT_ID('tempdb..#EnrollTemp') IS NOT NULLDROP TABLE #EnrollTempCREATE TABLE #EnrollTemp (enrollment_id int NOT NULL)SET @enrollment_id_list = LTRIM(RTRIM(@enrollment_id_list))+ ','SET @Pos = CHARINDEX(',', @enrollment_id_list, 1)IF REPLACE(@enrollment_id_list, ',', '') <> ''BEGIN WHILE @Pos > 0 BEGIN SET @EnrollmentID = LTRIM(RTRIM(LEFT(@enrollment_id_list, @Pos - 1))) IF @EnrollmentID <> '' And @EnrollmentID > 0 BEGIN INSERT INTO #EnrollTemp (enrollment_id) VALUES (CAST(@EnrollmentID AS int)) --Use Appropriate conversion END SET @enrollment_id_list = RIGHT(@enrollment_id_list, LEN(@enrollment_id_list) - @Pos) SET @Pos = CHARINDEX(',', @enrollment_id_list, 1) ENDENDSelect top 20 * From #EnrollTemp Edit: Just used a DataReader instead of a datatable and it works. Dont know why it would fail with a datatable.
View 4 Replies View RelatedI 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] ....