Finding The Expiration Date
Jul 26, 2006
I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.
Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.
Any ideas?
View 1 Replies
ADVERTISEMENT
Dec 27, 2007
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please. What needs to happen is that the employees need to see only the exclusions that have expired which is based on the Expiration Date.
Code Block
ALTER PROCEDURE [dbo].[SrchIssueDateLstNm_RecntIssueDte]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_LastName nvarchar(10) = null
AS
SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date],
[dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date]
FROM [dbo].[30 Day exclusion]
INNER
JOIN [dbo].[Extended Exclusions]
ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate)
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_LastName is null or [Last Name] = @Enter_LastName)
ORDER BY [Last Name]
View 3 Replies
View Related
Nov 29, 2000
I have a question on how the retain parameter works for Backups. If I have a nightly full backup for a database and have retain = 10, does that mean on the 11th day the very first backup will be purged? Will the 11th backup attempt to take up some of the space that was occupied by that first backup? It seems that the 11th backup would be too large for the space that the first backup was taking up. I am fairly new to backups with SQL Server and want to ensure I am setting this up properly. Our goal is to keep backups for 10 days only.
View 1 Replies
View Related
Jul 24, 2006
Hi There
This may seem like a stupid question but i am trying to get the hang of the new security model.
I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.
We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?
Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?
In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?
Thanx
View 8 Replies
View Related
Jul 15, 2006
First question: I have sql server 2005 enterprise eval edition installed. is there a query, command or tool can tell me the exact expiration date? I understand it is 180 day but I still want some way to query the exact date.
Second question: does sql server log any warning message before eval expires?
View 1 Replies
View Related
Oct 21, 2006
Is there any way I can check the remaining time before I must upgrade
an SQL Server 2000 Evaluation Edition? I thought I had made a note about
which day it was.
Can I rely upon the file date for uninstallation file in Programs/etc ?
View 2 Replies
View Related
Mar 11, 2014
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', GETDATE(), NULL union all
SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all
SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
[Code] ...
Output:
/*
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
2chandu2014-02-112014-03-20
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
*/
Expected output:
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
Looking for query WITH OUT using GROUP BY clause
View 10 Replies
View Related
Jan 24, 2008
We are maintaining a database of drivers, and we are looking for a script or procedure that will automatically notify certain users of the list of drivers who are in need of a recheck or driver's license has expired.
I am new to this , so any help you can give would be greatly appreciated
View 1 Replies
View Related
Oct 29, 2015
I have configured a Linked Server with Active Directory (LDAP), working properly.
Now, through this Linked Server, I need information about password expiration date in my AD Domain accounts.
View 2 Replies
View Related
Jul 22, 2015
We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know
(1)what happens when the user failed to change the password before it's expired or
(2)how a user can change his password in advance of the expiration date with no particular server-level permission.
View 10 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
Oct 21, 2013
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
View 11 Replies
View Related
Oct 25, 2007
Hi;
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
View 11 Replies
View Related
Aug 11, 2006
I have a tutorial in which there is a question to find the person who will be celebrating their Birthday within a week.
The table contains : name, dob, sex
Thanks
Knowledge grows when shared...
View 3 Replies
View Related
Nov 26, 2013
I want to find out the earliest [First_Post_Date] for any parentdid
My query (See below)
Produces the following results
SELECT
ParentID
,[First_Post_Date]
,[FDMSAccountNo]
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878595212886'
Order by ParentID desc
[code]....
View 1 Replies
View Related
May 13, 2014
how I would work out the max value of 3 max date values. Note the max date values may be null.
Here is my code :
SELECT TD.sro_num, SRO.description, SRO.cust_num, custad.name, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status],
SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate, convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate
[code]...
View 2 Replies
View Related
Aug 1, 2014
I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
View 2 Replies
View Related
Sep 18, 2006
Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
View 1 Replies
View Related
Oct 10, 2001
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.
View 1 Replies
View Related
Jan 7, 2014
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
View 3 Replies
View Related
Jul 23, 2005
Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder
View 2 Replies
View Related
Oct 2, 2007
Hi,
I am using SQL Server 2000 and have the following questions:
1. How do I know the last updated (data) date using system objects or any other method?
2. How do I know the last modified date of a table using system objects or any other method?
3. How do I know when a table is last accessed
Any help is appreciated
Thanks for your time and help in advance
Kumar
View 3 Replies
View Related
Apr 14, 2008
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
View 2 Replies
View Related
Apr 19, 1999
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
View 1 Replies
View Related
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
Jul 18, 2014
In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.
I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.
View 8 Replies
View Related
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
Jan 3, 2014
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime
set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';
select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
View 5 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Jul 15, 2004
I am having a pretty major problem with my SQL server. Everytime that I try to query the database I receive a timeout expired error. Even trying to open the query anylyzer, it presents me with that error. We have a we based ISP management solution and it cannot access the SQL either. This was an all the sudden problem and I have ran the performance monitor to try and look at the statistics. Everything seems like it is checking out. I even tried increasing the Timeout up to 20 seconds and it still will not function properly. Any suggestions?
View 1 Replies
View Related
Apr 18, 2008
hi,
here i am using .mdf file as database for my project. i want this .mdf file should be expired or become read only after 365 days exactly. how can i achieve this?
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
View 2 Replies
View Related
May 19, 2006
Is there a document that lists the expiration dates of the CTP releases? We've a test box still on CTP15 and I was wondering when the CTP will stop working.
View 4 Replies
View Related
Feb 14, 2007
Hi,
Description: In the past I had an issue with the replication that suddenly became expired (no reason at all) and I had to re-do the whole thing from scratch. This is a transactional replication (SQL2.5K -> SQL2.5K) and the subscriber is used as warehouse where applications also inserting data (with neg. seed values for the PKs). This means applying the snapshot from the publisher is not applicable because there are data which will be lost from subscriber.
Question: Since the applying snapshot is not an option for reinitializing the subscription (initially was created without snapshot - from the backup and restore is not applicable at this stage) I need to find implementation way for two things:
1. I need subscription never get marked as expired (I did disable job "Expired subscription clean up" but still the replication can be marked as expired from the "Distribution clean up" job which I am not suppose to disable - this has different function as well - cleaning up the delivered transactions)
2. Safely dropping articles from the publication without applying snapshot to the subscriber (see reason above - num.1). If you force to drop the articles, does this mean that the subscription will be marked as expired after period of time? (This again will lead to problems as above).
Please, give me your opinion and/or best practices how to solve these problems. Other than that I am very happy with the improvements in the replication domain in SQL 2005.
Regards,
Zlatko
View 1 Replies
View Related
