How To Calculate Average Number Of Days Taken

Jan 16, 2014

How to calculate the overall average number of days taken to complete something.

The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).

Each enquiry has a enquiry_number

Sample data typically looks like:

Enquiry number - enquiry_time - complete date
1 - 01/01/2014 - 12/01/2014
2 - 01/01/2014 - 11/01/2014
3 - 01/01/2014 - 10/01/2014
4 - 01/01/2014 - 07/01/2014
5 - 01/01/2014 - 12/01/2014
6 - 01/01/2014 - 04/01/2014

etc.

What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?

View 2 Replies


ADVERTISEMENT

How To Get Average Number Of Days Between Orders

Nov 5, 2013

SQL 2012

I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.

SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders)
Group BY custId

View 5 Replies View Related

T-SQL (SS2K8) :: Average Number Of Days Between Orders

Apr 7, 2015

I need to calculate the average number of days between customer orders.

My table has customer number and order number and the order date.

I would like to end with customer number last, order date and the average number of days between orders for each customer.

cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015

[Code] .....

View 9 Replies View Related

Calculate Number Of Days

Aug 28, 2004

I need to add days to a date field, my date field is as varchar(20041030 for example) and I need to add 4 days to it, my result should be 20041103, result field is also in varchar,how would I do that, can anyone pls help?

thx in advance!!

View 2 Replies View Related

SQL 2012 :: Calculate (Number Of Days To Pay) Logic

Jun 24, 2014

How to write a query that calculate the number of days (approx) that it takes to a customer to pay an invoice.

Document Types

1 Payment
2 Invoice
3 Credit Memo
4 Finance Charge Memo
5 Reminder
6 Refund

There are three methods to consider when calculating the days to pay logic.

Method 1 - Simple : Look for Document Type 2 (Invoice), if "closed at date" > "posting date" then number of days = ("closed at date" - "posting date")

Method 2 - A Document Type 1 (payment) closes a Document Type 2 (Invoice)
For this method the formula would be: Payment Record (1) "posting date" - Invoice Record (2) "posting date"

Method 3 - An Invoice closes the payment.

On a payment entry “closed by entry no.” refers to an Invoice entry.

a. In our code we are not on the payment looking for the invoice, we are on the invoice.
i. Because of this we need to find the entry that our current invoiced has closed.

I am taking this from a page that has the pascal code that I need to translate to SQL.

IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
NOT CustLedgEntry2.Open
THEN
IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")

[Code] ....

I am also including create table and insert data scripts ...

View 9 Replies View Related

SQL Server 2008 :: Calculate Number Of Days

Oct 8, 2015

I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .

CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016

for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013

then AVG them.

View 3 Replies View Related

DATEDIFF Case Statement - Calculate Number Of Days

Feb 14, 2014

Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]

This is my query so far

SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Which produces the following

CreatedDate2012-12-17
StageNameLive Transactions
CloseDate2012-12-31
Age424

When in fact the age should be 14days

View 7 Replies View Related

Calculate Number Of Working Days Back From A Date

May 6, 2008

Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime

SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)

-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

View 3 Replies View Related

Power Pivot :: Calculate Number Of Business Days Between Two Dates

May 14, 2015

I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using  the following query

    DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
    CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
    CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END 

I am looking for a similar query in Power Pivot.

View 2 Replies View Related

SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year

Jul 29, 2015

Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.

They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.

Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.

Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?

I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.

View 8 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

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.

[Code] ......

View 4 Replies View Related

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

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.

View 2 Replies View Related

Calculate Average Within A Query

Nov 29, 2013

I am trying to calculate an average within a query:

coalesce(field1, 0) + coalesce(field2], 0) + coalesce(field3, 0)/Count [AVG]

It seems to be subtracting the value of field3 from the total of 3 fields at some points instead of giving me the average.Some of the results are here:

Count Field1 Field2 Field3 Total Average
===== ====== ====== ====== ===== =======
2NULL6NULL66
11NULL28134129
10NULL33NULL3333
3NULL12NULL1212

[code]....

View 4 Replies View Related

How To Calculate Average Of Fields That Are Not Zero

May 27, 2015

I have a few columns: Week1, Week2, Week3, Week4, Week5

i.e. 
Week1 = 5
Week2 = 0
Week3 = 10
Week4 = 7
Week5 = 0

How do I calculate the average based on the fields that are not zero?

Incorrect:
(5+0+10+7+0)/5 = 4.4
Correct:
(5+0+10+7+0)/3 = 7.3

How to let create a custom column to let the SQL know that I only want to divide by 3 instead of 5?

View 7 Replies View Related

How To Calculate Average Row Size Of A Record.

Apr 22, 2008

 I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table. Here is my table structureCREATE TABLE patient_procedure(    proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY,    patient_id int NULL,    surgeon_name varchar(40) NOT NULL,    proc_name varchar(20) ,    part_name varchar(30),    wth_contrast int ,    wthout_contrast int ,    wth_wthout_contrast int,    xray_part varchar(60),    arth_area varchar(30),    others varchar(30) ,    cpt varchar(20) ,    procedure_date smalldatetime NOT NULL,    mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id)    REFERENCES dbo.patient_information (Patient_id)) Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.Here the problem is when the user enters these two procedures information remaining fields will store the  null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. Waiting for valuable suggestions. 

View 2 Replies View Related

Calculate A Variable Average In SQL Server?

Jun 11, 2007

ok so i have this table:


Code:


Products_Sold
priceSold - money
itemsSold - int


An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81

to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)

But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?

in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100

but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200

and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300

but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.

I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye

View 1 Replies View Related

How Do I Calculate Average Leadtime In Sqlserver...

Sep 2, 2007

