Click here to Skip to main content
13,139,254 members (87,378 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I've table called Stock details which has columns like
{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 28-Aug-12 19:36pm
Updated 28-Aug-12 19:58pm
Rate this: bad
Please Sign up or sign in to vote.

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
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
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 CHECK THIS!!!!
Rate this: bad
Please Sign up or sign in to vote.

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),
order by 1 asc,2 asc,3 asc
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
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.170915.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100