Start N End Dates

Nov 7, 2004

hi may i know how do i load a report based on the entered value for start and end date?





I understand that i have to create parameters and dataset but how to make use of them?

View 1 Replies


ADVERTISEMENT

How To Get Aggregate Start And End Dates

Oct 8, 2004

Hi, I'm a bit lost on this SPROC, hoping somebody can jumpstart my brain a bit please.

What I have is tbl_Classes, with a row for every class that's been taught at a school. Each class was taught on certain dates, stored in tbl_Class_Dates. tbl_Class_Dates stores EVERY day the class met, not just the start and end dates. However, in this one case, I want to list class history only showing start and end date... something like:

SELECT Class_ID,
MIN(Class_Date) AS 'Start_Date',
MAX(Class_Date) AS 'End_Date'
FROM tbl_Class_Dates
WHERE ...
GROUP BY Class_ID, Class_Date
HAVING Class_Date > @SomeCutoffDate


Problem is, when I run the above format, it still gives me EVERY class row, and the MAX and MIN functions don't seem to work...

I think I'm on the wrong track here. Incidentally, the old PROC is using a CURSOR and looping through, getting the start and end date and storing it all in a temp table. I'm hoping to improve on that efficiency.

Thanks very much for any help, or let me know if I can clarify better. Cheers!

- james

View 9 Replies View Related

Defaulting Start And End Dates To The Previous Month.

May 17, 2007

I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.

For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.

How can I do this?

View 2 Replies View Related

Create A Datetime Dimension Table With Start And End Dates

Jul 12, 2006

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

View 3 Replies View Related

Integration Services :: How To Get MM:SS When Doing Difference Between Start And End Dates In SSIS

May 28, 2015

I need getting the HH:MM:SS format when we do datepart.

For example, am using a variable to capture the time taken to execute the package using Start and End time stamps captured.

I would like to get the HH:MM:SS format of the datediff.

View 4 Replies View Related

Calculating Length Of Service Categories Based On Start Dates

May 1, 2007

Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)

Basically, I have the following table structure
Employee(employee_number, continuous_start_date, ...)

The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+).
I can work out the length of service in months with the following code

SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months'
FROM employee

So the first stage is to summarise the length of service into groups as mentioned above.

Then the final stage is working out how many people are in each group!

Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)

Thanks later, and in advance :p
-GeorgeV

View 14 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

Validating Start And End Dates In Slow Changing Dimension Table

May 4, 2007

Has anyone written or come across a routine that validates start and enddate in a slow changing dimension? (eg verifies there is no overlap in any of the records).



thanks in advance

View 3 Replies View Related

How To Use The Custom Code (getting Start And End Dates Of Every Month In Date Range) In The Report ?

Mar 29, 2007

Hi all,

I am trying to use the custom code in the report but I don't think I am understanding how this is being used.

I have a function to get starting months for a report parameter.
The function is below:-

--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String

'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))

if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if

Loop

return SqlString

End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.

In the dataset, I had put the following expression
=Code.GetStartingMonths()

However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.

Am I doing something wrong here or is there a better way to doing this ?

Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?

thanks !

Bernard Ong

View 15 Replies View Related

Analysis :: How To Find Quarter / Year / Month / Semester Start And End Dates In MDX

Sep 24, 2015

I have a date dimension set in the SSAS Cube. I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.

View 2 Replies View Related

SQL Server 2008 :: Show Single Placement Dates As Start And End Date For Asset

May 24, 2015

I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).

ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11

I'd like to show the assets with a start date and end date for the placement of the asset.

The start date to be the placement date and the end date to be the next placement date of the asset.

Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.

ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()

I'm guessing some sort of recursion is required here to produce this.

View 7 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

Reporting Services :: Range Chart To Display Timeline (start And End Dates) Side By Side?

Mar 24, 2014

I'm using SQL Server 2008 R2 BIDS to accomplish this.

View 7 Replies View Related

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

Jan 13, 2015

I've got some records like this:

ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0

where each month field has a 0 or 1, depending on if the person was enrolled that month.

I'm being asked to generate a table like this:

ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014

Is there some slam dunk way to do this without a bunch of If/Then statements?

The editor compressed all my space fields, so the column headers are off in some places.

View 8 Replies View Related

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

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?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

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]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

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]...

View 7 Replies View Related

IS Same? (RS Start In Window Severvices V RS Start In Configration)

Mar 30, 2007



1) RS Start in Window Severvices
2) RS Start in Configration

1) and 2) Is Same?

I think that It looks like same Operation.

View 1 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

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 Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

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!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

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?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related

Sql Dates

Mar 30, 2007

Hello All,
 I am trying to Add certain number of days to a particular date.
and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver.
 Please can anyone help me in achieving it.
 thanks
Shiva Kumar

View 2 Replies View Related

Dates

Jan 10, 2002

Hi can anyone help me , or am I on the wrong track.

Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters.
I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'

Thanks

View 1 Replies View Related

SQL Dates

Apr 19, 2004

OK, so I can return (& therefore use) the last day of last month using

select (GETDATE()- DATEPART ( dd , GETDATE()))

Which returns 31-03-2004 (dd-mm-yyyy)

Has anyone a good way of determining a way of deriving say a date such as 01-01-2004 (1st Jan)....

I'd like to get some data where my dmd.sdate falls always in the previous quarter.. but would like it to be not hard coded...

Currently I have

SELECT cust.no,
cust.surname,
dmd.csan,
dmd.sdate
FROM pdunity.dbo.cust cust,
pdunity.dbo.dmd dmd
WHERE cust.rowno = dmd.rowno_custdmd_cust
AND(dmd.sdate
BETWEEN '01/01/2004'
AND(GETDATE()- DATEPART ( dd , GETDATE())))
ORDER by dmd.sdate asc

Is there some form of function I can use to return 01/01/2004 automatically ?

Will

View 6 Replies View Related

Dates ...

Nov 21, 2005

why does this sql return the dates in different formats? Is it a default setting on the server or do i just handle it on SQL?

use northwind

select top 20 orderdate from orders

select top 20 left(orderdate,10) from orders


thanks

fatherjack

View 1 Replies View Related

Dates For The UK

Feb 11, 1999

We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?

View 2 Replies View Related

Dates Between 2 Other Dates

Aug 16, 2002

Hi All

I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.

The startdate is always less than / equal to enddate.

I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.

Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.

I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out

Yusuf

View 1 Replies View Related

Dates

Apr 11, 2008

How would I pull just a list of dates for say the next year or two

View 1 Replies View Related

Dates

Apr 18, 2008

I have an assignment using SQL this is the first time i have used it but i can understand the basics.

It is for an instrument service company and i need to find what instruments have not been serviced for a year.
i already know i have to:

SELECT instrument number, date of last service
FROM Instrument table

but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?

View 4 Replies View Related

Dates Between Two Dates

Apr 26, 2008

Hi friends,
I need to display the dates in between two dates without using tables. two dates are today date and 10th date from today date.

View 17 Replies View Related

Dates

Apr 30, 2008

I have a columns with storeID's and dates i need to be able to find the difference of date for a certain store for its Min(Date) to the End of that Month

example

Date StoreID Difference of Date to end of the
Month
2008-01-02 00:00:00.000 163
2008-01-03 00:00:00.000 165
2008-01-01 00:00:00.000 167
2008-01-02 00:00:00.000 180

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved