Working With Date Ranges

Jul 23, 2005

Hello,

I am importing data that lists rates for particular coverages for a
particular period of time. Unfortunately, the data source isn't very
clean. I've come up with some rules that I think will work to clean the
data, but I'm having trouble putting those rules into efficient SQL.
The table that I'm dealing with has just under 9M rows and I may need
to use similar logic on an even larger table, so I'd like something
that can be made efficient to some degree using indexes if necessary.

Here is some sample (simplified) code:

CREATE TABLE Coverage_Rates (
rate_id INT IDENTITY NOT NULL,
coverage_id INT NOT NULL,
start_date SMALLDATETIME NOT NULL,
end_date SMALLDATETIME NOT NULL,
rate MONEY NOT NULL )
GO

INSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',
40.00)
INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',
20.00)
INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',
30.00)
INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',
90.00)
INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',
20.00)
INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',
30.00)
GO


The rule is basically this... for any given period of time, for a
particular coverage, always use the coverage with the highest rate. So,
given the rows above, I would want the results to be:

coverage_id start_dt end_dt rate
----------- ---------- ---------- --------
1 2004-01-01 2004-06-01 40.00
1 2004-06-01 2004-08-01 30.00
2 2004-01-01 9999-12-31 90.00

There can be any combination of start and end dates in the source, but
in my final results I would like to be able to have only one distinct
row for any given time and coverage ID. So, given any date @my_date,

SELECT coverage_id, COUNT(*)
FROM <results>
WHERE @my_date >= start_dt
AND @my_date < end_dt
GROUP BY coverage_id
HAVING COUNT(*) > 1

the above query should return 0 rows.

Thanks for any help!

-Tom.

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: Finding Beginning Date From Multiple Lines Of Date Ranges?

Mar 20, 2015

I am trying to find a beginning date from multiple date ranges, for example:

RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03

For this id: 0793319, my beginning date is 2011-09-06

108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range

For this id: 0793319, my beginning date is 2012-09-04

108203492014-09-022015-06-30

For this id: 0820349, my beginning date is 2014-09-02

To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.

View 9 Replies View Related

How To Pull From Two Date Ranges

Dec 17, 2006

Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
 I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount

View 6 Replies View Related

Comparing Date Ranges

Mar 18, 2004

I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.

Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.

View 3 Replies View Related

Date Ranges Overlapping

Oct 25, 2005

I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.

Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.

The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.

I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.

At my current state, I'm just done.. i can't maintain the perspective... how do I do this?

View 5 Replies View Related

Age Function Help - Date Ranges

May 22, 2008

Hey,

I woudl like to take an age range (Say 22-27) and determine the min and max year, and then take those two DateTime Year values and do a SELECT from a column titled "Birthday Year", which is a an int value like '1984'. Could someone give me a hand with this?


/* Value 1: 1978
Value 2: 1988
*/

SELECT(???) FROM User WHERE ['BirthdayYear'] <> (?Age Function?)
GO


Thanks.

View 3 Replies View Related

Start And End Date Ranges

Apr 16, 2015

I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.

e.g: '2015-4-10' should only return 20 days worth of data.

I have tried the following query:

SELECT
DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH,
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE()))
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth

FROM
tbLogTimeValues
WHERE
DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))

Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

View 5 Replies View Related

Searching By Date Ranges

Dec 11, 2007

Hello Gang,

I have a strange problem that I haven't dealt with before.

I need to execute a piece of code based on date ranges. If the date range is:

Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y


I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.

[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or

[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>

In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.

How do I achieve both # 1 & 2 above in a code? Does this explain better.

Joshi

View 1 Replies View Related

Custom Date Ranges

Mar 15, 2007



I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?

View 1 Replies View Related

Date Ranges Using Only Month And Day Part

Jun 2, 2006

We have some seasonal products that we would like to turn on and off automatically on our website.  To accomplish this, we've added StartDate and EndDate fields to the product table.  See simplified table:
--------------Simple Table:--------------ID int identity (PK)Value varchar(50)StartDate datetimeEndDate datetime
Normally I could just do something like SELECT ID, Value FROM PRODUCTS WHERE StartDate <= @MyDate AND EndDate >= @MyDate
The catch is that we want to ignore the year part of the dates so that we don't have to go back through and update all the records every year.  I'm pretty sure I've done this in the past, but I'm having a brain fart right now and can't remember how.  One of the issues is that the start month could be > the end month (e.g. - October 15 - January 1).  Using code in the application I could work around this (see below), but I would like to handle this in SQL if possible so I don't have to return more records than are needed and weed out the bad records.
'dtStart and dtEnd are Date Objects, StartDate and EndDate are strings containing the MM/DD partIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(myDate) < 0 Then    dtEnd = Date.Parse(EndDate & "/" & (myDate.Year + 1))ElseIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(objDate) > 0 Then    dtStart = Date.Parse(StartDate & "/" & (myDate.Year - 1))End If
I appreciate any help you can provide.
Thanks,Sam

View 1 Replies View Related

Spanning Date Ranges In Query

Apr 23, 2008

Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example

empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008

How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?

Thanks in advance for any advice?

View 11 Replies View Related

Get The Date Ranges For Constant Values.

May 31, 2008

I have a table with date like this.

InstId--Date----Readings
--1---10/12/2008--10
--1---11/12/2008--10
--1---12/12/2008--10
--1---13/12/2008--9
--1---14/12/2008--10
--1---15/12/2008--10
--1---16/12/2008--10
--1---17/12/2008--10
--2---05/03/2008--8
--2---06/03/2008--6
--2---07/03/2008--8
--2---08/03/2008--8
--2---09/03/2008--8
--2---20/03/2008--8

Guys I want to get the date ranges instrument wise for which the instrument readings are constant.

For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.

StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8

Thanks for any help.

View 10 Replies View Related

Checking If Date Ranges Overlap

Nov 6, 2013

Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap as I can't have two prices on one day.

For example if in a table there was:

Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00

The top two overlap.I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:

SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]

[code]....

View 5 Replies View Related

Problem With Query And Date Ranges

Jul 23, 2005

I'm trying to create a query that will tell me which requeststook longer than 10 days to move one from particular state to anotherstate. The query I've created returns the correct requests,but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.I'm sure I'm missing something easy, but I can't figure out what itmight be. Any help is appreciated! Thanks,Myron-- remove SPAM-KILL from address to reply by email --DDL for table creation and data population:CREATE TABLE [dbo].[ReqHistory] ([Id] [int] NOT NULL ,[ReqId] [int] NOT NULL ,[ReqIDStateId] [tinyint] NOT NULL ,[ActionDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[RequestStates] ([ID] [tinyint] NOT NULL ,[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Requests] ([ID] [int] NOT NULL ,[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StatusChangeDate] [datetime] NULL ,[Status] [tinyint] NULL) ON [PRIMARY]GOinsert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))insert into RequestStates values(1,'Awaiting CSMC')insert into RequestStates values(2,'CSMC Review')insert into RequestStates values(3,'Reject Awaiting CSMC')insert into RequestStates values(10,'Awaiting MA Review')insert into RequestStates values(12,'Awaiting FO Review')insert into RequestStates values(13,'Awaiting IS Review')insert into RequestStates values(20,'MA Review')insert into RequestStates values(22,'FO Review')insert into RequestStates values(23,'IS Review')insert into RequestStates values(30,'Func Approval')insert into RequestStates values(31,'Func Approval Complete')insert into RequestStates values(96,'Resolved')insert into RequestStates values(97,'Planning')insert into RequestStates values(98,'Open')insert into RequestStates values(99,'Closed')The query that almost works:select irh.ReqID, irh.MAactiondate, irh.reviewstate,irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextStatefrom (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,irh.ReqIDStateID As IRHState, irs.statetext as ReviewStatefrom ReqHistory IRHjoin requeststates irs on irs.id = irh.ReqIDStateIDwhere irh.ReqIDStateID in (20, 23)group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irhjoin (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetextfrom (select min(actiondate) as actiondate, ReqID,min(ReqIDStateID) as IRH2Statefrom ReqHistory--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,--but I couldn't make it workwhere ReqIDStateID > 23group by ReqID) as irh2join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqIDjoin requests ir on ir.id = irh.ReqIDwhere irh.MAactiondate + 10 < irh2.Nextactiondateorder by irh.ReqIDThe data being returned is:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 96 ResolvedThe data that should have been returned:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMC

View 3 Replies View Related

Combining 2 Tables With Date Ranges

Jun 27, 2006

Hi there, I'm trying to generate a report for an old database and I'mhaving trouble coming up with an elegant way of going about it. Usingcursors and other 'ugly' tools I could get the job done but 1) I don'twant the report to take ages to run, 2) I'm not a big fan of cursors!Basically there are tables that track history and each table tends totrack only a specific value housed within a date range. I'm trying tocombine the tables to get a snap-shot of the complete history. I'mhaving problems dealing with the Start/End Dates from the two tablesand building the dates in the final table to be broken down by 'historytype'.Here are a few sample records and the results I'm trying to achieve:Table 1:CAgyHist (ProdID,AgyID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 5, 20061 2 Jan 5, 2006 Jan 25, 20061 1 Jan 25, 2006 NULLTable 2:CInvHist (ProdID, InvID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 23, 20061 2 Jan 23, 2006 Jan 15, 20061 1 Jan 15, 2006 NULLDesired End Result:CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)1 1 1 Jan 1, 2006 Jan 5, 20061 2 1 Jan 5, 2006 Jan 15, 20061 2 2 Jan 15, 2006 Jan 23, 20061 2 1 Jan 23, 2006 Jan 25, 20061 1 1 Jan 25, 2006 NULLMy challenge thus far has been dealing with the dates as they don'tnecessarily correspond - from one table to the other.I am by no means a database expert of any level and any help would begreatly appreciated.Thanks,Frank.

View 7 Replies View Related

Combing Queries - Grouping By Date Ranges

Aug 2, 2004

Masters,
The below queries return the data that I seek, but I have no idea how to combine them into a single query.


SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [0 to 30]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '0' and '30'

SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [31 to 60]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '31' and '60'

SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [61 to 90]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '61' and '90'

SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [90+]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) > '90'


Any assistance that can be provided will be greatly appreciated.
Grasshopper

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Gaps Within Date Ranges

Sep 13, 2013

I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.

Example dataset SQL below:

CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'

[Code] ....

I would expect a dataset to be returned consisting of:

1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013

I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...

View 9 Replies View Related

Analysis :: Find Date Ranges On Members And SUM

Jul 24, 2015

I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.

WITH MEMBER [Measures].[New Measure] AS 
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))

View 3 Replies View Related

Advice For Storing Reservation/booking Date Ranges...

May 19, 2005

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.
My question is about how to store these dates in the db. At the moment I have the table below:
TABLE Availability [PropertyID] [int] NOT NULL , [StatusID] [tinyint] NOT NULL , [StartDate] [smalldatetime] NOT NULL , [EndDate] [smalldatetime] NOT NULL
I was planning on having four status's for any given date, unknown, available, on hold or booked.
Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.
Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

View 2 Replies View Related

Analysis Services: MDX - Date Ranges With 2 Time Dimensions

Aug 31, 2004

Hi,

I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.

Is there any way that I can have a range of dates?

Is it possible to have 2 time Dimensions for example which did the following:

a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )

Thus I would be able to select a range of dates/months.

Do you know if this is possible to write this inot the dimension?

Thanks,

David

View 3 Replies View Related

Finding Date Ranges That Are Covered By Assignments In Data Set

Jan 14, 2014

I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.

IDCOMPANYPOS_IDSTART_DATEEND_DATE
999119/2/20119/9/2012
999119/10/20129/10/2012
999119/11/20129/11/2012
999119/12/20126/2/2013
999116/3/20136/30/2013

[Code] ....

In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.

Here's another example where there is a gap in the ranges.

IDCOMPANYPOS_IDSTART_DATEEND_DATE
333112011-09-022012-08-31
333112012-09-012012-09-10
333112012-09-112012-09-11
333112012-09-122013-01-06
333112013-09-01NULL

There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.

The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.

View 9 Replies View Related

SQL Server 2012 :: Window Function On Different Date Ranges?

Feb 5, 2014

I have a question regarding windowing functions. I have a sales order table with the columns "orderid", "customerid", "order_date" and "amount". I use the following query to get the amount of every customer as a additional column:

Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid)
FROM sales_orders

My question is if there is a good way to add another column, which includes the SUM(amount) of the customerid, where the order_date > 2012-01-15 , something like this:

Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid),
SUM(amount) OVER (PARTITION BY customerid WHERE order_date > 2012-01-15)
FROM sales_orders

