Hi,Just wonder if i can get a period of dates to be inserted into a temp table (with a single field [Sales_Date]) base on a Start and End Date using a select query?For Eg,Start = '8/1/2005', End = '8/5/2005'In the temp table,8/1/20058/2/20058/3/20058/4/20058/5/2005 Your help is appreciated. Thks.RgdsRyan
View 3 RepliesHi all,
I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
Is there any solution to this problem?
Any help will be appreciated.
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedHi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
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
I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10
NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10
and so on for a 6 month period.
I'm using SQL Server 2000.
Example table:
PeopleID Date Status
1 2004-01-01 True
1 2005-01-01 True
1 2006-01-01 True
2 2004-01-01 True
2 2005-01-01 False
2 2006-01-01 True
I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".
I'm wondering I can query this, or if I'm going to have to scan the records manually. :(
I suppose I could write a stored procedure and do some looping too.
Appreciate any help, thanks!
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...
Guys,
I am running SQL Server 2005 Enterp Trial version, which is about to expire in 14 days..
Is this possible to Extend this?
Or
Un-installing and re-installing may give me back 180 days again? I haven't tried this, but need to know before I do an attempt?
Don't have other machine, don't want to re-built my system..
Any help?
Thanks in advance
SKR
Alright here's my situation.
I have two servers both running Eval copies of SQL Server 2005 Enterprise. This is only because I had the promise that I would be getting a shiney new server with a fully licensed copy of the server soon so I wasn't worried when I setup these two programs to use these. Now they are dragging their feet to approve the server and software so I'm scared.
First question - What will happen when the eval period runs out?
Second question - Can I tell what the date was when I installed the server?
These both will help me push management to approve atleast the purchase of an SQL license if I can't get the server.
Thanks.
Rob
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 RelatedHello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.
Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)
Any hint (or web like example) would be very appreciated.
Best regards
Alessandro
Hi,
I have a table of the following structure:
TaskID ResourceName StartDateTime FinishDateTime
12345 Joe 01 Jun 2007 12:30PM 01 Jun 2007 13:30PM
99999 Joe 01 Jun 2007 16:30PM 01 Jun 2007 19:30PM
If I have a new task id that lasts for 1 hour how can I look for the first available space that my "1 hour task " will fit into
i.e i want it to return 01 Jun 2007 13:31PM as my "new task" will fit nicely between TASKID 12345 and Task ID 99999
Hope that makes sense
Thanks
Hi, I am runnig a system with SQL Server 2000, where I get the message (17502): SQL Server evaluation period has expired.
I have valid key for this product, can anybody tell me how to updgrade the SQL Server lisence without a new installation of SQL Server?
Cheers
Kim
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
Thanks!
RETAINDAYS option in BACKUP command.
SqlServer2000 (standard) on Win xp server.
Old backups are not being removed from backup sets.
I've created logical dumpdevices which maps to a physical file on disk - using sp_adddumpdevice. Regular, scheduled backups are successfully written to the backup sets (Complete, differential & Tlog) - This works fine and I can restore from them OK.
I've set the RETAINDAYS option of BACKUP command to keep backups for varying periods (eg full = 8 days, diff = 2 days, and Tlog = 1 day).
When the backups are viewed by 'drilling down' thru >Backup > open backupdevice > view contents.... all previous backups are still available even though their expiry date has passed.
The backup set files (dumpdevice files) are getting inexorably bigger and will eventually fill the available disks !
I don't want to delete or overwrite the entire contents of the backup set - just those backups more than 8 days old.
I've run sp_configure 'media_retention' 1 RECONFIGURE WITH OVERRIDE
but the old backups are still there.
dbs are getting ever bigger (high activity OLTP system) currently 2 production dbs totaling 50 GB.
Is this a bug? - ie. the option to set a retention period even though I'm writing to a logical 'dump device' rather than a normal *.BAK / *.TRN file?
or Am I doing something wrong?
HELP.
Cris Yarker
email to crispin@paymentsystemsltd.com
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
Account History
11
22
33
4NO information for this month
55
SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
THEN LEFT(H.NUMBER,8) END FROM ACCTS C
LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month
My query so far
select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build
Produces following results
IDCreatedDateDate_Docs_In_Complete__cDays
0063000000ausKGAAY2010-03-262013-07-161208
0063000000mC359AAC2011-06-302013-07-03734
0063000000oyaSPAAY2011-11-292013-07-18597
Desired outcome results would be
Year Month Date_Docs_In_Complete__c Total Days Avg_Days
2013 07 3 2539846.3
Hi- I let my sql server evaluation expire and I have no access to the database now. Is there anyway I can create a new server on my old sql server 7 software and import the data? I have stored procedures that I really need to access. I see where the data files are but is there a way to get them opened by SQL Server 7 when I created it all in the latest version?
Thanks
Nicki
Hi people. I hope you can help with this. It has me completely stumped.
I am trying to get SQLS 2000 to calculate days between dates, without any success at all.
The two dates are as imported from a text file, and are as follows: -
(@issueddate=) 3/01/2006 3:37:00 PM
(@completiondate=) 04-FEB-2006 18:00
I cannot do much about the date formats, as they are as received from another party, as an email attachment.
I have tried just about everything I know to get this working, even tried commenting out each line till practically nothing is left, and still get the same message. Have also deleted the procedure and recreating it.
I have tried removing "PM" from the first date; tried changing varchar to datetime; tried using 'dd' instead of 'd' in the DateDiff function
Because of the "Aborted" response in Query Analyser, nothing is returned, and yet the Query Analyser's "Execute"
Command reports it completed successfully.
Should I ignore this "Aborted" response altogether and assume that it works?
Is it a known bug in SQLS 2000?
Here is the Stored Procedure:
----------------------------
CREATE procedure proc_calcDeadline
(@issueddate varchar(30)='',@completiondate varchar(30)='',@return int=NULL output)
as
declare @Period int
--set @issueddate='3/01/2006 3:37:00 AM'
--set @completiondate='04-FEB-2006 18:00'
set @period=datediff(d,@issueddate,@completiondate)
--print 'period=='+cast(@period as varchar)
set @return=@period
return @return
GO
The DEBUG response in Query Analyser is as follows: -
"Aborted"
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
When I make changes in Query Analyser, (ie, "Alter Procedure', etc" then select "EXECUTE" option
the response is: -
"The command(s) completed successfully."
PS I am STILL unable to get fully into Query Analyser for step-by-step processing, even though I created the database on a single workstation (and therefore am the dbowner, with full Admin rights).
(Homesick for VBA territory, where I can get date calcs like this done easily!!)
Lester Vincent
Sydney
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
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
I have select statement that returns data on a construction project.
I have a start date, end date, and forecasted cost for each task in the project. I need to create a table that spreads the dollars in a linear fashion broken down by fiscal period.
I have:
task_id, start_date, end_date, cost
1, 9/15/2008, 12/15/2008, 3000
2, 7/1/2008, 12/15/2008, 550
I need
task_id, fiscal_period, cost
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500
2, 200801, 100
2, 200802, 100
2, 200803, 100
2, 200804, 100
2, 200805, 100
2, 200806, 50
I can do the math to properly calculate the dollar amount, I am having trouble creating the statement that will process through each row of my select statement and insert multiple rows into the new table.
I have TableA with the following structure and Data.
CaseID ActionID StartDate
------ ------------ --------------
A232/21/2007
A212/8/2007
B2212/4/2007
B1310/12/2007
B1710/7/2007
C617/6/2007
D3311/2/2007
D5610/22/2007
D267/29/2007
E226/21/2007
E585/25/2007
E874/8/2007
E293/23/2007
Expected Results:
I want to create TableB with the following structure and data .
CaseIDX YZ
----- - - -
A001
B011
C000
D001
E111
X = 1 when a case has 4 or more actions within 180 days , 0 otherwise
Y= 1 when a case has 3 or more actions within 90 days , 0 otherwise
Z= 1 when a case has 2 or more actions within 30 days , 0 otherwise
Any help will be welcomed
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 RelatedHow do you set the "Timeout Period" in SQL Server 2005?
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 Related