Transact SQL :: Use Field Alias In Calculated Fields?

Jul 18, 2015

use Northwind
Go
select 
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date 
, dbo.Customers.CustomerID 
, dbo.Customers.CompanyName 
, dbo.Products.ProductName

[code].....

I cannot use the alias field names as part of additional calculations for new columns. 

total_Amount and  Grand_Total cannot be done with my skill level.

View 9 Replies


ADVERTISEMENT

Transact SQL :: JOIN On Calculated Fields

Sep 21, 2015

I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).

SELECT
dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute
,ApplicationName
,COUNT(*) as Requests
FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute
WHERE EventClass IN (10,12)
GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName
ORDER BY StartTimeMinute

How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.

View 5 Replies View Related

Adding A Calculated Field By Using Two Fields From Different Data Sets

Oct 1, 2007



Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)

I tried to do that. But I got a error message.




"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."


Please can some one help me out?

View 5 Replies View Related

Transact SQL :: Calculated Fields Stop Working When There Are 10 Or More Rows In A Table

Nov 4, 2015

Setup:
Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit
SQL Server 2014 Express - 32 Bit

Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.

I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.

My PO table
CREATE TABLE [dbo].[PO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Quote_Number] [varchar](max) NULL,
[Customer] [varchar](max) NULL,
[CustomerPO] [varchar](max) NULL,
[PO_Received_Date] [datetime] NULL,
[Total_PO_Value] [decimal](18, 2) NULL,

[Code] ....

View 10 Replies View Related

Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?

Jan 21, 2008

Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.


Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.

But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link

Is this the standard and expected behavior? or its simply a BUG?

Have I done something wrong in my model? and in this case, How I can correct this?

regards.
Julio Diaz.

View 1 Replies View Related

Where To Put Calculated Fields

Oct 19, 2006

This doesn't necessarily belong in this forum, but I'm starting here in the hopes of getting some direction.

Business users have asked me to "map" a spreadsheet to our Datawarehouse. The spreadsheet contains a lot of calculations.

I created a first pass, but it was difficult to map the Analysis Services cube data to the spreadsheet data and in the process I had to hard code a lot of things that will make the spreadsheet less flexible for additional data.

So my question is, where is the best place to put calculated fields. In my SQL Statements, in ths SSIS transformations, or in the Analysis Cube?

Any help, or pointers to more information, would be greatly appreciated.

View 1 Replies View Related

Calculated Fields

Apr 19, 2007

I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?

View 6 Replies View Related

Adding Calculated Fields

Jul 20, 2005

I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,break_tue, lunch_tue, dinner_tue, ent_tue, .....etcI want to output the sum of the four monday columns as well as the sum ofthe four tuesday columns.I did this withSELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,break_tue+lunch_tue+dinner_tue+ent_tue AS tue_totFROM expense_reportI now want to add mon_tot and tue_tot AS total, but SQL is telling me thatit can not find mon_tot. Is there a way to do this? Thanks!DarrenMCP

View 4 Replies View Related

VS Crashes When I Use Calculated Fields

May 22, 2008



Hi ,

I have created a calculated data field for one of my data set. I use it for a table in my report.
But when I try to preview it, visual studio crashes and I get the following error message.

"An error occured during local report processing. An internal error occured on the report server.see the error log for more details."

I have not installd servers on my machine(only the sql tools). So I can not see error logs either.

Does any one know why this happenes?

Thanks

View 3 Replies View Related

Calculated Fields In The Report

Oct 1, 2007



I'm supposed to create a report like shown below.









Credit Tier
Jan-07
Feb-07
Mar-07

AA
0.00%
0.00%
0.00%

A
0.00%
0.00%
0.00%

B
0.00%
0.00%
0.00%

C
0.00%
0.00%
0.00%







Time columns go up to Dec 07. But I did not show all the columns.

The values are calculated as follows.
Field value for Jan07 = (No of Loans pass due in Jan07 / Total No of loans disbursed in Jan07)
Repayment due date is 5th of every month. if smbody does not pay on 5th, its considedred as pass due.


Source data tables look like this.

LoanTable(Disbursed date, userID, Amount, Status)

CreditTier table (Credit Tier, Rate) - Seems like no relationship with Loan table


we can identify pass due loans from status field in loan table. Status appears as 'Deliquency'.

Please Can any one help me to create this report?

View 11 Replies View Related

Index In Calculated Field From A Ntext Field

Nov 8, 2005

For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.

I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.

To trick the sorting I use a calculated field:

ORDER BY RSort - where Rsort is:

convert(varchar(4), RD.response) as RSort

It works but put a high load on the SQL server when the number of responses increases.

I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.

What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:

View 3 Replies View Related

Formatting Calculated Fields In Report

Jun 7, 2001

I have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom

View 1 Replies View Related

Boolean Calculated Fields In SQL Server

Feb 15, 2004

Hi
This may seem amazing and a stupid question but:

Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?

View 2 Replies View Related

Null Values In Calculated Fields

May 22, 2008



Hi all

I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query

select a + b + c + d from table1

and any of a,b,c or d is null, the result is null.

whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.

I want my calculated field to be null if any of the values are null.
Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.

Thanks

View 4 Replies View Related

SSRS / SSIS / Calculated Fields

Sep 10, 2007


Hi everyone:

I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?

Barry

View 3 Replies View Related

Calculated Fields In Queries Using Stored Procedures

Dec 20, 1999

Help!
I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.

I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.

How can I create functions to be used in my query?

Thanks.

View 1 Replies View Related

Reporting Services :: Find Row Sum On Calculated Fields?

Jul 20, 2015

I have a simple report with a row grouping on teh Date and record_id from an sql query. I would like to find the row total.

The expression I have on the row is:

=IIF(Max(Fields!closing.Value) > 0, Microsoft.VisualBasic.Interaction.IIF(Max(Fields!opening.Value) > 0, IIF(Max(Fields!closing.Value) > Max(Fields!opening.Value), Max(Fields!closing.Value) - Max(Fields!opening.Value), Nothing), Nothing), Nothing)

View 2 Replies View Related

Dynamic Data Set Based On Calculated Fields

Mar 26, 2008



Hi,

I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:


=iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C"))

Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:

=First(Fields!MyField.Value, "dataset_B")

OR

=First(Fields!MyField.Value, "dataset_C")

This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.

Any advice you have for me would be greatly appreciated!

Thanks.

View 1 Replies View Related

Problems With Non Deterministic Errors On Calculated Fields

May 22, 2008

Hi

I am new to SQL Server and am migrating another database

In my original database I have a default(constant) type field and a calculated field both of which call the same user defined function: GetMyUID()

My Function GetMyUID() returns the current date, time and users initials, i.e. "20080522T09:31:15.250LSG"

When a record is first created both fields have identical values

As the record is updated over time my constant field stays constant and my calculated field reflects the time the record was last updated and the initials of that person. So my first field is called 'Created' and my second is called 'Updated'

I would have thought that something like this would be a pretty bog standard and very straightforward requirement in any database

However in SQL I am getting error messages about the return value being non deterministic

I searched the web and found advice that to sort the problem I need to use WITH SCHEMABINDING in my function definition

Unfortunately I am still getting the same 'non deterministic' error

I wonder if (in the quest to not have an overlong field) by looking up the persons initials from a 'STAFF' file rather than leaving the username in full tacked on to the end that this is causing the problem?

I can't imagine that what I am trying to achieve is rocket science but unfortunately have not been able to find any resource on the web that solves this issue for me

In desperation I turn to you

