Click here to Skip to main content
12,076,206 members (48,912 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
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 28-Aug-12 20:36pm
Edited 28-Aug-12 20:58pm
v2
Rate this: bad
 
good
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
  Permalink  
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 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: bad
 
good
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),
                datepart(mm,dt))
order by 1 asc,2 asc,3 asc
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2016
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