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.
Author |
Topic |
melora55
Starting Member
1 Post |
Posted - 2013-01-07 : 20:06:21
|
Hi,I am fairly new to SQL, and I am working with a fundraising database. I am trying to pull a list of donors with a list of all of their pledges for the past year, sorted by date (i.e. donor name, pledge #1, pledge #1 date, pledge #2, pledge #2 date, and so on). I have figured out how to summarize people’s total pledges, but I cannot figure out how to get a list of distinct pledges without just repeating the same pledge/dates over and over. I first tried pulling the earliest pledge, but this does not even work correctly:update #Test_Table set pledge1_amt = h.creamt, pledge1_date = h.mindatefrom #Test_Table ginner join(select MIN(j.date) as mindate, j.pledge, j.donor_id from Pledge j group by j.donor_id, j.pledge) hon g.donor_id = h.donor_id Donor Table:Donor ID Donor Name1234 Jane Doe5678 Mike Brown9012 Mary SmithPledge TableDonor ID Pledge Date1234 20.00 1/20/20125678 50.00 2/3/20125678 50.00 3/3/20129012 100.00 1/1/20129012 45.00 5/10/20129012 5.00 12/31/2012Desired Output:Donor ID Donor Name Pledge1_Amt Pledge1_Date Pledge2_Amt Pledge2_Date Pledge3_Amt Pledge3_Date1234 Jane Doe 20.00 1/20/2012 Null Null Null Null5678 Mike Brown 50.00 2/3/2012 50.00 3/3/2012 Null Null9012 Mary Smith 100.00 1/1/2012 45 5/10/2012 5.00 12/31/2012Any help or advice would be greatly appreciated. Thank you!! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-07 : 23:19:54
|
[code]SELECT d.DonorID,d.DonorName,MAX(CASE WHEN Seq=1 THEN Pledge END) AS Pledge1_Amt,MAX(CASE WHEN Seq=1 THEN [Date] END) AS Pledge1_Date,MAX(CASE WHEN Seq=2 THEN Pledge END) AS Pledge2_Amt,MAX(CASE WHEN Seq=2 THEN [Date] END) AS Pledge2_Date,MAX(CASE WHEN Seq=3 THEN Pledge END) AS Pledge3_Amt,MAX(CASE WHEN Seq=3 THEN [Date] END) AS Pledge3_DateFROM Donor dINNER JOIN (SELECT DonorID,ROW_NUMBER() OVER (PARTITION BY Donor_ID ORDER BY [Date] ASC) AS Seq,* FROM Pledge)pON p.DonorID = d.DonorIDGROUP BY d.DonorID,d.DonorName[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-07 : 23:20:05
|
[code]Declare @Donor Table(DonorID int,Donor Varchar(10),Name Varchar(10))Insert into @DonorSelect 1234,'Jane','Doe' union allSelect 5678,'Mike','Brown' union allSelect 9012,'Mary','Smith'Declare @Pledge Table(Donor int,Pledge decimal(10,2),PledgeDate Datetime)Insert into @PledgeSelect 1234,20.00,'1/20/2012' union allSelect 5678,50.00,'2/3/2012' union allSelect 5678,50.00,'3/3/2012' union allSelect 9012,100.00,'1/1/2012' union allSelect 9012,45.00,'5/10/2012' union allSelect 9012,5.00,'12/31/2012'Select DonorId,Donor,Name, max([1_Pledge]) as Pledge1_Amt , max([1_PledgeDate]) as Pledge1_Date, max([2_Pledge]) as Pledge2_Amt , max([2_PledgeDate]) as Pledge2_Date, max([3_Pledge]) as Pledge3_Amt, max([3_PledgeDate]) as Pledge3_Date from ( Select D.DonorId,D.Donor,D.Name,Pledge,PledgeDate, Cast(ROW_NUMBER() OVER (PARTITION by D.DonorId order by Pledge desc) as varchar(2)) + '_Pledge' as ThatFieldSequence, Cast(ROW_NUMBER() OVER (PARTITION by D.DonorId order by Pledge) as varchar(2)) + '_PledgeDate' as ThatOtherFieldSequence from @Donor D inner join @Pledge P on D.DonorId = P.Donor ) a pivot ( max(Pledge) for ThatFieldSequence in ([1_Pledge], [2_Pledge], [3_Pledge]) ) p1 pivot ( max(PledgeDate) for ThatOtherFieldSequence in ([1_PledgeDate], [2_PledgeDate], [3_PledgeDate]) ) p2group by DonorId,Donor,NameDonorId Donor Name Pledge1_Amt Pledge1_Date Pledge2_Amt Pledge2_Date Pledge3_Amt Pledge3_Date1234 Jane Doe 20.00 2012-01-20 00:00:00.000 NULL NULL NULL NULL5678 Mike Brown 50.00 2012-02-03 00:00:00.000 50.00 2012-03-03 00:00:00.000 NULL NULL9012 Mary Smith 100.00 2012-12-31 00:00:00.000 45.00 2012-05-10 00:00:00.000 5.00 2012-01-01 00:00:00.000[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|