Number Of 'Periods' In A Date Range

Feb 10, 2006

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriodsINT
)

-- Initial data
insert @Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advance

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Hot To Get Number Of Days For Given Date Range

Sep 15, 2014

--From the rows I want to know how many number of days a person was active for the given date range.

create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values

[Code] ....

--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days

2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days

3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days

4) FromDt = '2013-01-01' ToDt ='2014-12-31'

KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/

View 9 Replies View Related

How To Assing Sequential Number To A Date Range

Oct 7, 2007

I have two tables

1. Po (Purchase Order)
2. PoDet (PoDetails)

Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).

PoDet is child table having PoNo and PoQty

Suppose following records are in both tables

Po:-

OrderNo : 1
PoNo :- Po No 1
ShipDate :- Oct 5, 2007

OrderNo : 1
PoNo :- Po No 2
ShipDate :- Dec 5, 2007


OrderNo : 1
PoNo :- Po No 3
ShipDate :- Oct 5, 2007

PoDet:

PoNo :- Po No 1
PoQty :- 2000

PoNo :- Po No 2
PoQty :- 3000

PoNo :- Po No 3
PoQty :- 4000


I want to generate a Delivery No. which will be generated in this way :-

1. Earlier Shipdates should be assigned a Lower Number
2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number

So meeting the above two conditions, a single SQL should return

PoNo :- Po No 3
DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)


PoNo :- Po No 1
DelNo : 2

PoNo :- Po No 2
DelNo : 3

Can anybody help on this.

View 4 Replies View Related

Create (n) Number Of Records Based On Date Range

Mar 11, 2008

Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:

@StartDate = '01/01/2008'
@EndDate = '01/8/2008'

Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:

01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>

I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.

View 3 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Date Periods

Jun 20, 2007

Hi, this is my first post here, i'm working on a view wich I have to display the results by period.
1st period - Current month
2nd period - Last month
3rd period - Month before last
and so on.

How can I achieve this? of course I have a column wich has the date I want to use, I was thinking on semthing like:

case when DATEDIFF(M, TRXDATE, getdate()) <= 0 then ammount END as Period1
(TRXDATE and ammount are columns on my table)

Any ideas? would this work?

Thanks for your replies

View 2 Replies View Related

How To Compare Date Periods ?

Oct 10, 2005

Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.

any idea ???

thanks

View 14 Replies View Related

Comparing Two Date Periods For Overlapping

Nov 9, 2006

hi guys,

i have a booking table which has the following columns...

booking
-------------------------------------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mike

View 4 Replies View Related

How To Compute Biweekly Periods When Date Is Passed In As Parameter

Oct 7, 2014

How can I compute the biweekly periods when a date is passed in as a parameter, for example if the user enters 9/12/2014 I should get:

Friday /2nd half9/12/2014
Saturday9/13/2014
Sunday9/14/2014
Monday9/15/2014
Tuesday9/16/2014
Wednesday9/17/2014
Thursday9/18/2014
Friday/1st half 9/19/2014
Friday/2nd half9/19/2014
Saturday9/20/2014
Sunday9/21/2014
Monday9/22/2014
Tuesday9/23/2014
Wednesday 9/24/2014
Thursday9/25/2014
Friday/1st half 9/26/2014

View 2 Replies View Related

SQL Server 2012 :: CTE To Return All Periods Between First And Last Transaction Date

Jan 21, 2014

I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:

SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod

[Code] ....

This gives me the following results:

IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131

Now what I need is something that looks like this:

LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135

[Code] .....

Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.

View 9 Replies View Related

Transact SQL :: Date Value Between One Of The Calendar Periods In Multiple Rows?

Aug 20, 2015

Question: How to determine if a date value was between one of the date periods that appear in multiple rows?

Background: We have a table of "license valid" periods, wherein each license can have one or more rows.  (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.)  Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.

My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false.  What is the best way to accomplish this?

DECLARE @ValidityInQuestion TABLE (
LicenseID int
, DateValidityInQuestion date);

DECLARE @LicenseValidPeriods TABLE (
LicenseID int
, BeginDate date
, EndDate date);

[Code] ...

 How then do I query both tables in order to get the same result that results from the following query?

SELECT
12345 AS LicenseID
, '2015-01-15' AS DateValidityInQuestion
, 1 AS LicenseActive
UNION
SELECT
67890
, '2015-02-04'
, 0;

I assume I need to join on the LicenseID columns, but my understanding stops there.

View 4 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related

Reporting Services :: Searching By Single Date Or Date Range

Apr 22, 2015

I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.

View 5 Replies View Related

How To Get Latest Temperature Reading For Each Date In A Date Range

Sep 28, 2012

I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:

Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.

I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable

How could I get the temperature if I did a sub-query here?

View 5 Replies View Related

{RESOLVED} Date Logic - Calculating A Date Range

Jan 23, 2007

I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

View 2 Replies View Related

Query Help - Giving A Date Range Given The Start Date, Thanks!

Jul 23, 2005

Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)

View 2 Replies View Related

Date Picker Controls - Anyway To Limit Date Range

Jul 26, 2007

Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?

View 4 Replies View Related

Expanding A Number Range

Jul 9, 2012

I have a table (we'll call it Ranges) of number ranges:

ID Start End
1 12345 12347
2 12355 12359
3 12380 12389
4...

Note: Start and End are text values..What I need is to output these ranges with their corresponding ID:

ID Number
1 12345
1 12346
1 12347
2 12355
2 12356
2...

Note: Number needs to be text.

View 2 Replies View Related

T-SQL (SS2K8) :: Fill In The Gap Between 2 Number Range?

Apr 2, 2014

In my SQL Server database, I have table with the following records

counter, value1
12345, 10.1
12370, 10.5
12390, 9.7

Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.

For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.

1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3

Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.

why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.

View 9 Replies View Related

Finding Where My Date Falls In Date Range

Oct 25, 2007

Hi;

We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.


I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------


with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View 11 Replies View Related

Finding Highest Current Number Only For Increasing Range

Jan 26, 2012

RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno

[code]...

View 4 Replies View Related

T-SQL (SS2K8) :: Number Of Rows And Range For Each Partition In A Table?

Mar 13, 2015

Is it possible to show the number of rows and the range for each partition in a table ?

This shows me the range but not the row count per partition

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC

View 4 Replies View Related

SQL Server 2014 :: Generate Random Number In Range

Oct 3, 2015

I have a function which generate random number in range :

CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO

If you run this script you always get result between 1 and 4

SELECT dbo.Func_Rand(4, NEWID())

BUT, in this script sometimes have no result !!!!!!! why??

SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )

View 2 Replies View Related

Range In Date

Aug 30, 2007

 
I have a order table which has a orderdate and despatchdate
i want to write a query in such a way that i want to get all the details from the table for a range specified date as the oderdate and despatchdate are user
interactable. I search the google to solve this problem but could not find a answer
 

View 10 Replies View Related

Date Range

Jan 14, 2005

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @Begin Date AND @EndDate. I want to include all of the @EndDate.

View 6 Replies View Related

Sum By Date Range

Aug 9, 2007

I am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.

So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.

Example:

Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now

If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.

Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.

Any quick and easy way to do this?

View 4 Replies View Related

Date Range

Apr 7, 2008

Hey guys..
pls help

what query should I do? Obtaining certain date only using SQL server2000?

Thnx...

View 4 Replies View Related

Date Range

Jun 18, 2006

Hi All,

I am very new to SQL and have a question, hopefully someone can answer.

I have a table of data, one of the fields is a date.

What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".

Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506
dates between 01/06/06 and 31/05/07 is fiscal year 0607

Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???

Any help is much appreciated.

Cheers

Rudi

View 8 Replies View Related

Date Range

Oct 2, 2006

Hi, 'am fairly very new to SQL SERVER 2000.

I have this particular problem....I need to develop a stored proc
where in a parameter checks the dates between JAN 1st and DEC 31st of a particular year. The parameter is declared for year..

I'll mention a small example below...


USE PUBS
declare @year as varchar
set @year = 1993

select * from employee
where hire_date >= '@year-01-01'
and hire_date<='@year-12-31'

This is just an example to show wat i want...
the year is prompted to the user...he/she can select any year

I get the following error message....

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Can anyone help me on this......

Thanks in advance

View 9 Replies View Related

Date Range

Mar 12, 2008

How to display start date and finish date together in the same column with a '-' in between?

2/24/2008 - 3/1/2008

View 3 Replies View Related







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