Please help (preferably by letting me have a few lines of code that return the current date/time followed by the username lookup of a Username's initials, here is a snippet of my code...


RETURN (Convert(VarChar(8),@DateTimeNow,112)+ Right(Convert(VarChar(30),@DateTimeNow,126),13)+dbo.myInitials())

Where the dbo.myInitials() calls:

RETURN (SELECT STAFF.Code from dbo.STAFF where STAFF.Login = dbo.myLogin())

and dbo.myLogin() calls

return UPPER(Right(System_User,PATINDEX('%\%',System_User)))

View 7 Replies View Related

SQL Server 2008 :: Data Type Of Calculated Fields

Aug 10, 2015

I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.

Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?

SELECT YEAR(SamplingDate) AS YearRecruited,

COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',

[Code] ....

View 5 Replies View Related

Inserting Multiple Rows Into One Table (with Calculated Fields)

Nov 29, 2006

Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.


Quote:

View 9 Replies View Related

Power Pivot :: Where Is The Calculated Fields / Measures Area

Jun 5, 2015

This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?

View 5 Replies View Related

Reporting Services :: Add Two Different Custom Calculated Fields From Two Different Datasets In Matrix

May 22, 2015

I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.

So it reports for example how much we billed in May 2015, 2014 and 2013.

I have got the table to display this when reporting on one dataset by grouping by year

The datasets both look similar to this 

SELECT
  Practice.ibvSalesByJob.JobBilledExVAT
  ,Practice.ibvSalesByJob.[Year]
FROM
  Practice.ibvSalesByJob
  INNER JOIN Practice.idvJobType

[Code] ...

The calculated field is 

=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")
The field I am looking to add together is below
=Sum(Fields!JobBilledExVAT.Value)

If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.

I tried this

=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")

However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.

I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.

View 5 Replies View Related

Power Pivot :: How To Add Calculated Fields To Every Query In Data Model

Nov 12, 2015

I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever. 

View 3 Replies View Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

Jul 2, 2015

I am using MS SQL 2012.  I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it.  Then I need a total of those added decimal fields. My data is like this:

I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:

ID, provider_name, uniq_id, total_spent, total_earned

Here is sample data:

1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00

I need the results to be just 3 lines:

Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00

But then I would need the totals for the Provider, so:

Harbor, 1800.00, 3800.00

View 3 Replies View Related

Cannot Use Alias For A Field Name In Views!!!!!!!!

Jul 27, 2004

HI Friends,
Can anybody help in this prob with SQL server Enterprise Manager

Prob:

I cannot use alias name spelled same as the table field-name, in a view. For eg:
SELECT Name AS name
FROM dbo.test
if I take this view in the design view in Enterprise Manager, it will changed to:

SELECT Name
FROM dbo.test
Can anybody help me How can I avoid this???? I need the same alias name with different case....

Nish

View 5 Replies View Related

Trying To Display Alias Field Names

Nov 20, 2006

Hello All,

I have the following code:


USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'


I am trying to get the description which is flong and I get the following error message:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.

What am I doing wrong?

TIA

Kurt

View 2 Replies View Related

Calculated Field

May 14, 2008

 HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill 

View 5 Replies View Related

Calculated Field

Jul 25, 2005

I am a newbie to SQL Server using SQL Server 2000.

I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)?
However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).

The fields that I am basing the calculation on are:-

ColumnName Type Size Allow Nulls
PurchasePrice money 8 0
LoanSize money 8 0



this is the code for the calculated field


([loansize] / [PurchasePrice] * 100)


and this is the description of the calculated field that is forced in the designer



ColumnName Type Size Allow Nulls
LTV money 8 1



and the designer does not allow me to change the type.

I would be grateful of any pointers

Regards

Tony

View 1 Replies View Related

IF In A Calculated Field

Nov 8, 2014

How is the below formula written in a calculated field in SQL?

If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if

View 1 Replies View Related

Calculated Field

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI
===================================================
1000|liability | -10,000.00 | 08 | 2005
===================================================
1001| asset | 20,000.00 | 08 | 2005
===================================================
1000|liability | -9,000.00 | 09 | 2005

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger


The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Description Amount
asset 20,000.00
liability (10,000.00)
===========
Net Asset 10,000.00
===========

The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.

Guys, hope someone out there can help me with the sql command for the above report?

View 3 Replies View Related

Calculated Field (GPA)

Sep 26, 2007

I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.

here is a sample table:








Term

ID

LastName

FirstName

CRN

Subject

LetterGrade

Calc GPA


20072

1

Doe

John

1234

CHEM

B




20072

1

Doe

John

3214

BIO

A




20072

1

Doe

John

4321

LAW

B




20072

2

Bauer

Jack

1234

CHEM

A




20072

2

Bauer

Jack

3214

BIO

C




20072

2

Bauer

Jack

5467

FIN

B




A = 4.0
B = 3.0
C = 2.0
D = 1.0

View 10 Replies View Related

Calculated Field

Apr 20, 2007



This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.



What is the simplest way to accomplish this?

View 1 Replies View Related







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