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'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
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?
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 ...
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 .
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]
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)
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
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.
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.
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.
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
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.
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
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..
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
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.
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'
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 ???
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.
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 )
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.
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?
