Transact SQL :: Overlapping Dates For Same Member Query
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).
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".
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.
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.
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),
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 )
I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN , CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit , pv.TicketNumber , vstatus.Description AS VisitStatus , doc.ListName AS Doctor
I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
I have a Column CCExp Varchar(7) that stores data like below
NULL '01/15' '02/15' '03/15' '04/15' '05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT CustomerBillingInfoID , CCExp FROM CustomerBillingInfo WHERE LEFT( CCExp, 2) < MONTH(GETDATE()) AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2) and CCExp NOT LIKE '#%' and CCExp not like 'NULL'
I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate
id startdate enddate rmd 1 10-oct-15 16-oct-15 11-oct-15 2 11-oct-15 14-oct-15 11-oct-15 3 09-oct-15 11-oct-15 10-oct-15 4 07-oct-15 08-ot-15 07-oct-15 5 if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3
I have a table with 3 columns , let say (PatientID int, AppointmentDate date, PatientName varchar(30))
My source data looks in below way..
PatientID AppointmentDate PatientName 1 01/01/2012 Tom 2 01/10/2012 Sam 3 02/15/2012 John
I need output in below way..
PatientID AppointmentDate PatientName 1 01/01/2012 Tom (actual patient record) null 01/10/2012 Tom null 02/15/2012 Tom null 01/01/2012 Sam 2 01/10/2012 Sam (actual patient record) null 02/15/2012 Sam null 01/01/2012 John null 01/10/2012 John 3 02/15/2012 John (actual patient record)
I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
Create table sample (PatientID int null, AppointmentDate date null, PatientName varchar(30) null)
declare @currentDate datetime,@enddate datetime; select @currentDate = '06/1/2015'; select @enddate='06/05/2015' while @currentDate <= @enddate begin
-- do whatever is needed
select @currentDate = dateadd(DAY,1,@currentDate); select @currentDate as currentdate end
In result am not getting the current date. am getting date from 06/02/2015.i want to get records from 06/1/2015 to 06/05/2015. total 5 records..but now i am getting only 4 records.
i have a table like below CREATE TABLE #Test (FromDate DATE,ToDate DATE) insert into #Test VALUES ('2015-08-08','2015-08-11') insert into #Test VALUES ('2015-08-13','2015-08-16') insert into #Test VALUES ('2015-08-19','2015-08-21') SELECT * from #Test drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE) insert into #Result VALUES ('2015-08-08') insert into #Result VALUES ('2015-08-09') insert into #Result VALUES ('2015-08-10') insert into #Result VALUES ('2015-08-11')
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
Select datediff(dd,Target_Date,Achv_Date) Now , checks are 1] when target date greater than achv_Date the difference should be greater than 0 means for FileID 77608 Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%
We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:
Declare @FromDate as date Declare @ToDate as date Set @FromDate=Convert(date,'09-01-2015',110) Set @ToDate= Convert(Date,'10-13-2015',110) Select @FromDate
[code]...
How can I keep the date as a date and still pass it to stored procedure.
ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATEstring 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE ( InputDate DATE ) GO INSERT INTO TEST_DATE VALUES('01-01-2015') INSERT INTO TEST_DATE VALUES('06-25-2015') INSERT INTO TEST_DATE VALUES('11-23-2014') GO SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then First Quarter End Date would be Previous Quarter End Date Second Quarter End Date would be Current Quarter End Date Third Quarter End Date would be Next Quarter End Date Fourth Quarter End Date would be Next +1 Quarter End Date Like that
I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.
Database: DB2, Customer information housed here Columns: UTOFMM - Month (2 character, numeric) UTOFDD - Day (2 character, numeric) UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc) UTOFCV - Century Value (2 char, numeric. 0 = before 2000, 1 = in or after 2000)
I need to concatenate the date to be "sql" friendly, and then compare to today's date. It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix. This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.
Add Customer ID Update [responder].[Temp_RX_CUSTOMERS] set CustomerID = lf.UTCSID from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join [HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID where lf.UTOFMM = 0 GO
