SQL Server - Month to date sum (cumulatives)

HI All,

 I need some examples  how to make a sql query that reterive the data of month to date . If  date is 15-02-2008 then query get the sum of xyz column  from 01-02-2008 to 15-02-2008 , if the current date is 20-02-2008 then the query get the monthly cumulative  to date like 01-02-2008 to 20-02-2008.


Thanks
hammeed

Hello

 

Try following SQL server Query

 

DECLARE @EndDate AS DATETIME ='1996-07-30'

 

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR(4),YEAR(@EndDate))+'-'+CONVERT(VARCHAR(2),MONTH(@EndDate))+'-01'

 

SELECT SUM(od.Quantity)  FROM Orders  AS o

INNER JOIN [Order Details] AS od

ON o.OrderID = od.OrderID

WHERE CONVERT(VARCHAR(10),OrderDate,102)

BETWEEN  CONVERT(VARCHAR(10),@StartDate ,102) AND  CONVERT(VARCHAR(10),@EndDate ,102)

 

Check it and let me know your feedback

 Try Following query

 
DECLARE @EndDate DATETIME
set @EndDate =getdate()

DECLARE @StartDate DATETIME
set @StartDate  = CONVERT(VARCHAR(4),YEAR(@EndDate))+'-'+CONVERT(VARCHAR(2),MONTH(@EndDate))+'-01'

Select isnull(Sum(Amount),0) from TestTable
where  convert(datetime,convert(varchar,[PaymentDate],101)) > @StartDate
and convert(datetime,convert(varchar,[PaymentDate],101)) < @EndDate


Let me know feedback  Happy Coding...

Hi sunil and RB,

 Your both procedures are working . i am very new to sql server can you please guide me how i do this in view i have created one view in which i have start_date and end_date how the do same month to date in  view?

Sorry


Thanks
hammeed

 hi,

 view can not accept input parameter.
 you can not pass input parameter in view so you can use Store procedure or Table value function.

 let me know if any doubt..

 

 hi,

 view can not accept input parameter.
 you can not pass input parameter in view so you can use your query in Store procedure or Table value function.

 let me know if any doubt..

 

Thanks Sunil Darji  i have understand the thing i have to execute the procedure rather then a view.

Best wishes
hammeed

 

 yes , you have to use store procedure rather than view. still you found any issue.. let me know..

Hi Sunil,

The procedure is working perfectly , can you please help me out how to pass the date parameter in vba while executing the procedure from vba. when i execute the below one it works fine but how to pass the vba declare paramter date1  can  pass in the syntax

sql = " exec test_proc "



thanks
hammeed