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
ADVERTISEMENT
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
Aug 18, 2015
I have a table with multiple rows per staff person. Each of these rows has staff_id, start_date, and end_date. Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical.
How can I do this? I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records')
) DROP TABLE #staff_records;
create table #staff_records
(
staff_id varchar(max),
[Code] ....
View 12 Replies
View Related
Jul 26, 2004
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30--------------------240
Row 2: 40--------------120
Row 3: 50----------------------260
Row 4: ...1300---------1440
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
Thank you for the help!
View 14 Replies
View Related
Oct 18, 2015
IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
DROP TABLE #OverLappingDates
CREATE TABLE #OverLappingDates
(
MemberID Varchar (50),
ClaimID Varchar(50),
StartDate DateTime,
EndDate DateTime
)
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).
View 2 Replies
View Related
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
Nov 7, 2012
Is it possible to determine an index create date in SQL Server 2008 R2? The index in question is not a primary key.
View 8 Replies
View Related
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
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
Oct 20, 2015
[URL] ...
So What I am trying to accomplish is sum up overlapping time ranges while also keeping Unique data rows within the table. So If a data row is unique meaning it is NOT within a overlapping group of data rows then I want to just insert it into a "final table", but if the data rows are overlapping then I want to grab the min(timestart) and the max(timestop) and the PKID of the data row with the max(timestop) within the overlapping group.
I accomplish this task using nested cursors, however When I place my method into a trigger which runs on INSERT, then my trigger never runs and nothing happens. Is there a way to accomplish this without using cursors. I have placed my cursor method into the sql fiddle to be inspected.
create table temp1
( pkid int,
line int,
dateentry date,
timestart time,
timestop time
)
[Code] ....
DESIRED RESULTS:
1060
1
2015-10-01
16:30:00
17:00:00
30
NULL
[Code] .....
View 11 Replies
View Related
Sep 29, 2015
How can I check the overlapping in a simple table like:
Create table forum (cid int, bfrom date, tfrom date, fval int)
Insert into forum values (1, '2014-01-01', '2014-01-31',10),(2, '2014-01-01', '2014-01-31',12),(1, '2014-02-01', '2014-02-28',8),(2, '2014-02-01', '2014-02-28',6),(1, '2014-03-01', '2014-03-31',11),(2, '2014-03-01', '2014-03-31',5),(1, '2014-04-01',
'2014-04-30',14), (2, '2014-03-01', '2014-04-30',12)
In the example above there is an overlapping for the cid 2 in March. How can I check, which select should I apply?
View 5 Replies
View Related
Jul 8, 2015
how can I check the overlapping and the LostPeriod by chargeid?
create table #forum (contractid int, chargeid int, ByFrom date, ByTo date)
insert into #forum values ('7','18','2005-04-01','2007-03-31'), ('7','19','2008-06-01','2010-03-31'),
('7','20','2014-04-01','2015-06-01'),
('8','10','2003-10-01','2005-03-31'),('8','11','2006-12-01','2007-07-31'),
('9','11','2003-10-01','2005-03-31'),('9','12','2004-10-01','2015-03-31')
As lost period I mean that period that is not covered by any chargeid. By overlapping I mean having two or more charge id in the same period.
View 9 Replies
View Related
Oct 4, 2006
i have a table containing following dataeffdate termdate uid----------- ----------- -----------1 2 13 4 25 8 37 9 411 12 512 13 63 6 75 9 8i need to replace all the overlapping records with one recordsuch that resultant table shud look likeeffdate termdate uid1 2 111 13 23 9 3Thanks
View 8 Replies
View Related
Oct 6, 2015
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
RETURN 1
RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
drop table t1
drop function dbo.dt_overlap
View 14 Replies
View Related
Jun 4, 2015
I have this following query,
select [Parent Name],[ID],[Year],[Sales Name],
sum([Total VtM]) as 'Total Sales'
from RegData
group by [Parent Name],[ID],[Year],[Sales Name]
order by [Total Sales] desc
I need to modify this query to get the top 5 of each category based on Total Sales amount.
View 13 Replies
View Related
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
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
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
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
Apr 4, 2007
I'm new to sql. Can someone help me to write a script to select overlapping start dates for each client records.
For example:
Clientid 1 have 3 episode as below(I only want to see the first two records with overlapping start date records)
clientid StratDate EndDate
1 2004-01-01 2004-05-01
1 2004-04-01 2004-05-01
1 2005-04-01 2006-01-01
Table create
CREATE TABLE [dbo].[TABLE_TEST] (
[Client_ID] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[STARTDate] [datetime] NULL ,
[ENDDate] [datetime] NULL ,
)
GO
INSERT
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-01-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-04-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2005-04-01','2006-04-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('2','2004-06-01','2004-07-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('3','2004-09-01','2004-010-01')
Go
Thanks for help
Husman
View 1 Replies
View Related
Oct 23, 2007
I need to identify time spans where members identified as having a condition have NOT had any of 5 specified services in the past 12 months. I have a table (DiabStrata) that identifies time frames for which my data shows a member as having the condition, and I have 5 separate tables with the dates of the relevant services.
I can easily identify when a member hasn't had the service at all, or is lacking it at the start or end of the time frame for which they have the condition, but I'm hitting a wall on how to deal with gaps between the minimum and maximum identification dates.
Code Block
create table dbo.DiabStrata(memberid char(11),Strat tinyint, StratStart datetime, StratEnd datetime)
create table dbo.hba1c(memberid char(11),dos datetime)
insert DiabStrata(
select '1',1,'20060101','20070302'
union
select '1',1,'20070803','20080804'
union
select '2',1,'20020101','20080503')
insert hba1c(
select '1','20060301'
union
select '1','20070301'
union
select '2','20050101')
--Missed Service
Begin
select * into #eval from DiabStrata where strat=1
delete #eval
from #eval left join hba1c on #eval.memberid=hba1c.memberid where hba1c.memberid is null
--repeat for other indicators
update e
set stratstart=min(dos)
from #eval e join hba1c on e.memberid=hba1c.memberid
having min(dos)>stratstart
update e
set stratend=max(dos)+365
from #eval e join hba1c on e.memberid=hba1c.memberid
having max(dos)+365<stratend
delete from #eval where stratstart>stratend
--repeat for other indicators
Desired output is into DiabStrata with a strat of 2 for the time frame for which they have strat 1 but do not have all 5 services within the prior 365 days.
MID Strat StartStrat EndStrat
1 2 1/1/06 - 2/28/06
1 2 3/2/08 - 8/4/08
2 2 1/1/02 - 12/31/04
2 2 1/2/06 - 5/3/08
View 1 Replies
View Related
May 28, 2015
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
View 6 Replies
View Related
Mar 22, 2011
I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.
Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.
I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.
How can I do this ?
View 8 Replies
View Related
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
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
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
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
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, 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 needto use similar logic on an even larger table, so I'd like somethingthat 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 )GOINSERT 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)GOThe rule is basically this... for any given period of time, for aparticular 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.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
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
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
Jun 29, 2005
Hi,
I have a simple table:
Categories
----------
CategoryID
ParentID
Name
I want to associate my Products to a category so that I can search by category....ok. If I just have a simlpe table:
CategoryProducts
-------------------
CategoryId
ProductId
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation. So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation. My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories. I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?
Thanks,
jr.
View 5 Replies
View Related
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
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