Hi,
How do I Calculate Average Leadtime...
I have a Table named "iCalls_Calls" which has 2 Columns (start_Date and Closed_Date).I need to calculate average leadtime based on the columns from this table . I have a query and i need to add this ( calculate average leadtime) to this query.


Code:

SELECT B.USER_DIVISION,B.USER_DEPARTMENT,COUNT(*) FROM iCalls_Calls A INNER JOIN iCalls_Users B on A.REQUESTOR = B.USER_ID
GROUP BY USER_DIVISION,USER_DEPARTMENT



Can anyone send me the correct query to calculate the average time ?
Thanks..

View 14 Replies View Related

Calculate Average Analysis Servives

Aug 6, 2004

Hi this might be pretty simple to you guys out there but i am having issues doing it.

Please help!!!!!!!!!!

Calculate Average of a measure called DIST irrespective of any dimension the page field.

Ie. to say it should calculate the average for any doension i bring on the rwo field.

Looking forward to your help.


you can mail me at hem_k01@yahoo.com

View 6 Replies View Related

Calculate Average Growth Rate

Apr 30, 2008

I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

Now, I'm trying to figure out what the fastest growers are. Here's the table ddl

CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO


What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

what am I missing here?

View 10 Replies View Related

How Do I Calculate The Average Variable Length For A Varchar?

Feb 26, 2008

im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars

Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)

i'm suppose to find the average length for them?

i'm suppose to use that to add up to my ROW SIZE

and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array

please help me asap before 2morrow night.
Thanks!
i have a test

View 2 Replies View Related

Calculate Rolling Average Cost From Two Dataset

Sep 11, 2013

I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.

Dataset 1:

SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(sum(AmountPaid),0)As [Amount Paid]
FROM factAdmissions

[Code] ....

Dataset2:

Select

(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'

[Code] ....

View 20 Replies View Related

Calculate Sum And Average - Patient Information For All Nurses

Oct 8, 2013

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patient information for all the Nurses.

I need to calculate Number of patients per Nurse and Average number of patients per day per nurse.

We need to calculate Average Number of patients per day per nurse = Total Patients per nurse/No.of unique days they worked

I need my report as like this.

DistinctNurse No.ofPatients AvgNo.ofpatients PerDay
Tina 100 25
Sony 50 16.6

How to get the result as above.

View 11 Replies View Related

Transact SQL :: Calculate Average Based On A Scenario

Oct 30, 2015

Col1                      Col2       Col3          Col4
54763.00              21           0              0             
--Row1
59574.00              23           0              0             
--Row2
64085.00              20           0              0             
--Row3
0.0               0.00      0           0              0             
--Row4

I'm trying to calculate Average of Col1 of above table based on below scenario:

CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3

ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4

View 7 Replies View Related

Calculate No Of Days

Apr 25, 2008

Hi,
I want to get the no of saturdays that are coming between given span of days,

ex:- if i give dates as 04/05/08 and 04/25/08 , i want to get the no of saturdays between these dates

can anyone help

View 3 Replies View Related

How Can I Calculate The Days Past Due

Apr 20, 2008

If I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause?
WHERE tblInvoices.InvoiceDate ???

View 1 Replies View Related

Query To Calculate Network Days

Apr 15, 2010

I have a requirement where i need to calculate the age of a work order excluding the Weekends (Sat,Sun) in an SQL table, this i need to updated as a formula for a particulay column in the SQl table so when a task startdate is enterred and submitted the Age field gets populated with the number of working days.

Example:
startdate = '04/09/2010'
currentdate = '04/12/2010'
the result should show 1 day and not 3 days.

View 20 Replies View Related

How To Calculate Difference Between Getdate And T1 How Many Days

Oct 10, 2013

i have a table

a1
-----
id t1

How can I calculate the difference between getdate() and t1 how many days.

View 3 Replies View Related

Calculate The SUM For 1 Month & 5 Days -display

Dec 18, 2007

Hi All,

I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions-

I have a Table1 which contains
C1-acountid
C2-date
C3-grossamount(postivie and negative decimal values)
C4-netamount

Table2
C1-groupid
C2-accountid

Table 3
C1-groupid
C2-groupname


I need create a store procedure to retrieve the following on a single table

1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day
NOTE:These 10 AccountIDs may be sam or differing each day
2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month.
NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date.
3.Sum of Netamount for the last 5 days for each accountids in STEP1


The result set must contain the columns as below

C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the month
C4-Sum of Net for last 5 days
C5-groupname


Please help me.

Below is the script that i have written, without calculating the sum

(
select top 10 a.date, a.accountid, a.gross, a.net, c.groupname
from GBSys_Sum_EOD a

join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id
join server2.dbname.dbo.Table3 c on b.groupid=c.groupid

where date> getdate()-1
and gross< (floor(-00.00)) order by gross
)

Thanks in advance.

View 2 Replies View Related

How To Calculate A Date Difference In Days

Apr 2, 2007

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

View 4 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 3 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 10 Replies View Related

Calculate Total Working Hours Between Days

Nov 15, 2013

I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:

Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.

View 3 Replies View Related

Calculate Contract Days In Financial Year

Feb 4, 2015

I have a table with 3 fields: Contract_No, Start_Date, End_Date. I need to calculate how many of the days between the Start and End dates fall within each financial year (FY) beginning 1st April and ending 31st March.

So for example if the Contract Start_Date is 26/01/2012 and the End_Date is 20/05/2012 or is null then the number of days for 2012 FY is 49 (counting from 01/04/2012 to 20/05/2012).

If the End_Date is null for the same contract, then the number of days for 2012 FY would be 365.

Since the contract period from Start_Date to End_Date might span more than one or even several FY I need to be able to show this in columns seperately for each FY. What is the best way of achieving this?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved