Getting previous months data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting previous months data

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-20 : 19:39:52

Hi ,

My sample table structure,


with Companyloyalty as (
	select 1 as idloyalty, 1000 as company, 100 as IdClient, '2014-01-12' as loyaltystartdate, null as loyaltyenddate, 10 as cashback union all
	select 2 as idloyalty, 1000 as company, 100 as IdClient, '2014-01-29' as loyaltystartdate, null as loyaltyenddate, 20 as cashback union all
	select 3 as idloyalty, 1000 as company, 100 as IdClient, '2013-12-12' as loyaltystartdate, null as loyaltyenddate, 30 as cashback union all
	select 4 as idloyalty, 1000 as company, 100 as IdClient, '2013-12-25' as loyaltystartdate, null as loyaltyenddate, 40 as cashback union all
	select 5 as idloyalty, 1000 as company, 100 as IdClient, '2013-10-12' as loyaltystartdate, null as loyaltyenddate, 66 as cashback union all
	select 6 as idloyalty, 1000 as company, 100 as IdClient, '2013-10-12' as loyaltystartdate, null as loyaltyenddate, 6 as cashback union all
	select 7 as idloyalty, 1000 as company, 100 as IdClient, '2013-08-12' as loyaltystartdate, null as loyaltyenddate, 7 as cashback union all
	select 8 as idloyalty, 1000 as company, 100 as IdClient, '2013-08-12' as loyaltystartdate, null as loyaltyenddate, 77 as cashback )
	
	
	with clientLoyalty as 
	(
	select 1 as idclientloyalty, 100 as IdClient, '2013-11-12' as loyaltystartdate, null as loyaltyenddate, 10 as cashback union all
	select 2 as idclientloyalty, 100 as IdClient, '2013-11-14' as loyaltystartdate, null as loyaltyenddate, 20 as cashback union all
	select 3 as idclientloyalty, 100 as IdClient, '2013-11-29' as loyaltystartdate, null as loyaltyenddate, 30 as cashback union all
	select 4 as idclientloyalty, 100 as IdClient, '2013-09-25' as loyaltystartdate, null as loyaltyenddate, 40 as cashback union all
	select 5 as idclientloyalty, 100 as IdClient, '2013-09-12' as loyaltystartdate, null as loyaltyenddate, 66 as cashback union all
	select 6 as idclientloyalty, 100 as IdClient, '2013-09-12' as loyaltystartdate, null as loyaltyenddate, 6 as cashback 
	)

I am trying to get last 6 months cashback amount with month wise. first i have to consider Companyloyalty table and get the sum of cachback based on

month wise for last 6 months.

if MONTH data doesn't availble in Companyloyalty table then we have to take it from clientloyalty table based on IdClient of company

with my sample data, my required output is

JAN 30

DEC 70

NOV 60

OCT 72

SEP 102

AUG 84


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) Month,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (1),(2),(3),(4),(5),(6)) x(N) 
)
	select * from Months

am bit confused about how wo make join b/w these tables to check if data doesn't exists in one table(company) to map and get it in another table (Client)

Can anyone please help me in this.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-20 : 20:44:00

Here is my first try,

This is the query u tried to get the Companyloyalty data. but i am facing how to join with clientLoyalty to get mothly data not available.

one good point here is if any data for the month available in Companyloyalty table won't be available in clientLoyalty and vice versa.


elect MONTH,SUM(Incentive.cashback) from Months LEFT JOIN(Select loyaltystartdate,cashback from Companyloyalty
	) Incentive
	
	ON Incentive.loyaltystartdate >= Months.startdate
			AND Incentive.loyaltystartdate <= Months.enddate
			GROUP BY Months.Month, Months.startdate

any further help please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources