Compute Column In View...

Mar 11, 2006

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....

 

View 2 Replies


ADVERTISEMENT

Create Multi Column View From Single Column Data

Jan 9, 2008

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.

View 5 Replies View Related

Compute The Age

Mar 5, 2007

Hi all. How could i get the age of the employees given the birthdate.

table - personalinfo

name birthdate
john 2/15/2004 12:00:00 AM
peter 2/15/2003 12:00:00 AM
jon 2/15/2001 12:00:00 AM
mike 2/15/2000 12:00:00 AM
sam 2/15/2002 12:00:00 AM

Thanks
-Ron-

View 8 Replies View Related

COMPUTE Clause

Feb 2, 2008

I run SELECT statement with a COMPUTE clause,columns in the select list overrides on aggregate functions in COMPUTE clause.
why was overrided?
how show  output COMPUTE clause?
SELECT     NUMBERFROM         Table_1COMPUTE AVG(NUMBER)
Result:
NUMBER
1
56
78
89
56
 
Thanks,mohsen

View 3 Replies View Related

Select With A COMPUTE

Nov 9, 2007

Is there a way to change the column headings when using a compute? I have the following code:


Select IP_address, Caption1, VendorIcon, InterfaceID, Caption2, InterfaceIcon, In_Maxbps, Out_Maxbps, DateTime1

from #test

WHERE InterfaceID IN('144', '154')

ORDER BY InterfaceID

COMPUTE max(In_Maxbps), max(OUT_maxbps), min(OUT_maxbps), avg(OUT_maxbps)

BY InterfaceID


The compute gives me 2 columns with the heading max. I would like to change those headings.

Thanks.

View 4 Replies View Related

Compute And Update In One Statement

Jun 13, 2008

Hi Guys, got a problem.
I am trying to create a table of summarized fees. I was unable to do a Insert command so I settled for an Update command. But I ran into this error msg which I’m not sure how to fix. Can anyone see the problem or is it not even possible to Update and Compute in the same statement? Do I have to do a sub query?

Update FEE_SUMs_20080402
SET LOAN_Num = F.DDLOAN, Fee_Amt = F.DDMFEE
FROM FEE_Recs AS F
INNER JOIN dbo.Addr_20080402 ON
Account_Num = F.DDLOAN
ORDER BY F.DDLOAN
COMPUTE SUM(F.DDMFEE) BY F.DDLOAN

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

View 3 Replies View Related

Compute Sums In Select Query

Dec 24, 2003

i have this query and would like to have a sum for each column. how can i phrase the compute line please ?

select office as Office
, Sum(Case (role) when 'ebp' then 1 else 0 end) as 'EBP'
, Sum(Case (role) when 'support' then 1 else 0 end) as 'Support'
, Sum(Case (role) when 'Awaiting Disposal' then 1 else 0 end) as 'Awaiting Disposal'
, Sum(Case (role) when 'Interview Room' then 1 else 0 end) as 'Interview Room'
, Sum(Case (role) when 'Sch Drop In' then 1 else 0 end) as 'Sch Drop In'
, Sum(Case (role) when 'Sch CX Staff' then 1 else 0 end) as 'Sch CX Staff'
, Sum(Case (role) when 'Not in Use' then 1 else 0 end) as 'Not in Use'
, Sum(Case (role) when 'Public' then 1 else 0 end) as 'Public'
, Sum(Case (role) when 'IAG' then 1 else 0 end) as 'IAG'
, Sum(Case (role) when 'Delivery' then 1 else 0 end) as 'Delivery'
, Sum(Case (role) when 'NVQ Use' then 1 else 0 end) as 'NVQ Use'
, Sum(Case (role) when 'Hot Swap Spare' then 1 else 0 end) as 'Hot Swap Spare'
, Sum(Case (role) when 'Archived' then 1 else 0 end) as 'Archived'
, Sum(Case (role) when 'Network Infrastructure' then 1 else 0 end) as 'Network Infrastructure'
, Sum(Case (role) when 'Unknown' then 1 else 0 end) as 'Unknown'
, Sum(Case (role) when 'Drop in Centres' then 1 else 0 end) as 'Drop in Centres'
, Sum(Case (role) when 'Training' then 1 else 0 end) as 'Training'
from tempassets2
group by office, role
order by office

any help appreciated.

View 2 Replies View Related

Compute By Clause Problem In SQL 2000

Feb 9, 2004

I am having a problem with this compute by clause

The statement is a simple select

Select col1, col2 from table1
where <where clause>
order by col1
Compute Sum(col2) by col1

I want the display to show details and group totals.

The error message I keep getting is
Server: Msg 410, Level 16, State 2, Line 1
COMPUTE clause #1 'BY' expression #1 is not in the order by list.


I have previously used the compute clause so I know it should work !!

Ay help will be appreciated - Thank you.

View 4 Replies View Related

Compute Sum Result Cannot Show In Report?

Aug 23, 2014

How to display sql compute sum command their result in amount and qty in report?

I am try this query is successful show the total amount and qty in studio management server and show in screen. But run this query on report is without to show last column of qty and amount, why ?

select
xsodetail.shopcode,xsodetail.memono,xsodetail.txdate,
xsodetail.sku,xsoheader.depositamt,xsopayment.paymentcode,xsopayment.paymentamt,
xsodetail.itemamt,xsodetail.salesqty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsoheader.shopcode + xsoheader.memono = xsopayment.shopcode + xsopayment.memono
where (xsodetail.sku = 'L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N' and xsodetail.txdate = CONVERT(varchar(100), GETDATE(), 112)
compute sum(xsopayment.paymentamt) , sum(xsodetail.salesqty)

View 2 Replies View Related

Compute Command Change To Rollup?

Aug 25, 2014

i want this keep query output result but don't want the "compute " this command , because this query cannot run in this dos command directc.exe , Thus, it is have any writing skill , it is possible using of rollup ?

select d.shopcode
,d.memono
,d.txdate
,d.sku
,h.depositamt

[Code] ....

View 2 Replies View Related

How To Compute For A Backup File Size

Jan 9, 2008

hello guys,

Please bare with my english.

Is there a way to compute for the backup file size of the database.
Example.

DatabaseName
TEST_DB

*in MB
DataSize DataSize_Used DataSize_UnUsed
150.00 81.00 70.00

LogSize LogSize_Used LogSize_UnUsed
30.00 1.00 29.00


When I run a backup for TEST_DB ,
*base on this figure - can we compute the the estimated backup file size for TEST_DB?

View 2 Replies View Related

Joe Celko Nested Set Model: How To Compute The Lft And Rgt Cols

Jul 3, 2006

Hi,

With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..

i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..

"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S

View 3 Replies View Related

Compute Parent Status From Child Statuses?

May 22, 2008

Take the example of a master-detail/parent-child set of tables, say, for example, an order table and order details. The order details table has bit field called "IsShipped". I want the overall order status to be shipped=true when all of the order details have their IsShipped column true. Let's say this is in a stored procedure that returns a result set of orders and whether or not they are completely shipped. How could I achieve this?

View 10 Replies View Related

Stored Procedure, Select/Compute/Update

Oct 20, 2005

I am trying to update fields in my table based on certain critera.

UPDATE tblALMLoans

SET tblALMLoans.NextRepDate = dateadd(YY,5,tblALMLoans.OriginalDate)

do until tblALMLoans.NextRepDate > getdate()
tblALMLoans.NextRepDate = dateadd(YY,1,tblALMLoans.OriginalDate)
loop

FROM tblALMLoans
WHERE (tblALMLoans.RateFlag = 'A');

I know this is no where near what its supposed to look like, but this is in code what I am looking to do.

Any help would be greatly appriciated.

View 8 Replies View Related

SQL Statement To Compute Employee Pay For A Year At Different Pay Rates

Nov 23, 2005

I would like a single SQL to return all employee's total billablecompensation for a year. Their billable rates change throughout the year sounder the employee table (one), there is a compensation table (to many)which has the employee id, effective date, billable hourly rate. So in agiven year calendar year they could have many different (though usually 2 atmost) rates. These rates then have to correspond to and e multiplied bytheir corresponding billable hours from the time sheet table.I know I could create a series of UNIONs and hard code the effective dates,i.e.select from time sheets where employee=john and timesheet.task_date betweenjan 1 and jun 1, compensation.billable rate * timesheet.billable hoursUNIONselect from time sheets where employee=john timesheet.task_date between jun1 and dec 31 compensation.billable_rate * timesheet.billable_hoursI'd have to do that for every employee in a very large SQL.Is there an easier way using straight SQL? If not could it be done with astored procedure?Thanks for any insight.

View 2 Replies View Related

TSQL Or Procedure To Compute According To Previous Data

May 19, 2008

I have three coloumn in Salary Table
Emp ID, Emp Salary , Sequence
a1 1000 1
a1 2000 2
a1 2000 3
a2 4000 1
a2 5000 2
a2 5000 3
a2 6000 4



Now I have to calculate the count on salary if the previous salary is different then count +1 else is previous salary same then add +0.
so output be
EmpID and Updation in Salary
a1 = 2 and for a2 =3

Can anyone help me with the query or storeprocedure i can achieve this output counting according to previous data.

View 9 Replies View Related

How To Compute Biweekly Periods When Date Is Passed In As Parameter

Oct 7, 2014

How can I compute the biweekly periods when a date is passed in as a parameter, for example if the user enters 9/12/2014 I should get:

Friday /2nd half9/12/2014
Saturday9/13/2014
Sunday9/14/2014
Monday9/15/2014
Tuesday9/16/2014
Wednesday9/17/2014
Thursday9/18/2014
Friday/1st half 9/19/2014
Friday/2nd half9/19/2014
Saturday9/20/2014
Sunday9/21/2014
Monday9/22/2014
Tuesday9/23/2014
Wednesday 9/24/2014
Thursday9/25/2014
Friday/1st half 9/26/2014

View 2 Replies View Related

SQL 2005 On Windows Compute Cluster 2003 Will It Work ?

Mar 22, 2007

Hi all



I am busy looking at the WIndows 2003 Comptu Cluster setup (Well I am loading it)

And I am interested to know if any body has run SQL 2005 Enterprise on this platform before ?

I know that Windows Compute Cluster uses a JOB scheduler to run tasks and this can be customised for each task , to allow it to use more resourses or less .

I also know that SQL 2005 is much more capable of running cluster mode with database replication and log file shipping

I want to know is of I load SQL 2005 on a Clustered Compute setup will it handle the SQL jobs, database requests with out the scheduler.



The question might be a bit tricky but any help is appricated



Thanks



Sylvester

View 2 Replies View Related

Data Access :: How To Compute Student Grade In Server

Nov 21, 2015

i am having problem putting this query to calculate students grade using the condition and legend bellow.

WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'

[code]...

View 7 Replies View Related

Power Pivot :: Compute Billing In Previous Period

Jun 14, 2015

Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged.  Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period.  This is where I am getting stuck.  Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.

In trying to solve the problem, I tried to frame a calculated field formula but could not do so.  Therefore, I tried solving it via a calculated column formula in the PowerPivot window.  My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result.  I am getting an error when I write this formula (see Billing data tab of PowerPivot window).

View 11 Replies View Related

How To Add An Identity PK Column To A View

Nov 16, 2007

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

View 1 Replies View Related

View Changes Data In A Column

Sep 17, 2001

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

View 2 Replies View Related

Computed Column Of A View

Jun 11, 2003

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.

View 1 Replies View Related

MS SQL View The Column Entries

Oct 25, 2006

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.

View 2 Replies View Related

How To Add A Calculated Column In A View

Apr 21, 2008

*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

View 2 Replies View Related

Creating A View With A New Column

Jan 31, 2014

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

View 4 Replies View Related

Can Reference A Column In A View

Aug 14, 2014

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.

View 2 Replies View Related

Temp Column In VIEW

Sep 27, 2005

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

Add Column To A View - RESOLVED

May 3, 2006

I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?

View 2 Replies View Related

Update Column In A View

May 21, 2006

Hello all,
I'd like to create a view which shows all specific column values
(null values for example)
as calculated values from another column,
if for example i have the table:
---------------
| col1 | col2 |
|------|------|
| 123 | null |
| 126 | 9 |
---------------
i would like the view to be:

---------------
| col1 | col2 |
|------|------|
| 123 | 6 | ----> 6, since it's the sum of col1 digits 1+2+3
| 126 | 9 |
---------------

I don't really know how to do that, although i'm quite sure it's possible, any help will be appreciated,

Best Regards,
pitt

View 3 Replies View Related

Finding Column Name Associated With View

Apr 30, 2007

I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.

TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --

___________________________________________________
--just example

go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee

go

go
create view v_PersonalD
as
select SSN from Employee

View 5 Replies View Related

Bcp Into View With Derived Column

Jan 10, 2006

Hi,I have a view that looks something like this -CREATE VIEW myview AS SELECTmyudf(col1) as col1, col2, col3FROM mytableThe view has an 'INSTEAD OF' trigger on it to do the correct thing oninsert. When I run the bcp, it runs successfully, but the valueinserted into col1 is always NULL. I modified my trigger to get abetter idea of what was happening, and when I look at the values ofINSERTED.col1, they are all null. It appears that bcp is setting thecolumn value to null, presumable because the view definintion for thiscolumn is a derived column.Does any one know a way around this?Thanks!

View 5 Replies View Related

Change A Column Name In A View

Apr 24, 2008



Hello,
I am going to change a column name YYZ in a VIEW because of typo. It should be named as YYY.
What is best scenario?

Thanks

View 1 Replies View Related







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