SQL Server 2012 :: Calculate Failure Rates Of Systems Based On Cases Opened Through Support
Jun 5, 2014
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
Hey guys want a suggestion from you I am a Masters Student here in USA This forum helped me a lot when I was doing internship.My university is offering tution wavier for these to jobs "1)backup systems support 2)Database admin" .I know MySql also took course for basic database . The question is if any of you please tell me what all I have to do in these job if ...if ... :) I get this job .Thanks based on your answer only I will apply for this job its 20 hrs a week commitment with college assignment .
I have attached some test data for you that has two temp tables "#worker" and "#worker_rate".
The issue is to find all workers who are sharing SAME SET of rate_codes.
I'm able to get the output as "workers sharing same rate_codes", but unfortunately I could not get the list of workers sharing same SET of rate_codes. Also definition of SAME SET is not defined.
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
I have a view saved on server - mhsvi-datawarehousedatawarehouse.This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008ainstance1.When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)
The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.I have access to the database where the view is saved and the database that the TSQL calls.
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number , concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year , cast(fh.transaction_date as date) as transaction_date , sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
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 am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable ( Name varchar(50), GA int, GB int, startdate DATETIME, enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate Pavan 261/1/20151/1/2015 Hema 561/1/20151/1/2015 Surya 501/1/20151/1/2015 Pavan 811/2/20151/8/2015 Hema 311/2/20151/8/2015 Surya 121/2/20151/8/2015 Pavan 1041/9/20151/15/2015 Hema 301/9/20151/15/2015 Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal Total 8 11 5 24 GA 2 5 5 12 GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal Total 9 4 3 16 GA 8 3 1 12 GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal Total 14 3 19 36 GA 10 3 6 19 GB 4 0 13 17
I can fetch the counts for total present and absent
Query i have tried is
Declare @StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458' Begin select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:
Server: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals divide, type equals datetime.
when I run the following query:
SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup
All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into. I have already created a CurrentDate column which has a (getdate()) value attached to it.
This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.
I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
CREATE PROC CLEAR_MY_TABLE @TableStat varchar(30) AS If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null BEGIN ---PRINT 'I FOUND THE TABLE' DECLARE @count INT = -1; SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat); IF (@count > 0) BEGIN TRUNCATE TABLE @TABLESTAT END
I am trying to create a report in SRS that will calculate a Composite Score based on the Sum of other scores and then that Sum is divided by 12.
Here is a copy of the Query I have created to calculate the numbers I want to use, but when I create the expression in SRS the calculations don't come out correctly. The expression that I use in SRS is at the bottom of the Script.
Thanks in Advance
--6 Trait_03052008.sql
--Pull test scores for 6 Trait out of Campus for SRS reports.
SELECT s.lastname+','+' '+s.firstname AS Student, e.grade, sm.lastname+','+' '+sm.firstname AS Teacher, sc.name AS School,
t.name AS Test, t.code, ts.rawscore,
CASE WHEN t.name = '1 Ideas' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric)END AS IComp,
CASE WHEN t.name = '2 Organization' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric) END AS OComp,
CASE WHEN t.name = '3 Voice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS VComp,
CASE WHEN t.name = '4 Word Choice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS WComp,
CASE WHEN t.name = '5 Sentence Fluency' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric) END AS SComp,
CASE WHEN t.name = '6 Conventions' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric)END AS CComp
FROM testscore ts
JOIN student s ON s.personid = ts.personid
JOIN roster r ON r.personid = s.personid
JOIN test t ON t.testid = ts.testid
JOIN calendar ca ON ca.calendarid = s.calendarid
JOIN enrollment e ON e.enrollmentid = s.enrollmentid and e.calendarid = ca.calendarid
JOIN section se ON se.sectionid = r.sectionid
JOIN course c ON c.courseid = se.courseid AND c.calendarid = ca.calendarid
JOIN school sc ON sc.schoolid = s.schoolid
JOIN staffmember sm ON sm.personid = se.teacherpersonid AND sm.schoolid = ca.schoolid
WHERE t.code LIKE '6%' AND c.calendarid =50 AND c.homeroom = 1
GROUP BY s.lastName,s.firstName,e.grade,sm.lastName,sm.firstName,sc.name,t.name,t.code,ts.rawScore
ORDER BY [Student], t.code, t.name DESC
Expression being used in SRS to calculate the Composite Score.
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate!
I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.
Is this an average download rate for a server hosting sql server?
I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
I'm looking for some online resources here. Specifically, I'm interested in finding some case/project examples to learn more. I'm looking for any and all kinds in all areas...ADO, Security, Maintenance, etc. I've worn out google, but the most I seem to find is articles. I'm looking for actual Cases, like one you'd find in a text.
I have a text book from a couple of courses I took in school. Unfortunately it doesn't delve much into said areas. Any resources you could point me I will greatly appreciate it. I'd even be interested in some actual books if there's any that any of you have experience with that you think would help me out. Thanks for reading.
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014 PhaniMarketing NULL (Do not fall in Req Dt) PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec) PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days) Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
