Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime )
INSERT INTO @EventLog
SELECT '100','Inplace','01/05/2015 05:00' UNION ALL
SELECT '100','Removed','03/05/2015 10:00' UNION ALL
SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance 201501 201502 201503 TOTAL Number of events 8 10 10 28 Time 5 5 4 14 Ratio 8/5 10/5 10/4 28/14
I have a table that holds log event records, that keep getting appended. I need to get the duration of the each event 0 which has other events before and after. How can I do this - to get event duration of event 0 and the cumulative.
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.
How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK") FROM ruleResults;
For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?
I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.
I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.
SELECT '0' As RN ,memrepay.lnr ,'Repayment' AS Type ,SYSDATETIME() AS NOW ,SUM(memrepay.mprinc) As PrincPaid ,SUM(memrepay.mint) As IntPaid
[Code] .....
How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?
table A-NewParts PartNumber Description LaunchDate a123 product a 4/1/2009
[code].....
I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.
PartNumber Description LaunchDate Yr1 Sales Total Yr1 Sales Yr2 Sales Total Yr2 Sales a123
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd Test Inside Property2009-08-29 2010-07-31 Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.For example)...Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.
In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...
I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user only if he makes the 3 incorrect entries within 30 minutes.
I created a field named "FirstEntryTime" of type datetime that saves the date of the first incorrect entry. I tried to make an if statement:
if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30) Begin Update myTable set ... End
Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??
select building_number as SchoolID,building_name as Campus, count( distinct( CASE WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed from Sales sl join Buildings b on sl.Building_Num =b.Building_number join students2 s on s.Student_Number= sl.Student_Num join Sale_Items SI on si.UID = sl.UID where CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and '2015-05-07' and VoidReview <> 'v' and SI.INum = '1' group by building_number,building_name order by building_number,Building_Name;
I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.
How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.
Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2)) Insert into #sum values (121, 2000,'HLMO',98,'DT') Insert into #sum values (122, 2000,'HLMT',2,'DT') Insert into #sum values (123, 2001,'HLMO',100,'DT') Insert into #sum values (124, 2002,'HLMD',97,'DT')
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2 --@X at starting point is 15000 -- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1) -- 2 1000.00 0 --@X IS 9000 = 10000 - 1000 -- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000 -- 4 12000.00 12000.00 -- 5 300.00 300.00 -- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order. -- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
What we are trying to do is watch a table in one database for an insert. If something is inserted into a unique ID column, we want to go to another database, on the same server, and look for that same number and place a value in another column for that record. Any ideas?
I'd like to build an application that will react to specific changes todata in a set of tables in a database. The application would replicatethese data changes to another database. The target database won't beSQLServer. Neither is this simple replication, at times the applicationwill need to get extra data from the source database before the targetis updated.In other DBMS systems I am involved with the DBMS has the facilty towrite to an application message queue so that the monitoring applicationonly has to monitor the queue rather than a database. What I'd like todo is something like this:1. Some application changes data in a table.2. A trigger reacts to the change and writes a message to an applicationqueue.3. A windows service/process monitors the queue and picks up themessage. It then carries out whatever replication/DB actions are necessary.This would mean defining a number of new triggers on existing tables anddeveloping the windows service/process. Existing applications and theexisting tables in the database would remain unchanged. I'm not awindows programmer but I have someone in my team who is and who willbuild the windows service/process.The bit I'm unsure about is how a trigger can write to an applicationqueue or communicate with the windows service/process. I may be usingthe wrong terminology as I have more knowledge of Unix than Windows.Could anyone help with how I can do this or suggest any alternativestrategies.Thanks In Advance.Laurence
Just finishing of a large ETL system and have aquestion about the following:-
We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?
My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something
I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...
Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?
I would like to know how I can get hold the text for a service and event notifications. What I need is the ability to get hold of a list of the events I'm currently monitoring.
i.e.
Server or database level
I though I would be able to get hold of this in sys.server_event_notifications but it doesn't seem to have any data although my server level event notifcation service is running.
