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-
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
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.
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....
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'.
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.
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.
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)
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] ....
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?
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
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 RelatedI 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.
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 RelatedI 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.
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
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
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]...
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).