locked
get last 3 months on date selection RRS feed

  • Question

  • User639567535 posted

    I have datepicker on form.aspx i want when i select date from datepicker then i want last 3 month record ie. if i select date from dec then last 3 month record display 

    i try this 

    ALTER procedure [dbo].[spdatasumry]
    @RegNo nvarchar(10)
    as
    
    SELECT 
       ISNULL(SUM(total),0)  total,S.Name,	S.Month
    
    FROM 
    (
    SELECT  
    		count(tblVV.Name) as total,
    		DATENAME(MONTH,tblReg.StartDate) as [Month],
    		tblVV.Name
    from 
     tblVV
    inner join tblRV
    on tblVV.MID=tblRV.ID
    inner join tblReg
    on tblRV.RID=tblReg.RID
    WHERE 
    tblReg.StartDate >= DATEADD(MONTH, -3, GETDATE()) 
     AND  tblReg.EndDate   <= GETDATE()  and
     tblRV.RegNo ='782'
            AND Name <> ''
    and 
    tblVV.Name <>''
    GROUP BY 
    	tblVV.VName,tblReg.StartDate
    		 ) S
    		 group by 
    		 S.Name,	S.Month
    	

    above sp works but when i select date from aug then this show last 3 months and current month whereas when i select date from jan or another month then this  shows july,june and may data whereas i want when i select jan or any other month suppose i select jan then want dec,nov,oct and jan

    Monday, August 22, 2016 8:14 AM

Answers

  • User-901496047 posted
    Try below sp

    ALTER procedure [dbo].[spdatasumry] @RegNo nvarchar(10),
    @SelectedDate Datetime as SELECT ISNULL(SUM(total),0) total,S.Name, S.Month FROM ( SELECT count(tblVV.Name) as total, DATENAME(MONTH,tblReg.StartDate) as [Month], tblVV.Name from tblVV inner join tblRV on tblVV.MID=tblRV.ID inner join tblReg on tblRV.RID=tblReg.RID WHERE tblReg.StartDate BETWEEN DATEADD(MONTH, -3, @SelectedDate) AND @SelectedDate
    and tblRV.RegNo ='782' AND Name <> '' and tblVV.Name <>'' GROUP BY tblVV.VName,tblReg.StartDate ) S group by S.Name, S.Month
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 22, 2016 1:53 PM