Alternate Receipts
-
We have a contribution table where people information are input when they contribute... Some people pay every month while there are people who pay contribute once a while. Say when I put a certain month, I should be able to tell all the people that contributed that month and did not contribute the previous month. I do not know the starting point... and I should be able to pull this information for any month of any year; however, if it is not possible I should at least pull for couple of years. Do you think any of you here have worked on something like this and may be help me with this query. I need to create report for them.
Ex: Say If I put January 2006, I should be able to get all the people from the table who paid in January and did not pay in February.
Thanks in advance for the help.
-
We need a little more information on how your contribution table looks.
create table #Contribution (pk int identity, Contributorid int,
Amount money, ContributionDate datetime)
insert into #Contribution
select 1, 100, '1/01/2005' union
select 1, 100, '2/01/2005' union
select 1, 100, '3/01/2005' union
select 2, 100, '1/01/2005' union
select 2, 100, '3/01/2005' union
select 3, 100, '2/01/2005' union
select 3, 100, '3/01/2005'
select *
from #Contribution
pk Contributor Amount ContributionDate
1 1 100.0000 2005-01-01
2 1 100.0000 2005-02-01
3 1 100.0000 2005-03-01
4 2 100.0000 2005-01-01
5 2 100.0000 2005-03-01
6 3 100.0000 2005-02-01
7 3 100.0000 2005-03-01
-- Contributor 2 contributed in Jan, but not in Feb
select *
from #Contribution A
where datepart(mm,ContributionDate) = 1
and not exists (select *
from #Contribution B
where datepart(mm,ContributionDate) = 2
and A.Contributorid = b.Contributorid)
Results
pk Contributor Amount ContributionDate
4 2 100.0000 2005-01-01
-
Thanks for the help.
The contribution table as contrib_id (primary key, individual_ID, date, amount...etc.
contrib_id ind_id amount date
1238 5562 20.05 12/03/2005
1299 5569 10.25 01/05/2006
1310 5562 20.25 01/05/2006
So one ind_id can be in every month or any months depending on when they contributed. I should be able to pull ids when I put month, those peole should have contributed that month and not the previous month.
-
There ya go, modify the query I gave you and it will work.
-
Thanks a lot Ray. It works for sure.
-
What if the month and years are spread to 10 years? Do we have to write bunch of Case statements or there is way to calculate alternates for any length of time for this report? Thanks in advance for the help.
-
No one please
-
I mean anyone... thanks
-
Using Ray's table definition, try this:
select distinct A.*
from #Contribution A
left join #Contribution B
on A.Contributorid = B.Contributorid
and datediff(mm, '1900', B.ContributionDate) = datediff(mm, '1900', A.ContributionDate) + 1
where B.ContributorId is null
-
This is the query I got for Month = January 2006... Now how can I use your last query. please suggest Ray. Thanks again for your big time help.
select Ind_id as [Individual ID], firstname as [First Name], lastname as [Last Name], occupat
as Title from individual1
where ind_id in
(
select distinct payer_fk from contribution1 c
where ((datepart(mm, c.date) = 1) and (datepart(yyyy, c.date) = '2006'))
and payer_fk not in
(
select payer_fk from
contribution1 where
((datepart(mm, date) = 12) and
(datepart(yyyy, date) = '2005' ))
and delete_mark = 0
and subtype_fk <> 'check'
and amount > 0
 
and c.delete_mark = 0
and c.subtype_fk <> 'check'
and c.amount > 0
)
-
Jasper Thanks for your help.... I did not realize earlier it was you who replied.
-
Never mind, I guess you still need help
Try this query:select
Ind_id as [Individual ID],
firstname as [First Name],
lastname as [Last Name],
occupat as Title
from individual1
where ind_id in
(
select A.payer_fk
from contribution1 A
left join contribution1 B
on
B.payer_fk = A.payer_fk
and B.delete_mark = 0
and B.subtype_fk <> 'check'
and B.amount > 0
and datediff(mm, '1900', B.date) = datediff(mm, '1900', A.date) + 1
where
A.delete_mark = 0
and A.subtype_fk <> 'check'
and A.amount > 0
and B.payer_fk is null
) --
-
Thanks Jasper This seems like does the rite thing... I am sorry I am a new developer therefore I need help. This query I think pulls for the entire contribution1 table. How can I use this so that I can use a parameter say month and year that gives this ADD-Report for only that month. Like I said earlier, If I put 02 2006 it should give all the people that contributed in February and not in January 2006. Thanks again for your help.
-
Is this what you are looking for?
select
Ind_id as [Individual ID],
firstname as [First Name],
lastname as [Last Name],
occupat as Title
from individual1
where ind_id in
(
select A.payer_fk
from contribution1 A
left join contribution1 B
on
B.payer_fk = A.payer_fk
and B.delete_mark = 0
and B.subtype_fk <> 'check'
and B.amount > 0
and datediff(mm, '1900', B.date) = datediff(mm, '1900', A.date) + 1
where
A.delete_mark = 0
and A.subtype_fk <> 'check'
and A.amount > 0
and B.payer_fk is null
and datepart(mm, A.date) = @month and datepart(yyyy, A.date) = @year
) --
-
Yes,
You should use (Not Exists) predicate
for satisfying two or more conditions.
either
You can use (IN) Predicate.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply