12,952,398 members (39,957 online)
Rate this:
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
Thank you☻☻☺☺☺
Posted 28-Aug-12 19:36pm
Updated 28-Aug-12 19:58pm
v2

Rate this:

## Solution 1

`select monthtotal+yeartotal_id as total from stockdetails`

updated solution:

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

`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
v3
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 J 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!!
Santhosh Kumar J 29-Aug-12 2:10am

check my updated solution
Sharath2790 29-Aug-12 2:20am

chill!!
Sharath2790 31-Aug-12 1:38am

Hey! Santhosh Can you help me with latest query?? Please
Santhosh Kumar J 31-Aug-12 1:50am

latest? I didnt get you
Sharath2790 31-Aug-12 1:51am

http://www.codeproject.com/Questions/450538/Retrieve-last-updated-on-Database CHECK THIS!!!!
Rate this:

## Solution 2

if your table contians column which having month total and year total.
then you can use that values only...

try below query..

```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```
v2
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...

Top Experts
Last 24hrsThis month
 OriginalGriff 385 CPallini 135 Maciej Los 95 RickZeeland 95 AdvancedDNA 85
 OriginalGriff 6,239 CHill60 3,490 Maciej Los 3,083 Jochen Arndt 1,975 ppolymorphe 1,880