# How To Calculate The Total Days Between Open And Close Date

Apr 6, 2006

Hi All,

I have a table call case and case_status have two fields, date and status as below:

date                          status

04/01/2006               open

04/05/2006               closed

04/10/2006               open

04/15/2006               closed

Whenever i open and closed the case, one record is insert into the case_status table.

Now I would need to calculate the total days of the case in storeprocedure.

Aung

## 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.

## 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.

## 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.

## 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

## T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.

[URL]

## 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] ......

## Case Statement - Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

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.

## SQL 2012 :: Produce Report To Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

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.

## Leave The Connection Open Or Always Open And Close Immediately?

Jun 7, 2006

Hi there,

I got an approach like that:
1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc.
Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that?
The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2).
Thanks for the input!

## Trying To Attach Database In VS2005 And All DB Files Say They Are Being Used And To Close...they Are NOT Open.

Apr 13, 2008

Just to verify that this was an issue, I downloaded web developer 2008 and I do not experience this same problem.

BUT when I go to add a dataset in vs2005 for an asp website - all my db files come up in the dialogue box but everyone that click (every db file) I get "This file is in use. Please enter a new name or close the file that's open in another program."

But, like I said, I downloaded 2008 and it does not occur. Plus I KNOW that the db's are not being used. Can someone give me a remedy to this?

## 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

## 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 ???

## 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?

## Keep A Few Connections Open All The Time Or Open/close Connections On The Fly?

Jul 20, 2005

Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?

## 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.

## 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.

## 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?

## 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

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
)

## How To Add 7% For The Total And Calculate VAT

Aug 22, 2007

Hi

I am using the below code in sql procedure.How do I add 7% of the price and then VAT for the total.

CAST(CAST(cast(Price * 1.175 as decimal(19, 2)) as INT) + 0.99 AS DECIMAL(19, 2)) as [item-price],'

## 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

## 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

## 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 ...

## 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.

## 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?

## How To Calculate Only Total One Column

Jul 18, 2014

I want calculate to my total column sum

ex- col 1 col 2 col 3 col 4
raj 10 20 20
pani 20 88 88
tiger 87 78 89
john 77 77 77
---------
??
----------

## 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

## Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

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;

## How Do I Calculate Current Total Bytes Per Row Used

Feb 16, 2001

We are close to exceeding the Maximum Row size of 8K per row and the CIO asked me "how close are we to reaching the 8K row limit?"

I began by looking at byte requirements for the data types we are using
from the systypes table then I wondered, do the indecie, Foreign Keys and Primary Keys use bytes which must be included in my Calculation for current number of bytes used per Row.

Question: How do I calculate Current total number of bytes used per row.

## Calculate Total Hours / Minutes?

Jul 5, 2014

need to calculate the total hours mintues in sql server and asp.net

## Calculate Grand Total Using Pivot

Apr 25, 2014

How to calculate grand total in sql server using pivot?

## Transact SQL :: Calculate Total Between Two Columns?

Sep 16, 2015

I have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.

CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);

And i have some data:

INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)

I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):

SinGroup C1 C2

y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50

Z 50 0

And also result like this (c1-c2 and grop by each row,the result show under the column 1):

SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0

## Reporting Services :: How To Calculate TOP N Total

Oct 23, 2015

I have a SSRS report. I am displaying TOP 10 Stores by sales by creating a filter on group Store. It has 3 row groups (Store, brand, product).

There is no column group. In each cell, I am using sum function in expression (Sum(Fields!TotalSales.Value).

I am adding Totals by going on Group properties, but I am getting totals of all the rows not the total of all the groups within my Store level (which is my top Parent group).

Store     Total sales(CW)  TotalSales(LW)
A             2                            1      -expression used to calculated this value is sum(Fields!TotalSales.Value)
B             3                             2
C             7                             3

I want subtotal =12 AND 6 under respective columns . ALSO, my store (A, B, C) can be expanded into Brands and brands into Products. I tried using running value but its not working as I have 3 groups within Store row parent group and I am doing a SUM on each group.