I have a Hospital Utilization Report and my Client wants me to calculate the number of days.
Example
Date of Services from 1/1/08 - Date of Service Thru 01-05-2008. Which the total will be 5 days.
How would I calculate that?
Hi all - am developing my client statement report on my accounts application...
My accounts team need the ability to print off monthly statements for clients. I have done this no probs. I end up with a table of invoices (invoiceid, invoicedate, amount) in my report table. At the bottom of this table, they require the following table:
Current: £amount
30-60 Days: £amount
60-90 Days: £amount
90 + Days: £amount
basically, Say I print the report on 15th Feb. The Current value should be a sum of invoice totals from February. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 90 Days should be a sum of invoices raised before Oct.
I'm assuming I grab the current month from Globals!ExecutionTime but where would I go from there?
Thanks in advance as usual guys :)
Stephen.
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.
View 7 Replies View RelatedHow would I do a date range from the first day of this year to the getdate(), and so it will change once the new year hits as well?
View 6 Replies View RelatedI'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
i have id, start_date, end_date
View 2 Replies View RelatedIs there any easy way to calulate number of business days between the 2 dates ?
I want to exclude all sat and sun between the 2 dates.
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!!
select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'
Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria.
I can't seem to be able to get the datediff function right in this particular example
Hiwhen inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!
View 7 Replies View RelatedHello,
I have a table with 3 columns: Item# | Date | ItemAmount.
Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).
What I want to do is to :
retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).
Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.
Does anyone have an idea.
thanks in advance,
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
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?
I have a table like this (a small section of the table)
Cu_id |Tr_id |Date
1234 |1 |12/3/2006
1234 |2 |12/18/2006
1234 |3 |1/5/2007
1234 |4 |1/9/2007
1234 |5 |2/21/2007
9999 |91 | 1/3/2006
9999 |81 |1/10/2006
9999 |71 |1/18/2007
9999 |61 |2/1/2007
I have to find the number of days between the dates for the same cu_id and add the number as a new column. The new table should look like this.
Cu_id |Tr_id |Date| Days_between
1234 |1 |12/3/2006 |0
1234 |2 |12/18/2006 |15
1234 |3 |1/5/2007 |18
1234 |4 |1/9/2007 |4
1234 |5 |2/21/2007 | 43
9999 |91 |1/3/2006 |0
9999 |81 |1/10/2006 |7
9999 |71 |1/18/2007 |8
9999 |61 |2/1/2007 |14
Please let me know how I can find the number of days between two dates in the same column for the same cu_id (customer_id).
Thanks
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
As always, your help is very much appreciated!
I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.
Thanks in advance
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
I need to find out the count of number of records older than 100 days from a table having 'order_date' as yyyymmdd format eg. 20041115. Thanks in advance.
View 9 Replies View RelatedHow 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 ...
--From the rows I want to know how many number of days a person was active for the given date range.
create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values
[Code] ....
--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days
2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days
3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days
4) FromDt = '2013-01-01' ToDt ='2014-12-31'
KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/
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] .....
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.
I'm stuck on converting a datetime field to Int. Basically, one of the fields in the select returns the datetime of a journey. However I need to be able to add up the number of days for a specific person who done that journey, e.g. it will return 21/08/2014, 22/08/2014 etc...
I then need to total up the days as a new field to display the number of days.
Can you do this by converting a datetime to int then use COUNT on the int?
Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.
can anyone say the solution?
thanks.
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
Something like below,
DateTD Days left
2009-01-05 14
2009-01-06 13
2009-01-07 12
2009-01-08 11
2009-01-09 10
2009-01-12 9
2009-01-13 8
2009-01-14 7
2009-01-15 6
2009-01-16 5
2009-01-19 4
2009-01-20 3
2009-01-21 2
2009-01-22 1
2009-01-23 0
2009-02-02 /
2009-02-03 /
I am new to sql server query. I am trying to write a query for an application and I want user to enter number of over due days for payment.Below is my query and I am getting an error: An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.
SELECT (P.FirstName+', '+P.LastName) As PA, Px.PRespDate,Px.DateFrom
WHERE Px.Hide = 0 AND Px.Total <> Px.Payments AND Px.PRespDate IS NOT NULL AND PE.Hide = 0 AND PE.BillReady = 1
AND DATEDIFF(DAY, Px.DateFrom, PRespDate) LIKE 'Enter # of days |%% '
Group By (P.FirstName+', '+P.LastName), Px.PRespDate,Px.DateFrom
I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.
View 2 Replies View RelatedAim – 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
DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth
View 1 Replies View RelatedI want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
We have a stored procedure which currently accepts the following parameter: exec dbo.PurgeOldData <'yyyy-mm-dd'...We want to schedule this sp to run once a (first day of the month) going back to say today's date (or the date when this sp is called to run) minus 365 or purging 12 months data. Basically, the function should convert the today's date minus xxx number of days to yyyy-mm-dd so that the sp accept it as the correct parameter and purges the data successfully. So, if the sp is run on 2015-08-06, it will purge data going back to 2014-08-06 or earlier with the following parameter:
exec dbo.PurgeOldData <'2014-08-06'>
which means, all the data prior to the above date will be purged. Even the sp was run on 2015-08-06, the converted value of 2014-08-06' is entered as the sp parameter.
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