Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I've table called Stock details which has columns like
date/factory1/factory2/factory3/total/month_total/year_total.
{Total is factory1+factory2+factory3}
{month_total is stock details from the 1st month to the current date of that month}
{year_total is stock details from the 1st jan to the current date}
So the problem here is how do i write query to calculate year n month total
Please help!!
Thank you☻☻☺☺☺
Posted
Updated 28-Aug-12 19:58pm
v2

SQL
select monthtotal+yeartotal_id as total from stockdetails



updated solution:

C#
select sum(total) as month_Total from stockdetails where day(date)<=day(Getdate()) and month(date)=month(getdate()) and year(Date)=year(getdate())

The above query will give me sum of total column for the particular month 1st to current date.

C#
select sum(total) as year_total from stockdetails where day(date)<=day(Getdate()) and month(date)<=month(getdate()) and year(Date)=year(getdate())


this will give you years total
 
Share this answer
 
v3
Comments
Sharath2790 29-Aug-12 1:55am    
Sorry!! that doesn't work. probably you failed to understand my question! I need to add stocks for whole month in month_total and add stocks for whole year in year_total based on dates and i'm not using any id here!!
Santhosh Kumar Jayaraman 29-Aug-12 2:03am    
Probably you didnt explain this first time when you posted the question. Aint i right? and you are downvoting answer for your mistake!!
Sharath2790 29-Aug-12 2:20am    
chill!!
Santhosh Kumar Jayaraman 29-Aug-12 2:10am    
check my updated solution
Sharath2790 31-Aug-12 1:38am    
Hey! Santhosh Can you help me with latest query?? Please
if your table contians column which having month total and year total.
then you can use that values only...

try below query..

SQL
select datepart(year,dt) yr, datepart(mm,dt) month, month_tot,year_tot 
from users 
where dt in (select max(dt) from Stock_details 
             group by datepart(year,dt),
                datepart(mm,dt))
order by 1 asc,2 asc,3 asc
 
Share this answer
 
v2
Comments
Sharath2790 29-Aug-12 2:39am    
Your solution may work but i can't keep updating the year and month part because this database will be connected to vb front end or i can't keep asking the user to enter which month/year is this..
ssd_coolguy 29-Aug-12 2:43am    
means you want month & year wise data right?
Sharath2790 29-Aug-12 2:45am    
yep!!
ssd_coolguy 29-Aug-12 2:48am    
see my updated solution...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900