The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.
What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:
CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL
I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2 - when user change password it's need to be uniqe and it should not appear last 10 passwords - Each user can have a maximum of 10 lines containing history password table - Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID] , MAX([TimeStart]) [LastRunDate] FROM [dbo].[Catalog] [c] LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID] WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source! group by [c].[Name],[c].[ItemID],[xl].[ReportID] order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT * FROM [dbo].[Catalog] WHERE [Type] NOT IN (1,5) -- Not a folder or a data source! AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1' select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
Is there a way to make a single insert (in a loop) and take system dates and insert them in different languages without making a new loop for each language.
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year FROM EMP_DATE DTE join EMP_INFO EMP on EMP.ID = DTE.ID join YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
I have a table with a list of jobs along with their start and end datetime values.
I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.
Example
Start Date=2014-05-15 12:00:00.000 End Date=2014-05-19 13:00:00.000
Total Time should be: 2 Days, 1 Hour and 0 Minutes
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. */ AS BEGIN
--check if one of the dates is null if @StartDate is null or @EndDate is null RETURN 0
currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like
SELECTCusID(int) , StoreID(int) , SUM(L3M) as Last3Months , SUM(L6M) as Last6Months , SUM(L9M) as Last9Months
[code]...
I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
I've been struggling with this for some time. we have to group data based on Patients admission date and discharge date. If any Patients discharge date + 1 = admission date then we have group both rows into one row and sum costs from both the rows. Please check out the sample input and expected output for details.
I am trying to find out CPU utilization from the history using process.%processor time. I am having dual core CPU with 2 numa nodes each having 16 logical cpus bind to it.
how to calculate the CPU utilization using perfmon.I tried to use SQL query which gives CPU history using SQL DMV, but I am unable to get the exact value. Because in between I have used the same querry to capture my CPU usage on the run day, the value on run day and the query which iam tryting to pull out is different. I am using the same query to pull the history data with providing the date.
-- Get CPU Utilization History (SQL Server 2008 and above)
DECLARE @ts BIGINT SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); SELECT SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization], SystemIdle AS [System_Idle_Process], 100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.
How can I make a copy of an SSRS Report history snaphot to another folder in Report Manager. I have a report that has history snapshots. But want a copy of the report snapshot history in another folder for users to view easier in Report Manager.
I have a date that I get from my now() function that returns:2/10/2004 2:17:16 PMI would like to be able to break this value up. Is there a function thatallows me to pull whatever I want out of this?For example, if I just want the year 2004 pulled out (for a drop downlist I'm creating) what would the function be to do that?And If I just want the day, 10 pulled out, then how would I do that?part of the problem is that depending on the month, is the number ofdigits one counts from the front. 10,11,12 add one digit to the front ofthe string, making this difficult.I'm creating a search between dates so someone can select the day, monthand year as beginning value, and the end value. So how would I go aboutdisecting this string?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
declare @t table ( segment int, subsegment int, primary key (segment,subsegment) ) insert @t select 1,33 union all select 1,22 union all select 2,33 union all select 2,22 union all select 3,33 union all select 3,22 union all select 3,44
What I want is to find all segments that are in some sense complete duplicates of other segments. a segment is made up of subsegments. a subsegment is not a segment - it's a completely different entity. this table is not hierarchical.
So in the sample data above, segments 1 and 2 are dupes because they share exactly the same subsegments: 22 and 33. Segment 3 is not a dupe because it has a third subsegment the other two don't have: 44.
when a duped segment is found, I need to know which other segment it duplicates. so an acceptable result set for the above sample data would be:
segment partner ------- ------- 1 2
this would also be fine:
segment partner ------- ------- 1 2 2 1
ps: i already posted this on dbforums - just broadening the audience a little.
varchar(MAX) as 'MSG|John|J|Smith EVN|2008-02-01|A03 ADD|101 Highland St|Mount Vernon|WA|55231 OBS|Flu|Severe OBS|Mumps|Mild'
To explain this more thoroughly, the data is organized into segments: MSG, EVN, ADD, and OBS.
Each message may have 1+ of each of these segments.
What I'd like to do is pull the contents of all OBS segments and put them into a table.
I know I can identify them using the regex of ' OBS|' but I'm not clear on how to capture anything but the first one easily. Programmatically, it's simple. SQL I'm not so sure.
Can anyone give me a gentle nudge in the right direction?
I am designing a database to store data from a leak tester.
We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.
ID (int) RESULT (float) TS (datetime)
The query will be, of course:
SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2
This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.
Using a index with a datetime field sounds like a crazy idea.
SELECT SUM(((CASE WHEN o.date>= a.activity_date, other filter condition, other filter condition THEN (select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date' else end ))
from emailtable a LEFT JOIN opportunity o left join drawtable d left join drawtable d2 etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
