Transact SQL :: Get Start And End Date Of Billing Cycle
Oct 9, 2015
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
View 6 Replies
ADVERTISEMENT
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 Replies
View Related
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
View 4 Replies
View Related
Jul 23, 2005
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)
View 2 Replies
View Related
Jan 14, 2008
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
View 4 Replies
View Related
May 6, 2008
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
View 6 Replies
View Related
Mar 14, 2014
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.
View 9 Replies
View Related
Jul 20, 2005
helloi need to write an sql which calculates # of billing cycles for anorder for which ive order start and end date. the first invoice willbe cut on the order start date. Also if i apply a coupon for lets sayone month (30 days) to that order i need to calculate how many billingcycles that coupon will apply to.ive start and end date of coupon ina diff table for that orderfor eg: if an order was started on 1/15 and ended on 4/14 i have 3billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and3/15)lets say on 3/15 if a users applied one month coupon which means hedidnt have to pay for billing cycle 3/15.can someone help me with this logicthank uml
View 1 Replies
View Related
Aug 4, 2006
Greetings;
I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.
I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.
Is this a possibility? Or am I going about this all wrong?
Thanks
nhas
View 2 Replies
View Related
Apr 10, 2008
My DB has the following description
Table & Columns: ID field is identity. Currency is either CDN or USD.
Order: ID, DateOrder, Currency, AmountOrder
Invoice: ID, NumOrder, DateInvoice, AmountInvoice
Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit
Rate: YYYY, MM, US
Relationship among tables:
Order & Invoice : on Order.ID = Invoice.NumOrder, one to many
Order & Credit: on Order.ID = Credit.NumOrder, one to many
Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many
Other conditions:
For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN.
The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN.
A credit always uses the rate of DateInvoice of its associated invoice.
Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN
Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)]
WHERE the DateInvoice and DateCredit must be in the specified period.
AND associated Order.Devise = USD
SQL:
1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder
3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate
6 AND DateCredit >= startDate AND DateCredit <= endDate
Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.
How can I correct this, please?
View 5 Replies
View Related
Jul 12, 2006
Hi,
Can some participants in this forum provide ideas on suggested training for somebody with a basic knowledge of SQL (started working in this field 6 months ago and still feel like a newbie beginner).
I assume I should focus on getting T-SQL nailed down before delving into the SSIS piece of SQL Server 2005?
Maybe there are a few companies that seem to give good practical knowledge and are located in the greater Los Angeles metro area?
Is it better to get certified through a 1 week or 2 week course via companies like Trainingcamp - does that really give you the practical skills to jump up several levels at one does at work?
Hope I can get some suggestions or direction on where I could best post this question for answers.
Regards,
Patrick
View 3 Replies
View Related
Jul 12, 2006
Hi,
Can some participants in this forum provide ideas on suggested training for somebody with a basic knowledge of SQL (started working in this field 6 months ago and still feel like a newbie beginner).
I assume I should focus on getting T-SQL nailed down before delving into the SSIS piece of SQL Server 2005?
Maybe there are a few companies that seem to give good practical knowledge and are located in the greater Los Angeles metro area?
Is it better to get certified through a 1 week or 2 week course via companies like Trainingcamp - does that really give you the practical skills to jump up several levels at one does at work?
Hope I can get some suggestions or direction on where I could best post this question for answers.
Regards,
Patrick
View 4 Replies
View Related
Jun 14, 2015
Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged. Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period. This is where I am getting stuck. Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.
In trying to solve the problem, I tried to frame a calculated field formula but could not do so. Therefore, I tried solving it via a calculated column formula in the PowerPivot window. My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result. I am getting an error when I write this formula (see Billing data tab of PowerPivot window).
View 11 Replies
View Related
Dec 12, 2007
hai, iam new to ssrs pease help me below calculations.iam taking the parameters as
begin date,Enddate,region,year,site,
My report having outstanding bills information, i need to calculate, here resters are nothing but students, you take any relavent input data.
1)Rosters Outstanding 6-10 Days (Rosters expected, but not received for up to 10 days in the rears),
2)Rosters Outstanding > 30 Days
3)Rosters Current (Rosters expected as of the report end date)
4)Total # Rosters Outstanding (Total rosters expected, but not received)
5)Rosters Queued For Entry Into 3rd Party Systems (Total rosters entered into the SIS System and expected to be entered into, but not already in 3rd party systems)
6)Rosters Entered Into 3rd Party Systems (Total rosters entered into 3rd party systems)
7)Display Total Billable Dollar Amounts Associated With All Outstanding Rosters For All Aging Columns At Bottom Of Report By Daily, Weekly, Monthly, SYTD
thanks
View 1 Replies
View Related
Mar 10, 2005
Quick question (I hope) regarding the cycling of the sql server error logs.
I am looking at implementing a daily / weekly job to run the stored procedure "sp_cycle_errorlog", but was wondering if there was any way of stopping the sql server process cycling the logs automatically when it is started? Therefore the logs will only be cycled when the job runs.
Many thanks.
View 1 Replies
View Related
Apr 30, 2015
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
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
View 5 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
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?
View 9 Replies
View Related
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
Aug 31, 2015
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.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
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?
View 2 Replies
View Related
Jul 18, 2006
Hi all
I have an application which takes start time from a different date.
My actual requirement is "How to take start time someday in august or other month if the current day is in the month".
I will be thankful to any help provided.
Mahathi.
Mahathi
View 3 Replies
View Related
Jun 19, 2007
Hello All,
In my report I need two parameters, StartDate and EndDate to display a certain period of sales for example. How can I enforce the fact that once the user picks a StartDate, the EndDate cannot be prior to StartDate and vice versa?
Thanks.
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
May 27, 2015
I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:
Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END
how could I build this query or what better way of doing the job there are...
View 1 Replies
View Related
Jun 16, 2007
Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Thank you in advance.
View 2 Replies
View Related
Sep 13, 2007
Hi
uh
im on the blag....
Can someone help me with a script for the following?
7am tuesday, week 1 of the year:
full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.
7am Tuesday, week 2 of the year:
exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..
ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.
help me obi-wan SQLee. cos I cant bloody do it....
Thanks
Alastair
Methodology
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
View 6 Replies
View Related
Aug 13, 2015
How to get the date where planned delivery date key is less than getdate()
I want to make a view where I want put the condition :
select CONVERT(datetime, CAST(planned_delivery_date_key AS CHAR(8)), 101) < CONVERT(datetime,getdate(),101)
from qlk_fact_sales_back_order
View 2 Replies
View Related
Nov 12, 2014
I'm looking at the following Records:
cust_no item_no start_dt end_dt price
1060 2931 2011-02-06 9999-12-31 1.23
1060 2931 2011-04-18 9999-12-31 2.00
I want to be able to pull the records with the earliest date 2011-02-06 ...
There were other records with this same customer and item number. I used this script to return the two above.
select *
from price
where end_dt > getdate()
Now I need to add something so it only returns the record with the earliest date. I'm going to run this on a table that has many customer and item combinations.
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
Jun 16, 2007
Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Thank you in advance.
View 3 Replies
View Related
Jan 26, 2007
Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog" once a week to cycle the SQL Server Agent log. Job is owned by "sa".
Most servers run this with no problem, but one active node of an active/active SQL Servers cluster fails with the message:
Executed as user: DOMAINSQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.
The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:
(Date/Time)+ [412] Errorlog has been reinitialized. See previous log for older entries.
If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. Failing over has no effect.
I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)
View 14 Replies
View Related
May 29, 2008
need help on logical SELECT cycle
Code Snippet
SELECT empID, unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, unit_date, GETDATE())) % 4, 0), 4) AS new_unit
FROM dbo.empList
i have this code
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
IT OK
but
if i put
unit_date = 01/04/2008
and unit=1
than i get new_unit=2
but it should be 3
it dont go backward ok
if i put
unit_date = 01/06/2008
and unit=1
than i get new_unit=4
but it should be 2
it dont go forward ok !
TNX
View 1 Replies
View Related
Aug 15, 2006
I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
View 1 Replies
View Related
