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
Go to Top of Page
   

- Advertisement -