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