SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Getting previous months data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 02/20/2014 :  19:39:52  Show Profile  Reply with Quote
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.

Edited by - sqllover on 02/20/2014 20:32:20

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 02/20/2014 :  20:44:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000