Click here to Skip to main content
11,920,772 members (55,474 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL SQL-Server
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 20:36pm
Edited 28-Aug-12 20: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
0 OriginalGriff 415
1 phil.o 170
2 Dave Kreskowiak 155
3 KrunalRohit 134
4 F-ES Sitecore 130
0 OriginalGriff 6,778
1 KrunalRohit 4,530
2 Sergey Alexandrovich Kryukov 3,203
3 George Jonsson 2,815
4 Suvendu Shekhar Giri 2,181

Advertise | Privacy | Mobile
Web02 | 2.8.151120.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2015
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