CODEWITHSUNDEEP
asp.netsqlsql-server
Hardik Parmar
Hardik Parmar

Reputation: 1051

How to get last six month's data in sql

I am developing one chart in that chart I want to show last six month's details separately. I want to show the details in bar chart.

But I am not getting the any solution. I am bit weak in sql queries.

Till now I got the data for last month. But I want the data for last six month. here's my code.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = DATEADD(mm, 1, @StartDate)

SELECT  sum(quantity)
FROM RS_Sell_Order_Master as s
left outer join RS_Sell_Order_Mapping as sm on sm.sell_order_no = s.sell_order_no 
left outer join RS_GIN_Master as GIN on gin.product_id = sm.product_id 

WHERE del_date BETWEEN @StartDate AND @EndDate

I want o/p of last six month like this suppose I have sell 10 quantity last month & 20 prevous to that month & I didnt sell before that So o/p should be

null
null
null
null
10
20

some thing like this.

Upvotes: 1

Views: 2104

Answers (2)

StuartLC
StuartLC

Reputation: 107247

This will assign a date exactly 6 months ago - there doesn't seem to be a reason why you would need to go back to the zero datum of SqlServer's DATETIME:

SET @StartDate = DATEADD(mm, -6, CURRENT_TIMESTAMP);

Edit

Assuming that you want month's aligned from the first to the end of each month, and assuming because you have nulls but still want to show data that we will need to manufacture the missing months (e.g. with a recursive CTE)

DECLARE @StartDate DATETIME;
SET @StartDate = DATEADD(mm, -6, CURRENT_TIMESTAMP);

WITH cte AS
(
    SELECT 0 AS TheMonth
    UNION ALL
    SELECT TheMonth + 1
    FROM cte
    WHERE TheMonth < 5
)
SELECT sum(quantity)
FROM
    cte 
    LEFT OUTER JOIN RS_Sell_Order_Master as s
    ON del_date >= DATEADD(MM, cte.TheMonth, @StartDate) AND del_date < DATEADD(MM, cte.TheMonth + 1, @StartDate)
    -- Other Joins here
GROUP BY cte.TheMonth
ORDER BY cte.TheMonth;

SqlFiddle here

Upvotes: 3

techrhl
techrhl

Reputation: 454

total no of items per month can be calculated like

select sum(items),left(date,6)+'01' from yourTable
group by left(date,6)

assuming your date is in 112 format....and it is of varchar(8) type..

Upvotes: 0

Related Questions