Link to home
Start Free TrialLog in
Avatar of akiles99
akiles99Flag for India

asked on

mssql help...

Hi guys ,

I will tell clearly what i want...

How to use aggregate function such as AVG,SUM on column which has a varchar(20) data type...
Actually  my need is i want to store this AVG(col1)& SUM(col1) into another table(column) using triggers..

But when using avg,sum in varchar it throws error....

Also i want to find the date difference...And average of datedifference...

So i need 4 queries...

Please dontsend any link...

Thanks & Regards,
akiles
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of akiles99

ASKER

:)  I think I've answered both (hopefully).  Let me know if you have questions on either.
Ok nice...

But send me a sample trigger say the 4 queries to be stored in 4 variables namely @a,@b,@c,@d in table 1..

I want to update a 4 different columns col 1,col2,col3,col4  in another table2...
ie ..when a insert or update occurs on table1 @a value has to be stored in col1 and so on ...

You've lost me...Im not sure what you're trying to do in a trigger here.
Oh ok no probs i will work it....Thanks for ur time..

Best Regards
akiles
Ok.  Hopefully the queries above will steer you in the right direction...if you get stuck, just let me know.
Query 2 & 4 throws error....
post the queries you're using and the error
i have a column which stores the (datetime)....I want to retreive only the last year...how come?
suppose i have a column name udate..

UDATE
1/7/2008 12:00:00 AM
3/29/2008 12:00:00 AM
6/10/2008 12:00:00 AM
4/11/2008 12:00:00 AM

I want to find the Year to Date for the current year....
hmmm, so for the data you provided, what would you expect the outcome to be?
select avg(datediff(day, Max(ondate), getdate())) from conthist where rectype='S';
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

sum(cast(ondate as decimal)) from conthist where rectype='S'
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'cast'.
For the Year to date question:
 If a set of sales occured before 1/1/2008, then it would not be part of this year to date sales, would it?
so for all sales done between 1/1/2008 and 31/12/2008 i need a total?
the data would move to this field in another table  ULYRSALES field  when the System date turned past 1/1/2009 etc using trigger........
thanks