Click here to Skip to main content
14,970,304 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i wanted to find the monthly sales by just select the month and year.

below is the sample of i find the sales for Jan and Feb of 2014.
SELECT SUM(totalSell) AS monthlyTotal, monthGrp FROM(
SELECT sellDate, SUM(total) as totalSell, Format(sellDate, "yyyy-mm") AS monthGrp FROM(
SELECT sellDate, total FROM sell)
WHERE sellDate BETWEEN Format(#01/2014#, 'mm/yyyy') AND Format(#02/2014#,'mm/yyyy') 
GROUP BY sellDate)
GROUP BY monthGrp ORDER BY monthGrp

but the query above only return the complete sales for Jan and sales on 1 Feb 2014.

monthlyTotal     monthGrp
  100000         2014-01      <-- complete Jan 2014 Sales (correct)
    100          2014-02      <-- Sales on 1st Feb 2014 only (wrong)

select *From Table  where month(selldate) between 1 and 2 and year(selldate)=2014
you can create your query with the help of following query...
DECLARE @Month int
DECLARE @Year int

set @Month = 2
set @Year = 2014

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))/*Last*/
<pre lang="SQL">
select sum(total) AS monthlyTotal,
       trunc(sellDate)as monthGrp 
  from sell 
 where to_char(sellDate,'MM') between 1 and 2 
   and to_char(sellDate,'YYYY')=2014 
 group by trunc(sellDate);

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900