*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1
as
select tab.col1,tab.col2 from
from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
thanx
I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!
View 6 Replies View RelatedI am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies View RelatedI have a location table with columns for [state tax rate], [local tax rate] and a bit column for each to indicate if it is active. From this I need to calculate a total tax percentage; i.e.(state tax rate * bit) +(local tax rate * bit). If I want to stay at the database with this I can identify three alternatives (I'm sure there are more):
1. Calculated column
2. Table Variable (or Temp. Table)
3. View
I'm interested in feedback as to which of these methods is more appropriate or if there is a better way I haven't identified.
Thanks
Hello Everyone,
I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much. Dave Roda
View 4 Replies View RelatedHi,I'm struggling to get a calculated column to work in sql, the fields to be calculated are:[AdRevenue_a] money[Admissions_a] int[DoorPrice_a] smallmoney[DoorSplit_a] moneyAnd the calculation I require is:(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100This is what I think it should be but it doesn't work...convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))Any suggestions??
View 6 Replies View RelatedI am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:
COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.
I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.
I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".
----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
[code]....
I have calculated column which is referenced by function and I need to alter the function. How can I disable the calculated column so I am able to modify the function?
..................................................................
I have orders table and I need to disable the calculated column OrderDate so I may able to modify the function dbo.udfTicksToDateTime.
CREATE TABLE Orders (
OrderDateAsTicks BIGINT,
OrderDate AS dbo.udfTicksToDateTime(OrderDateAsTicks)
)
Thanks
Sanjeev
Cleveland
Hi,
I'm writing a query that calculates values for each id, then I need to sum up all the values for each id and put them in another field, here is what I wrote but SQL cannot understand the column that I calculeted
select id,term_cd,
case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004) then '1'
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then '0.5'
else '1' end as term_count,
SUM(term_count) AS ttd_enrolled
group by id,term_cd
SQL gives me an error "invalid column name tern_count" in the line where I calculate the sum.
what's wrong with the query? or should I calculate the term_count in an inner query?
Appretiate your help
Table: Names
Columns: Name_RID char(10)
Name_Type smallint
Name_Last char(50)
Name_First char(25)
Name_MI char
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name
But how can I test the value of the Name_Type field to determine how the Name column looks
if Name_Type = 1 then
Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI)
else
Name = Name_Last
The Name_Type represents Individual versus an Entity
0,ABC Pipeline,,
1,Williams, John,A
I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.
Product O is added according to:
for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501
for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150
SQL:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
[Code] ....
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
[Code] ....
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
[Code] ....
Where we have increase the size of the calculated column in cube..whether in attribute or some where else...
View 4 Replies View RelatedI have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.
View 2 Replies View RelatedMy simplistic data model looks like this:
Table.1
Spread Product Sales Channel ..
2 x CB ..
2 y CB …
4 x GFR ..
4 y GFR ..
I need a column with Spread Mapping, which should look like this:
->There are more columns in data model.
-> Spread for GFR is wrong so it must be mapped to be able to compare with CB
I guess there should be calculated column formula to calculate Spread Mapping. How to do it?
Hi All,
I need to show the Cumulative calculated value only in Total by year/Group. I could not use Visibility expression using
InScope, as it creates *Blank column. Please go thru details below.
Year
Month01 02 03 Total
Salary Salary Salary Salary Cumulative (Calc)
Employee01 20 5 25 25
Employee02 10 10 20 45
.....
Total
How can i achieve this?. Any suggestion on this would be appreciated.
Thanks,
How to include a time difference column using 2 other date columns during creation of a table ?
The requirement is to create a table and include the following columns:
1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:
=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])
But as soon as I substitute the numerical column by a string column, #error results.
I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.
I want a report that displays selected year quantity sales and previous year sales quantity and their quantity difference.
i also want to display a chart like I added year to the series group but i don't know how to add difference to the series group.
I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period.
I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time.
The current formula is:
CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)
For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order.
However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders.
In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order.
Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).
To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK.
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList.
I am wondering how can I add an identity PK to my view?
TIA,Jeffrey
I have a SQL table that consists of columns A, B and C. I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column. This computed column has the following logic:
If B is blank or null then NewColumn = A + ' - ' C
else
NewColumn = A + ' - ' + B
I am just wondering how the SQL statement should look like....
Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.
TIA
Jennifer
I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:
View
====
Col1: Unit = get_number_of_units()
Col2: MV = get_number_of_units() * get_unit_value()
I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?
Is get_unit_value() being called if I do Select Col1 from View?
Thanks.
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.
I'm using SQL Server 2008 to solve the following exercise.
I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone
I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders
how can i reference a column in a view.
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
Not sure if I am approaching this correctly. I can get name, address, zip code from a table via a VIEW. But the problem is I need to create another column called "status" in the same view that is based on "customer_end_date" if end_date> GETDATE() then 'P'(pick-up) and end_date< GETDATE () then 'I'(install). Using a stored procedure this is easy...but how would I do this in a VIEW?
View 6 Replies View Related