akiles99
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:) I think I've answered both (hopefully). Let me know if you have questions on either.
ASKER
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 ...
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.
ASKER
Oh ok no probs i will work it....Thanks for ur time..
Best Regards
akiles
Best Regards
akiles
Ok. Hopefully the queries above will steer you in the right direction...if you get stuck, just let me know.
ASKER
Query 2 & 4 throws error....
post the queries you're using and the error
ASKER
i have a column which stores the (datetime)....I want to retreive only the last year...how come?
ASKER
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....
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?
ASKER
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'.
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'.
ASKER
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........
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........
ASKER
thanks
ASKER
Thank u very much...Please check it.....
https://www.experts-exchange.com/questions/23589389/Trigger-for-date.html?anchorAnswerId=22071851#a22071851