MS SQL Server :: T-SQL (SS2K8) :: Calculating Total Outage Time

T-SQL (SS2K8) :: Finding Total Execution Time?

Oct 30, 2014

I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.

i planned to test it as follows

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

----- for the SP that is before optimize

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

View 9 Replies View Related

Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,

I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.

I kindly request for help.

Thanx in advance.

Ronnie

View 4 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Calculating Whole School Total Tuition

Jun 12, 2004

I am trying to calculate a whole school total tuition. I've got helped with individual tuition total which is

create proc sp_individualtuition
as
select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition
from contract c, contractDiscount cd, discount d
where c.contractNum = cd.contractNum
and cd.discountNum =d.discountNum
group by c.contractNum, c.tuition

however how can i calculate the whole school total tuition (adding all the individual total tuition)?

my contract, contractDiscount and discount DDL are down below

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null
);

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_contractnum
foreign key (contractNum)
references contract(contractNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_discountnum
foreign key (discountNum)
references discount(discountNum)

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null
);

thanks
gazawaymy

View 1 Replies View Related

Calculating Total Events (query)

Jan 23, 2008

Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:

select count(defendanteventpros.vbkey) as countcase from defendanteventpros
where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007')
having count(defendanteventpros.vbkey)>4

The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).

What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.

I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!

View 7 Replies View Related

Calculating Grand Total And Percentage

Oct 19, 2007

Hi All,

I have to create a table like this using SSRS.

LoanStatus NoOfLoans Amouunt Percentage

Started
1,000
1,000,000.00
100%

Sent to Partner
350
350,000.00
35%

Denied by Partner
50
50,000.00
5%

Accepted by Partner
300
30,000.00
30%

Loan Apps Pnd Disb
10
100,000.00
1%

Loan Apps Disbursed
290
290,000.00
29%

Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.

Please can anyone tell me how to calculate the grand total and Percentages?

View 5 Replies View Related

Reporting Services :: Calculating Percentage Of Column Total In Charts

Jul 22, 2015

Calculating % of Column Total in Charts...

Doing this exercise in Excel is always very simple:

View 3 Replies View Related

T-SQL (SS2K8) :: Calculating Average For Each Record

Jul 4, 2014

How to calculate Average sal foe below scenario.

I am having tables with 12 columns as jan,feb,.......dec.

Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.

For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.

View 5 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!

Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

T-SQL (SS2K8) :: Add Total Row For Each Name

Aug 29, 2015

I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this

Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here

but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed

Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)

Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')

View 6 Replies View Related

Help With Calculating Duration Time

Oct 14, 2005

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?

View 4 Replies View Related

Calculating Time Between Each Record?

Dec 4, 2013

Code:
CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,

[Code]....

-- not accurate b/c it returns 0 for all TimePerPageSec
SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
FROM DigiTracker
GROUP BY contact_id, ZinePageNumber
time spent (in seconds) between each record

View 9 Replies View Related

Calculating Duration Time

Dec 13, 2007

View 2 Replies View Related

Calculating Time Difference

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help

View 2 Replies View Related

I Need Help In Calculating Time Duration

Dec 13, 2007

View 6 Replies View Related

T-SQL (SS2K8) :: How To Add Up Monthly Total

Mar 7, 2014

Here is example table and data

CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)

[code]...

How can I create a query to get below result?

YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340

[code]...

View 6 Replies View Related

T-SQL (SS2K8) :: Query To Get Total At The End

Sep 15, 2014

select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1

The results yields

33915
3405
3412

I am trying to get the following result but can't figure out how to get the total in the end.

33915
3405
3412
Total 22

View 2 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Calculating The Time Duration For Each Event

Jun 6, 2008

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:

order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12

It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:

From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days

I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

View 4 Replies View Related

Calculating Query Running Time...

Dec 19, 2007

Hi all
As you know when you run a piece of TSQL scrypt in Query Analizer
at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time
from SQL-Server environment and use it in the Front-End Application
to inform the user?

Kind regards.

View 2 Replies View Related

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.

View 4 Replies View Related

Transact SQL :: Calculating Transaction Time

Nov 23, 2015

select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log]
order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23
12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --

difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show Total Sales

Mar 10, 2014

My Table struct

create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float

[code]....

like i wanna display each pincodes how to make a code?

View 6 Replies View Related

Calculating Time Between Two Date In Sql Server 2005

Mar 17, 2008

Calculating time between two date In Sql Server 2005help me ..

View 3 Replies View Related

Calculating Time Differenc Between Text Fields

Feb 8, 2007

I am using MS SQL Server 2000.
I have 4 text fields
1. event begin date
2. event begin time
3. event end date
4. event end time
I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time � The event begin date and event end date are char(8) & event begin time and event end time are char(4) � with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time.
If the values of the 4 text fields are
event begin date = �20070207�, event begin time = �2015�, event end date = �20070208�, event end time = �0105�, the difference should be 0 month 0 day 4 hours 50 minutes.
Can somebody please help with MS SQL Server 2000 syntax?

View 1 Replies View Related

SQL 2012 :: Calculating Elapsed Time Between Records?

Oct 29, 2015

I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,

emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL

The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.

View 9 Replies View Related

Calculating Time Difference In Msdb..sysjobhistory

Dec 28, 2006

I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.

select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.

The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.

run_date run_time

2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)

Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.

View 5 Replies View Related

Calculating Length Of Time A Ticket Was Suspended

Mar 7, 2007

I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").

The data is stored something similar to the following...

Ticket Date Status
------ ---- ------
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"

This is the result i'd like to see....

Ticket Start Hold End Hold Time Suspended
------ ---------- -------- --------------
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...

Has anybody done something along the same lines? If so, could you point me in the right direction?

Thanks in anticipation

Jon

View 6 Replies View Related

Transact SQL :: Calculating Date And Time For 3rd Shift

Jul 13, 2015

We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.

Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)

[code]...

View 3 Replies View Related

Calculating The Time Required To Update Statistics

Mar 6, 2008

We have a proc that runs nightly which updates stats. For each database it will run dbcc updateusage on each table, then then sp_updatestats on the database itself.

This process normally takes about an hour, but recently the time has gone up dramatically.

I am looking at ways of making the run faster. One suggestion was to split the work and have it run in parrallel, with multiple procs all doing the same job but on different databases. So for example, I could have a proc that would determine which databases would take the longest to process, and then call 3 different copies of our update proc, each getting a different set of databases to process.

If I went this route, how could I determine which databases would take the longest to complete this process? I'm investigating determining this from sysindexes' rowmodctr, or maybe just database size?

Any suggestions on how I could get an estimate of which dbs would be the most costly to update stats? This is for SQL Server 2000.

Thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Preserving The Total Field Length?

Jun 24, 2014

I have a field Char(10) named bank, and another one Char(15) named namebank.

I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.

B001 Bank 123

How can accomplish this query.

View 6 Replies View Related