I know, this is not a valid method, so do you know a way to achieve this? Can I maybe use CROSS APPLY or something like this? I know that I could use a subquery to get this, but is there maybe a way / a better way via window functions?

View 9 Replies View Related

SQL Server 2012 :: Filling Gaps In Date Ranges?

Dec 10, 2014

See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.

SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL
SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL
SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL
SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL
SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL

[Code] .....

-- expected result

SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL
SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL

[code].....

View 9 Replies View Related

Fast Date Ranges Without Loops In SQL Server 2000

Sep 28, 2005

The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html

View 3 Replies View Related

Group Results Into Custom Date Ranges/Calendar

Nov 29, 2007

Hello,



I really hope that someone can help me or at least point me in the right direction. I am selecting a set of data and using the date values across the X axis. However the needs exists to group these by week, but these weeks are not the normal weeks, for - they exist as follows the month starts on the first Monday of a month, for example December 2007 starts on Monday the 3rd and the week ends on the 6th of December a so on till the fact that the last week of the month December 2007 starts on Monday the 31st and ends on January the 6th is there any way that I can create a group that could group the datetime values together in this way,



This is not best achieved in SSRS where should I be creating these groups. Any help would really be appreciated.

Many Thanks

Olaf Dedig

View 1 Replies View Related

Transact SQL :: How To Generate Date Ranges From Given List Of Dates

Sep 10, 2015

I want generating Valid date ranges from any list of dates.

The List of Dates could be generated from the below TSQL - 

SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'

And the expected output should look like - 

SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate

View 2 Replies View Related

SQL Server 2014 :: Pivots Date Ranges With Union Data?

Aug 21, 2015

What I am trying to accomplish is to make a few extra columns with specified date ranges.

I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.and also I need to add 2 more columns Prior year current month and This year, current month.

<code>
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14

[code]....

View 2 Replies View Related

Reporting Services :: Conditional Formatting Date Ranges In SSRS

Jun 1, 2015

Running into an error [BC30205] and no values get colored using this syntax

=iif(DateDiff("d",Fields!Last_Reboot.Value,Now()) > 30  And DateDiff("d",Fields!Last_Reboot.Value,Now()) <= 59, "Orange", NOTHING),IIF(DateDiff("d",Fields!Last_Reboot.Value, Now()) >60, "Red",
NOTHING)

View 2 Replies View Related

Transact SQL :: Query To Determine Overlapping Date Ranges (by Category)

May 12, 2015

Given the data below, I have a couple needs:

1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)

2) Query to determine if any date ranges of the same category overlap

declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'

[code]....

View 7 Replies View Related

Power Pivot :: Calculating Monthly Totals From Date Ranges?

Jul 28, 2015

I am looking to calculate the monthly holidays by staff member using DAX. I am able to calculate it  if all entries (Start Date & End Date)  are confined to a unique month. The issue is where a person's holidays go across several months

I have 2 tables

Table 1 - Holidays
Name                        StartDate                                                 EndDate
Joe Bloggs                 27July 2015                                             7th Aug 2015
Jenny Jones               22nd July 2015                                        23rd July 2015
Jenny Jones               27th  July 2015                                        28th July 2015

Table 2 -Months&Years
Month/Year
June 2015
July 2015
August 2015
September 2015

I am looking for two types of Output  (high level and detailed)

Detailed
Month/Year                        Name                         Total  Days OOO
July 2015                          Joe Bloggs                            5
July 2015                          Jenny Jones                          4
August 2015                      Joe Bloggs                          5

High level Month/Year                       Days OOO
June 2015                                0
July 2015                                 9
August 2015                             5

View 4 Replies View Related

Reporting Services :: Setting SSRS Parameters In Subscription For Date Ranges

Jun 4, 2015

I have an SSRS report with parameters for Created On Start and Created On End. Users run this manually and choose the date range to display records for.

I would like to set up two different subscriptions

1) to send weekly on Monday morning for "last weeks" records and
2) to send monthly to send "last month's" records.

View 2 Replies View Related

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

T-SQL (SS2K8) :: Pulling Records From A Table Between Date Ranges In Another Table

Mar 17, 2014

This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?

View 6 Replies View Related







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