12,076,206 members (48,912 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 20:36pm
Edited 28-Aug-12 20: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!!
Sharath2790 29-Aug-12 2:20am

chill!!
Santhosh Kumar J 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
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
 Richard MacCutchan 414 Jochen Arndt 345 OriginalGriff 332 Sergey Alexandrovich Kryukov 280 F-ES Sitecore 225
 Dave Kreskowiak 2,836 OriginalGriff 2,805 Richard MacCutchan 2,174 CPallini 1,607 CHill60 1,379