Calculate Number Of Admits In Past 12 Months
Aug 5, 2013I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
View 6 Replies
ADVERTISEMENT
How Calculate 18 Months Past ?
Nov 13, 2000I have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?
Thanks
Luiz Lucasi
SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?
Oct 7, 2015I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
Generating A Table From A Log For Past 12 Months
Apr 1, 2004I would like to show a datagrid where the columns are the last 12 months from whatever today is. I am drawing from what could be a rather large tagle in the DB. The Table looks like this:LogID LogTally LogDate Item
1 2 3/28/03 apples
2 1 3/29/03 apples
3 2 4/01/03 oranges
4 3 4/01/03 apples
5 1 4/01/03 grapes
.......
293 1 3/17/04 oranges
294 1 3/17/04 apples And if someone wants to see a report on apples, they would see something like:3/03 4/03 5/03..................3/04
3 3 - 3I can select one month (this one) like this:SELECT SUM(LogTally) AS Expr1
FROM TABLE
WHERE (MONTH(GETDATE()) = MONTH(LogDate)) AND (YEAR(GETDATE()) = YEAR(LogDate)) AND (item = 'apples')
GROUP BY itembut, I just don't know how to do it for the past 12 months. Do I play with the dates and do 12 seperate SELECTS?
THANK YOU!
Transact SQL :: Last Record Not In Past 9 Months From Todays Date?
Aug 20, 2015I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
How Can I Calculate The Days Past Due
Apr 20, 2008If 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 ???
How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.
Apr 30, 2007Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
Calculate Elaped Months
Apr 3, 2008I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?
View 4 Replies View RelatedCalculate A Sum For The Previous Three Months?
Sep 15, 2015Is there an easy way to calculate a sum for the previous three months?
Data
Date,Area,PropertyID,Volume,DaysInMonth
197904,6,888888,9589,30
197905,6,888888,27403,31
197906,6,888888,17130,30
197907,6,888888,14321,31
197908,6,888888,15234,31
[Code] .....
Desired Output
Date,Area,PropertyID,Volume,DaysInMonth,3MonthSum, DaysInMonthSUm
197904,6,888888,9589,30,NULL,NULL
197905,6,888888,27403,31,NULL,NULL
197906,6,888888,17130,30,NULL,NULL
197907,6,888888,14321,31,54122,91
197908,6,888888,15234,31,58854,92
[Code] .....
How To Calculate Cumulative Of Months
Aug 2, 2014How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
SQL 2012 :: How To Calculate Date Difference In Months
Jan 23, 2015I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.
Oct 12, 2006Hi there,
if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.
Thanks very much,
Manny
Add A Number Of Months To Date
Oct 15, 2014I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:
select item, purchase_date, warranty_period
from inventory where item like '05MC%'
item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36
Trying To Get A Series Of Number To Go By Months
Jul 12, 2007if
(datalength
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD])
) = 1)
then
(0 ||
cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
else
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
Goal:
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
Transact SQL :: Number Of Distinct Users In Last 3 / 6 / 9 / 12 Months
Aug 26, 2015I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:
3 months 6 months 9 months 12 months
number of distinct users x y z w
SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format
Jun 15, 2015I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305
Power Pivot :: Revenue Schedules - Creating Rows By Number Of Months
May 26, 2015I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
Calculate Number Of Days
Aug 28, 2004I 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!!
How To Calculate Average Number Of Days Taken
Jan 16, 2014How 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/create Row Number Without Identity
Jul 23, 2005How do I output a row number for a table solely for the purpose ofquerying for a unique row?In my problem, the table from a legacy system does not have a primarykey, so it limits various querying I'd like to do that identifiesuniqueness in the table.The problem is that since I'm using DTS to simply copy the table toSQL, I don't want to create identity rows.
View 2 Replies View RelatedHow Do I Calculate The Sum Of Each Number In A String Of Numbers?
Jul 20, 2005I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
View 5 Replies View RelatedTransact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?
Aug 27, 2015I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
Calculating Admits Per 1000 From Two Data Sets
Aug 27, 2013I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.
Below are my queries from the two datasets.
MemberMonths dataset:
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
[Code] ....
Admits dataset:
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions
[Code] ...
SQL 2012 :: Calculate (Number Of Days To Pay) Logic
Jun 24, 2014How 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, 2015I 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.
Transact SQL :: How To Calculate Number Of Employees In Each Month
Jun 17, 2015I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
How To Calculate The Character Number In The Field Column
Apr 25, 2008
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
DATEDIFF Case Statement - Calculate Number Of Days
Feb 14, 2014Aim – 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
Two Count Queries - Calculate % Change In Number Of Orders
Mar 12, 2015I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.
I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.
Calculate Number Of Working Days Back From A Date
May 6, 2008Hi,
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
Power Pivot :: Calculate Number Of Business Days Between Two Dates
May 14, 2015I 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.
SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year
Jul 29, 2015Our 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.
SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays
Feb 2, 2014I 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] ......