Click here to Skip to main content
Rate this: bad
good
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 at 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 at 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 at 29-Aug-12 2:20am
   
chill!!
Santhosh Kumar J at 29-Aug-12 2:10am
   
check my updated solution
Sharath2790 at 31-Aug-12 1:38am
   
Hey! Santhosh Can you help me with latest query?? Please
Santhosh Kumar J at 31-Aug-12 1:50am
   
latest? I didnt get you
Sharath2790 at 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 at 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 at 29-Aug-12 2:43am
   
means you want month & year wise data right?
Sharath2790 at 29-Aug-12 2:45am
   
yep!!
ssd_coolguy at 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 490
1 Maciej Los 305
2 Richard MacCutchan 270
3 Mathew Soji 220
4 BillWoodruff 210
0 OriginalGriff 8,834
1 Sergey Alexandrovich Kryukov 7,477
2 DamithSL 5,689
3 Maciej Los 5,329
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411028.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2014
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