February 20, 2014 at 5:37 pm
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
Below is the query m using to generate last 6 months start data and end date
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.
February 20, 2014 at 6:34 pm
Hi
I think a full outer join with a sum on the coalesced values may do the trick for you
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, '2014-10-12' as loyaltystartdate, null as loyaltyenddate, 6 as cashback union all
select 7 as idloyalty, 1000 as company, 100 as IdClient, '2014-08-12' as loyaltystartdate, null as loyaltyenddate, 7 as cashback union all
select 8 as idloyalty, 1000 as company, 100 as IdClient, '2014-08-12' as loyaltystartdate, null as loyaltyenddate, 77 as cashback ),
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
)
SELECT MONTH(COALESCE(cmp.loyaltystartdate, cli.loyaltystartdate)), YEAR(COALESCE(cmp.loyaltystartdate, cli.loyaltystartdate)), SUM(COALESCE(cmp.cashback, cli.cashback))
FROM Companyloyalty cmp
FULL OUTER JOIN clientLoyalty cli ON cmp.idclient = cli.IdClient AND MONTH(cmp.loyaltystartdate) = MONTH(cli.loyaltystartdate) and YEAR(cmp.loyaltystartdate) = YEAR(cli.loyaltystartdate)
group by MONTH(COALESCE(cmp.loyaltystartdate, cli.loyaltystartdate)), YEAR(COALESCE(cmp.loyaltystartdate, cli.loyaltystartdate))
February 20, 2014 at 6:38 pm
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.
select 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
ORDER BY Months.startdate asc
any further help please
February 20, 2014 at 6:49 pm
Hi Micky,
Thanks for your reply and from your sample query there is no logic to get last 6 moths data. i just gave the sample data. my actual table will have last one year data.
Any suggestions please
February 20, 2014 at 8:03 pm
born2achieve (2/20/2014)
Hi Micky,Thanks for your reply and from your sample query there is no logic to get last 6 moths data. i just gave the sample data. my actual table will have last one year data.
Any suggestions please
Just add in a where clause to fetch the records for the last six months. Something like
WHERE (cmp.loyaltystartdate >= dateadd(month,-6,getdate()) and cmp.loyaltystartdate < getdate())
or (cli.loyaltystartdate >= dateadd(month,-6,getdate()) and cli.loyaltystartdate < getdate())
If you want to work from the beginnings of the month you will need to do some extra work.
Since you also appear to have an end date, you may want to have a look at this article[/url] about finding active rows by Jeff Moden.
I see that you have said there will be no month overlaps with the tables, so you may also want to consider a union all rather than a join
SELECT myMonth, myYear, SUM(cashback)
FROM (
SELECT MONTH(loyaltystartdate) , YEAR(loyaltystartdate) , cashback
FROM Companyloyalty
WHERE loyaltystartdate >= dateadd(month,-6,getdate()) and loyaltystartdate < getdate()
UNION ALL
SELECT MONTH(loyaltystartdate), YEAR(loyaltystartdate), cashback
FROM clientLoyalty
WHERE loyaltystartdate >= dateadd(month,-6,getdate()) and loyaltystartdate < getdate()
) u (myMonth, myYear, cashback)
GROUP BY myMonth, myYear
February 20, 2014 at 8:16 pm
Thank you Micky for your reply and hope the where clause has to be like the below
WHERE (cmp.loyaltystartdate >= dateadd(month,-7,getdate()) and cmp.loyaltystartdate < getdate())
or (cli.loyaltystartdate >= dateadd(month,-7,getdate()) and cli.loyaltystartdate < getdate())
to get last 6 months hope i need to be subtract from -7. please correct me if am wrong.
February 20, 2014 at 11:15 pm
How do you want pick you months? It's the 21st Feb, do you want rows from February to be included? Do you want complete months regardless of the current day?
February 20, 2014 at 11:30 pm
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 )
, 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
)
select coalesce(EOMONTH(Co.loyaltystartdate) , EOMONTH(Cl.loyaltystartdate)),SUM(ISNULL(Co.cashback,0)+ISNULL(Cl.cashback,0))
from Companyloyalty Co
FULL OUTER JOIN clientLoyalty Cl
ON EOMONTH(Co.loyaltystartdate) = EOMONTH(Cl.loyaltystartdate)
GROUP BY EOMONTH(Co.loyaltystartdate) , EOMONTH(Cl.loyaltystartdate)
Order by 1 DESC
Regards,
Mitesh OSwal
+918698619998
February 21, 2014 at 5:34 am
Hi Micky and Mitesh, thanks for your info
Hi Micky,
I don't need to consider the current month.
Hi Mitesh,
On your sample what is EO month? Is it a function?
February 21, 2014 at 6:46 pm
The EOMONTH is a function to return the End Of Month. So to return the last six months excluding the current month you could do something like:
WHERE loyaltystartdate >= DATEADD(month,-6, DATEADD(month, -1, EOMONTH(GETDATE())) and
loyaltystartdate < DATEADD(month, -1, EOMONTH(GETDATE())
Please check this as I can't look at it at this moment. Make sure the time component is what you expect.
There are a number of articles here that cover a lot of date stuff, unfortunately I am have problems locating the particular one I am thinking of. Hopefully someone that works with dates more than I do will chime in:-)
February 21, 2014 at 7:22 pm
Thank you Micky. I will work with EOMonth. Appreciate your time on this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply