previous,cummulative,current month data in SQL : The Official Microsoft ASP.NET Forums

Reply

naveensenaga...

Member

30 Points

85 Posts

previous,cummulative,current month data in SQL

Feb 22, 2013 12:13 PM|LINK

Hi Guy's..

I executed my SP , one of the column returns the values like below..

Material_Qty	Planned_Date
100	        2012-11-13 00:00:00.000
100	        2012-12-13 00:00:00.000
100	        2013-01-24 00:00:00.000
100	        2013-02-13 00:00:00.000
100	        2013-03-21 00:00:00.000
100	        2013-04-18 00:00:00.000

Now, my question i want to add 3 more column's like previousmonth, CummilativProgress upto previous month & current month..

I want to display the quantities related that 3 column's.

EX:

Previous_month Qty------100 2013-01-24 00:00:00.000
cummunlativeprogress--- 300 11,12,01 month's
currentmonth ----- 100 2013-02-13 00:00:00.000

How to achieve this can any help regarding this?

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Edited by bbcompent1 on Feb 22, 2013 12:17 PM

Reply

Pbalan.in

Contributor

2142 Points

483 Posts

Re: previous,cummulative,current month data in SQL

Feb 22, 2013 12:21 PM|LINK

Can you give me more detail about this store procedure and table schema. So that could help in better way..

Bala

Reply

naveensenaga...

Member

30 Points

85 Posts

Re: previous,cummulative,current month data in SQL

Feb 22, 2013 12:30 PM|LINK

Proj_Code Material_Id DED_Description DED_Short_Desc Material_Qty Planned_Date
O11001 2             RCC            CUM              100        2012-11-13 00:00:00.000
O11001 2             RCC            CUM              100           2012-12-13 00:00:00.000
O11001 2             RCC            CUM              100        2013-01-24 00:00:00.000
O11001 2             RCC            CUM              100        2013-02-13 00:00:00.000
O11001 2             RCC            CUM              100        2013-03-21 00:00:00.000
O11001 2             RCC            CUM              100        2013-04-18 00:00:00.000

On here i'm displaying data based upon Planned date, but i want to add 3 more col's

1) previous month --- here i want to display previoous month qty

2) cummulative previous month--- here i want to display upto previous month total qty

3) current month --- here i want to display this month qty.

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Reply

Pbalan.in

Contributor

2142 Points

483 Posts

Re: previous,cummulative,current month data in SQL

Feb 22, 2013 01:17 PM|LINK

Hi try this...,

Dont forget to mark as answer, if it useful

Declare @TempTable Table(Proj_Code nvarchar(50),Material_Id numeric(18,0),
DED_Description nvarchar(50),DED_Short_Desc nvarchar(50),Material_Qty numeric(18,0),Planned_Date datetime);
Insert into @TempTable Values ('O11002',2,'RCC','CUM',100,'2011-11-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2012-11-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2012-12-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-01-24 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-02-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-03-21 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-04-18 00:00:00.000');

Declare @curDate as nvarchar(500);

Select TT.Proj_Code,

Max(case when MONTH(TT.Planned_Date) =MONTH(getdate()) then
TT.Planned_Date end) as Planned_Date,
sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())-1) then
TT.Material_Qty else 0 end) as PreviousMonth,
sum(isnull(TT.Material_Qty,0)) as cummunlativeprogress,
sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())) then
TT.Material_Qty else 0 end) as CurrentMonth
from @TempTable TT where
MONTH(TT.Planned_Date) in (12,1,2) and YEAR(TT.Planned_Date) in (2012,2013) group by TT.Proj_Code

Bala

Marked as answer by naveensenagasetti on Feb 25, 2013 03:29 AM

Reply

eralper

Contributor

6048 Points

971 Posts

Re: previous,cummulative,current month data in SQL

Feb 22, 2013 01:41 PM|LINK

Here is an other script slightly different

WITH cte AS (SELECT        SUM(Material_Qty) AS qty, DATEPART(year, Planned_Date) AS y, DATEPART(month, Planned_Date) AS m
                             FROM            pdata
                             GROUP BY DATEPART(year, Planned_Date), DATEPART(month, Planned_Date)), sums AS
    (SELECT        CAST(CAST(y AS varchar(4)) + CASE WHEN m < 10 THEN '0' + CAST(m AS varchar(2)) ELSE CAST(m AS varchar(2)) END + '01' AS date) AS pd, qty
      FROM            cte AS cte_1), m AS
    (SELECT        pd, qty, DATEDIFF(mm, 0, pd) AS id
      FROM            sums AS sums_1)
    SELECT        m_1.pd, m_1.qty AS currentmonth, p.qty AS previous_month,
                                  (SELECT        SUM(qty) AS Expr1
                                    FROM            m AS c
                                    WHERE        (id <= m_1.id)) AS cumulative_qty
     FROM            m AS m_1 LEFT OUTER JOIN
                              m AS p ON m_1.id = p.id + 1
     ORDER BY m_1.id

T-SQL programming SQL Server Tutorials
SQL Server 2012

Marked as answer by naveensenagasetti on Feb 25, 2013 03:29 AM

Reply

sandeepmitta...

Contributor

6801 Points

1059 Posts

Re: previous,cummulative,current month data in SQL

Feb 23, 2013 09:51 AM|LINK

Declare @TempTable Table(
		Proj_Code nvarchar(10),Material_Id int, DED_Description nvarchar(10)
		,DED_Short_Desc nvarchar(10),Material_Qty int,Planned_Date datetime
)
Insert into @TempTable Values ('O11002',2,'RCC','CUM',100,'2011-11-13');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',200,'2012-11-13');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',300,'2012-12-13');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-01-24');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',200,'2013-02-13');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',300,'2013-03-21');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-04-18');

;with cte as (
	select *, ROW_NUMBER() over (order by Planned_Date) as rid from @TempTable
)

select Proj_Code, Material_Id, DED_Description, DED_Short_Desc, Material_Qty, Planned_Date
		, [Prev_mth] = isnull(sum(isnull(prev_mth,0)),0)
		, cumm_pogress = isnull(sum(isnull(qty1,0)),0)
		, curr_mth = Material_Qty
from (
	select a.* , b.rid as rid1, b.Material_Qty as qty1, case when a.rid - b.rid = 1 then b.Material_Qty else 0 end as prev_mth
	from cte a
	left join cte b on a.rid >  b.rid
) t
group by Proj_Code, Material_Id, DED_Description, DED_Short_Desc, Material_Qty, Planned_Date, rid
order by t.rid

Sandeep Mittal | My Blog - IT Developer Zone

Marked as answer by naveensenagasetti on Feb 25, 2013 03:29 AM

Reply

naveensenaga...

Member

30 Points

85 Posts

Re: previous,cummulative,current month data in SQL

Feb 25, 2013 03:28 AM|LINK

USING this it's working fine for me, offcourse other posts also working fine but i'm confusing in this line

MONTH(TT.Planned_Date) in (12,1,2) and YEAR(TT.Planned_Date) in (2012,2013)

I don't want hard coded value's . Here you pass month & year as Hard Coded but i don't want Hard Coded result...

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Reply

Pbalan.in

Contributor

2142 Points

483 Posts

Re: previous,cummulative,current month data in SQL

Feb 25, 2013 04:33 AM|LINK

Dear actually we have to pass the month and year for retrieve the records.

MONTH(TT.Planned_Date) in (12,1,2)  --> December,Janaury,Febraury Month number

and YEAR(TT.Planned_Date) in (2012,2013) --> 2012 and 2013 year

Bala

Reply

naveensenaga...

Member

30 Points

85 Posts

Re: previous,cummulative,current month data in SQL

Feb 25, 2013 04:43 AM|LINK

In a year total 12 month's . So, month values we give hard coded means Ok.

But year also you given 2012,2013... hard coded but i don't want like this based on Material_Id min date & max date

for ex

MONTH(TT.Planned_Date) in (1,2,3,4,5,6,7,8,9,10,11,12)
and
year(TT.Planned_Date) in (min_date, maxdate )

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Reply

Pbalan.in

Contributor

2142 Points

483 Posts

Re: previous,cummulative,current month data in SQL

Feb 25, 2013 04:47 AM|LINK

Or try like this...

Declare @TempTable Table(Proj_Code nvarchar(50),Material_Id numeric(18,0),
DED_Description nvarchar(50),DED_Short_Desc nvarchar(50),Material_Qty numeric(18,0),Planned_Date datetime);
Insert into @TempTable Values ('O11002',2,'RCC','CUM',100,'2011-11-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2012-11-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2012-12-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-01-24 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-02-13 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-03-21 00:00:00.000');
Insert into @TempTable Values ('O11001',2,'RCC','CUM',100,'2013-04-18 00:00:00.000');

Declare @Month_tbl Table(MonthValue numeric(18,0));
Insert into @Month_tbl Select DATEPART(m, DATEADD(m, -2, getdate()));
Insert into @Month_tbl Select DATEPART(m, DATEADD(m, -1, getdate()));
Insert into @Month_tbl Select Month(getdate());

Declare @Year_tbl Table(YearValue numeric(18,0));
Insert into @Year_tbl Select DATEPART(year, DATEADD(m, -2, getdate()));
Insert into @Year_tbl Select DATEPART(year, DATEADD(m, -1, getdate()));
Insert into @Year_tbl Select Year(getdate());

Select TT.Proj_Code,
Max(case when MONTH(TT.Planned_Date) =MONTH(getdate()) then
TT.Planned_Date end) as Planned_Date,
sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())-1) then
TT.Material_Qty else 0 end) as PreviousMonth,
sum(isnull(TT.Material_Qty,0)) as cummunlativeprogress,
sum(case when MONTH(TT.Planned_Date) =(MONTH(getdate())) then
TT.Material_Qty else 0 end) as CurrentMonth
from @TempTable TT where
MONTH(TT.Planned_Date) in (Select MonthValue from @Month_tbl) and
YEAR(TT.Planned_Date) in (Select YearValue from @Year_tbl) group by TT.Proj_Code

Dont forget to mark as answer, if it useful

Bala

previous,cummulative,current month data in SQL RSS

10 replies

naveensenaga...

previous,cummulative,current month data in SQL

Pbalan.in

Re: previous,cummulative,current month data in SQL

naveensenaga...

Re: previous,cummulative,current month data in SQL

Pbalan.in

Re: previous,cummulative,current month data in SQL

eralper

Re: previous,cummulative,current month data in SQL

sandeepmitta...

Re: previous,cummulative,current month data in SQL

naveensenaga...

Re: previous,cummulative,current month data in SQL

Pbalan.in

Re: previous,cummulative,current month data in SQL

naveensenaga...

Re: previous,cummulative,current month data in SQL

Pbalan.in

Re: previous,cummulative,current month data in SQL

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft