Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output
id Startdate
1 2014-10-01
2 2014-01-01
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth) select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:
Customer StartDate EndDate A 4/1/04 4/15/04 A 4/15/04 5/1/04 A 5/1/04 5/15/04 A 5/16/04 5/28/04 A 5/28/04 6/5/04 B 5/1/04 5/15/04 B 5/16/04 5/20/04
The results I am looking for would be as follows:
Customer A : Outstanding 4/1/04 - 5/15/04 Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string ) Customer B : OUtstanding 5/1/04 - 5/15/04 Customer B : Outstanding 5/16/04 - 5/20/04
I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
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?
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code: SELECT tblinmate.statusid, tblinmate.activedate, Last(tblclassificationhistory.classificationid) AS LastOfclassificationID, Last(tblsquadhistory.squadid) AS LastOfsquadID, tblperson.firstname, tblperson.middlename, tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code: SELECT a.inmateID, a.classificationID, b.max_date FROM ( SELECT tblClassificationHistory.inmateID, tblClassificationHistory.classificationID,
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 a 100 7-7-15 a 100 8-7-15 b 200 7-7-15 b 200 8-7-15 b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 50 a 100 7-7-15 50 a 100 8-7-15 100 b 200 7-7-15 200 b 200 8-7-15 100 b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) : SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3, CASE WHEN TRAPR3 IS NULL THEN (CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) RETURNS date AS BEGIN; DECLARE @Result date; SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) RETURN @Result; END;
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)
need help help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008' i explain i have stord prosege that create mod cycle shift pattern and it working ok now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1 so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008
tnx
Code Block DECLARE @shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL) declare @I int set @i=0 while @i < 5 BEGIN INSERT INTO @shifts_pattern ([patternShiftValue] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 set @i=@i+1 end declare @empList TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO @empList ([empID], [ShiftType],[StartDate],[EndDate]) SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103) -- create shifts table declare @empShifts TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) DECLARE @StartDate datetime DECLARE @EndDate datetime Declare @current datetime DEclare @last_shift_id int Declare @input_empID int ----------------- open list table for emp with curser DECLARE List_of_emp CURSOR FOR SELECT emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp OPEN List_of_emp FETCH List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate SET @current = @StartDate ----------------- -- loop on all emp in the list while @@Fetch_Status = 0 begin -- loop to insert info of emp shifts while @current<=@EndDate begin INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate]) select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate from @shifts_pattern as shift where PatternId=@last_shift_id+1 -- if it is Friday and we are on one of the first shift we don't move to next shift type . if (DATENAME(dw ,@current) = 'Friday' ) and EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) -- do nothing --set @last_shift_id=@last_shift_id print ('friday first shift') ELSE set @last_shift_id=@last_shift_id+ 1 set @current=DATEADD( d,1, @current) end FETCH List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate -- init of start date for the next emp set @current = @StartDate end CLOSE List_of_emp DEALLOCATE List_of_emp select empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift RETURN
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
hi, I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database.. right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables.. Thanks Karen
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------ Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19 Select statements included within a function cannot return data to a client. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20 Invalid column name 'WorkDay'. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22 Invalid column name 'Date'.
------ my function code ---------------------------- CREATE FUNCTION [dbo].[FGetWorkDate]( @WorkDays VARCHAR(5), @DateFromValue AS DateTime ) RETURNS DATETIME
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
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.
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
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).
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -
