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


ADVERTISEMENT

How To Calculate Cost Of A Trigger?

Mar 28, 2006

I wanna used derived fields to improve select performance of my system.
How can I  calculate the trigger cost in the system?

View 18 Replies View Related

How To Calculate Query Cost

Aug 29, 2007

hello,

Does anybody knows how to calculate cost of query ? Any help link or something.

--kneel

View 6 Replies View Related

T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username

Mar 30, 2015

how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.

Columns are four:

username, current_tenure, move_in_date, and count.

DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)

CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

[code]....

View 9 Replies View Related

Transact SQL :: Calculate Rolling Year Sales?

Aug 19, 2015

I have 2 tables as follows:

table A-NewParts
PartNumber
Description
LaunchDate
a123
product a
4/1/2009

[code].....

I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.

PartNumber
Description
LaunchDate
Yr1 Sales
Total Yr1 Sales
Yr2 Sales
Total Yr2 Sales
a123

[code].....

View 3 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 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 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 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

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

May 28, 2015

I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.

This is my script:

With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl

[code]....

the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.

Example:

invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075

That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.

View 4 Replies View Related

SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost

Jul 3, 2014

I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?

View 9 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View 4 Replies View Related

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

I have a small number of rows in a dataset, Table 1.  There is a CLOB on a large dataset, Table 2.  They join on a PK.  I would like to retrieve this CLOB and add it to the data flow for Table1.  In short I want to emulate the following:

Table 1:  Small table without CLOB, 10 rows. 
Table 2: Large table with CLOB, 10,000,000 rows

select CLOB
from table2
where pk = (select pk from table1)

I want this to return the CLOBs for the small number of rows in Table 1.  The PK is indexed obviously so it should be a fast look up.

Table 1 and Table 2 live on different Oracle databases.  How do I perform this operation efficiently in SSIS?  It seems the Lookup and Merge Join wont do this.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.

I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 3 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

View 2 Replies View Related

COST: 105%

Aug 28, 2007

I wrote some code to insert some data into a table, if the record already exists overwrite, etc. I've never done something like this so
I am going to bang my head against that for a while. But when I did an execution plan I got

Index Seek Cost : 105 %

How do I get more than 100% and what does that mean?

Thanks,

Jim

View 3 Replies View Related

Listing Datasets In Report (dataset Name, Dataset's Commands)

Oct 12, 2007



Is there any way to display this information in the report?

Thanks

View 3 Replies View Related

Search Using Like With Low Cost

Aug 7, 2004

hi there,

i have a site that has high traffic on it...and ofcourse there is search on the site...

one of the search options is to search by name, so i use a stored procedure as following :

select StID,StName from StudentTable where StName like '%' + @SearchField + '%'

ofcourse it works perfectly, the problem that it has a really high cost on the sql server..

so is there any way i can search for the name and using the '%' with lower cost, noting that the name is of type varchar(100)

Thanks in advance

Mahmoud Manasrah

View 4 Replies View Related

Lowest Cost

Nov 30, 2004

OK, this should be an easy one but my brain isn't quite working right now.

I have a table, we'll call Table1 like so:

ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00


And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:

SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID

and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.

Thanks,

View 7 Replies View Related

Cost Of A SQL Query

Aug 8, 2003

Is there a way that we can figure out what the cost of a SQL query is via jdbc ? any command etc?

View 1 Replies View Related

Sum Hours Per Cost

Apr 21, 2015

I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.

HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67

I want to report to the business:

40hrs 10.26
5.68 9.67

The query I have written is below:

Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or

[Code] ....

View 4 Replies View Related

How Much Does A VARCHAR Cost?

Aug 13, 2007

Hi,

Does anyone know how much storage space is required in SQL Server for a char(1) compared to a varchar(1)?

If I have a table with 14 varchar(1) fields and 60652902 rows, how much is this overhead costing me in wasted space?

Answers on a postcard!

Thanks,
Richard.

View 4 Replies View Related

Query Cost

Mar 11, 2008



I have 2 different queries which produce same result. I want to know which querry is better and why?
The query is used to display the employee details who is handling the maximum number of project.

Queries are the following

Query A



Code Snippet

SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.Empid=EmployeeDetails.Empid
GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName,
LUP_EmpProject.Empid
HAVING COUNT(LUP_EmpProject.Empid)>0
AND COUNT(LUP_EmpProject.Empid)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub)


Query B



Code Snippet

SELECT LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName AS EmpName,
COUNT(*) AS NumberOfProjects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.EmpID = EmployeeDetails.EmpID
GROUP BY LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName
HAVING COUNT(*)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub



Please Help!!!!!!!!!!!!!!!!1

View 2 Replies View Related

Cost Of Unicode?

Sep 28, 2006

I've converted most SQL Server databases to unicode since the AS 400 stores data in unicode (UTF-16). So far the only difference I see is that running queries to text in Unicode takes a lot longer, and UTF-16 files are 4x as large as Cp1252 files.

Any way to get the file sizes / query times of old with Unicode?

View 3 Replies View Related







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