January 23, 2012 at 4:26 pm
I have a query that current returns the year that a company donated money, as well as the amount of that donation. Anything older than 5 years ago (so 2007 and earlier) gets lumped into a "Previous Giving" category.
Does anyone know a way to pad the results of the table so if there is a year the company didn't donate, that it inserts a row with that year (say 2010) and a bunch of zeros?
I'll include a sample of one company below...
--Create table and insert two rows in order to test my query
CREATE TABLE GiftSummary(
GType varchar(1) NULL,
GiftYear varchar(4) NULL,
Consid varchar(20) NULL,
Amount money NULL,
GCount int NULL,
FYTotal money NULL,
FYCount int NULL,
TotalGiving money NULL,
SumCount int NULL,
SumHC money NULL,
SumSC money NULL,
PriorYear varchar(5) NULL,
GiftOrder int NULL
) ON [PRIMARY]
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2008', '186816', 135950.00, 1, 135950.00, 1,190950.00, 3, 190950.00, NULL, '2006', 2008 )
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2006', '186816', 55000.00, 2, 55000.00, 2, 190950.00, 3,190950.00, NULL, '2006', 1 )
--This is the query that needs help so that the last 5 years appear in the result set (2008 --> 2012)
select distinct consid,
case when g.giftorder = 1 then 'Previous giving' else g.giftyear end as GiftYear,
case when h.amount is null then 0 else h.amount end as HC,
case when s.amount is null then 0 else s.amount end as SC,
FYTotal,
FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
GiftOrder
from giftsummary g
left join (select giftyear, amount from giftsummary where gtype = 'S') s
on s.giftyear = g.giftyear
left join (select giftyear, amount from giftsummary where gtype = 'H') h
on h.giftyear = g.giftyear
union
select distinct consid,
'Total' as GiftYear,
isnull(SumHC,0) as HC,
isnull(SumSC,0) as SC,
'' as FYTotal,
SumCount as FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
'0' as GiftOrder
from giftsummary g
order by consid, GiftOrder desc
Thanks so much!
January 23, 2012 at 6:04 pm
Having the desired output as a sample would be helpful. I see the 2006 data in the table but I don't see it in the output of the query you listed, so I don't really understand how you want the data displayed. Thank you, though, for the good sample table and data.
The standard method to ensure that you get all records is to use a master list or lookup list, then LEFT JOIN to that master table. For example, if you have a master "GiftYear" table, you could:
SELECTy.GiftYear,
ISNULL(d.{{Value column}}, 0) AS {{ColumnName}}
FROMGiftYear y
LEFT JOIN {{Your data set}} d
ONd.GiftYear = y.GiftYear
WHEREy.GiftYear BETWEEN 2005 AND 2011
If you don't have a GiftYear table, you could use a tally table to get the values. The depending on the values needed, you could select records from Tally "WHERE N BETWEEN 2005 AND 2011" or you could select N + 2004 "WHERE N BETWEEN 1 AND 7".
If you don't have a Tally table, there are several ways to create them on the fly, especially if you have a very limited, fixed set. If you know you always need 7 rows, then you can use a VALUES clause:
SELECT N FROM (VALUES (1), (2), (3), (4), (5), (6), (7)) AS Tally (N)
Seeing sample data with all the intended rows would help me understand the desired output.
January 24, 2012 at 11:20 am
Thanks so much for your help with this! I'm a seasoned programmer but never had to do much T-SQL programming beyond simple joins.
To see how I'd like the results to display, please run these 3 insert scripts:
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2009', '186816', 0.00, 0, 0.00, 0,0.00, 0, 0.00, NULL, '2008', 2009 )
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2010', '186816', 0.00, 0, 0.00, 0,0.00, 0, 0.00, NULL, '2008', 2010 )
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2011', '186816', 0.00, 0, 0.00, 0,0.00, 0, 0.00, NULL, '2008', 2011 )
INSERT INTO GiftSummary ( GType,GiftYear, Consid, Amount, GCount, FYTotal, FYCount, TotalGiving, SumCount, SumHC, SumSC, PriorYear, GiftOrder )
VALUES ( 'H', '2012', '186816', 0.00, 0, 0.00, 0,0.00, 0, 0.00, NULL, '2008', 2012 )
Then change the final query to be this:
--This is the query that needs help so that the last 5 years appear in the result set (2008 --> 2012)
select distinct consid,
case when g.giftorder = 1 then 'Previous giving' else g.giftyear end as GiftYear,
case when h.amount is null then 0 else h.amount end as HC,
case when s.amount is null then 0 else s.amount end as SC,
FYTotal,
FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
GiftOrder
from giftsummary g
left join (select giftyear, amount from giftsummary where gtype = 'S') s
on s.giftyear = g.giftyear
left join (select giftyear, amount from giftsummary where gtype = 'H') h
on h.giftyear = g.giftyear
union
select distinct consid,
'Total' as GiftYear,
isnull(SumHC,0) as HC,
isnull(SumSC,0) as SC,
'' as FYTotal,
SumCount as FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
'0' as GiftOrder
from giftsummary g
where SumHC > 0
order by consid, GiftOrder desc
BTW, the result set gets bound to a GridView in an ASP.NET page.
Thanks again!
January 24, 2012 at 12:55 pm
It is pretty hard to tell what your desired output should look like. Can you provide that in the format you want it in instead of an insert? If we insert that data the query will be wrong. 😉
The first query in your example can be greatly simplified. There is no reason to join to subselects from the same table with multiple values.
This should provide the same information and is a lot easier to read.
select distinct consid,
case when g.giftorder = 1 then 'Previous giving' else g.giftyear end as GiftYear,
case gtype when 'H' then ISNULL(amount, 0) else 0 end as HC,
case gtype when 'S' then ISNULL(amount, 0) else 0 end as SC,
FYTotal,
FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
GiftOrder
from giftsummary g
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 12:58 pm
To truly get your five years of data you will need to use a calendar or a tally table. If at all possible you might consider changing your datatypes for Years to be int instead of varchar. Also, it appears there is no primary key for this table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 1:07 pm
Thanks for the optimization help! 🙂
As for the results, I'd like them to look like this attachment...
January 24, 2012 at 1:16 pm
What is that total row? It is kind of total line but not at the same time. There seems to be a lot of information you are not providing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 1:35 pm
Let's see if I understand correctly.
1 - You want data in the "detail" section for the last five years only, 2008 - 2012.
2 - Any gifts prior to five years ago (2008) should be consolidated into a line titled "Previous".
3 - A line titled "Total" should contain the sum of all gifts for all years.
4 - The "PriorYear" column should contain the latest year prior to the year of the record in which a gift was given (although it looks as though that is being pulled from the detail data; should it be calcuated for the "Previous" and "Total" lines?).
5 - The "TotalGiving" column should contain the total contribution only if a gift was given in that year. The same applies for SumHC and SumSC.
Is this correct?
Also, please explain the FYCount and SumCount columns.
January 24, 2012 at 1:49 pm
I apologize! In my effort to make a quick script for the purpose of asking my question, I left out the reasoning behind the script.
In this case, there is a stored procedure (that someone else developed) which returns a table of data that is bound to an ASP GridView (in a website someone else developed). I have inherited this project and was looking to make some enhancements.
Currently, the page only display the years that gifts/donations were given but we'd like the table to show all the last 5 years, even if no gifts were given. Also, HC and SC refer to Hard Credits (ie. I donated $100 in my own name) and Soft Credits (ie. I donated $100 in my wife's name) so that 's what those two columns are referring to when you see HC and SC.
Here is the complete/unmodified stored procedure being called to generate this table. I hope it clarifies things further but feel free to ask more questions as they arise. I just really appreciate the help! 🙂
ALTER PROCEDURE [dbo].[GetGifts] @consid varchar(50)
AS
BEGIN
truncate table giftsummary
insert into GiftSummary (GType,GiftYear,PriorYear,Consid,Amount,GCount,GiftOrder)
(
select 'H' as GType,
dbo.fnx_FiscalYear(dte,7) as GiftYear,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as PriorYear,
consid as Consid,
sum(amount) as Amount,
count(*) as GCount,
dbo.fnx_FiscalYear(dte,7) as GiftOrder
from v_hardcredits
where consid = @consid
and (dbo.fnx_FiscalYear(getdate(),7) - dbo.fnx_FiscalYear(dte,7) < 6)
group by consid, dbo.fnx_FiscalYear(dte,7)
)
insert into GiftSummary (GType,GiftYear,PriorYear,Consid,Amount,GCount, GiftOrder)
(
select 'S' as GType,
dbo.fnx_FiscalYear(dte,7) as GiftYear,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as PriorYear,
consid as Consid,
sum(SC) as Amount,
count(*) as GCount,
dbo.fnx_FiscalYear(dte,7) as GiftOrder
from v_softcredits
where consid = @consid
and (dbo.fnx_FiscalYear(getdate(),7) - dbo.fnx_FiscalYear(dte,7) < 6)
group by consid, dbo.fnx_FiscalYear(dte,7)
)
insert into GiftSummary (GType,GiftYear,PriorYear,Consid,Amount,GCount, GiftOrder)
(
select 'H' as GType,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as GiftYear,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as PriorYear,
consid, sum(amount) as Amount,
count(*) as GCount ,
1 as GiftOrder
from v_hardcredits
where consid = @consid
and (dbo.fnx_FiscalYear(getdate(),7) - dbo.fnx_FiscalYear(dte,7) >= 6)
group by consid
)
insert into GiftSummary (GType,GiftYear,PriorYear,Consid,Amount,GCount,GiftOrder)
(
select 'S' as GType,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as GiftYear,
(dbo.fnx_FiscalYear(getdate(),7) - 6) as PriorYear,
consid, sum(amount) as Amount, count(*) as GCount,
1 as GiftOrder
from v_softcredits
where consid = @consid
and (dbo.fnx_FiscalYear(getdate(),7) - dbo.fnx_FiscalYear(dte,7) >= 6)
group by consid
)
update giftsummary
set fytotal = g.total
from (
select consid, giftyear, sum(amount) as total from GiftSummary
group by consid, giftyear) g
where g.consid = giftsummary.consid and g.giftyear = giftsummary.giftyear
update giftsummary
set fycount = g.total
from (
select consid, giftyear, sum(gcount) as total from GiftSummary
group by consid, giftyear) g
where g.consid = giftsummary.consid and g.giftyear = giftsummary.giftyear
update giftsummary
set totalgiving = g.total
from (select consid, sum(amount) as total from GiftSummary
group by consid) g
where g.consid = giftsummary.consid
update giftsummary
set sumcount = g.total
from (select consid, sum(gcount) as total from GiftSummary
group by consid) g
where g.consid = giftsummary.consid
update giftsummary
set sumhc = g.total
from (select consid, sum(amount) as total from GiftSummary
where gtype = 'H' group by consid) g
where g.consid = giftsummary.consid
update giftsummary
set sumsc = g.total
from (select consid, sum(amount) as total from GiftSummary
where gtype = 'S' group by consid) g
where g.consid = giftsummary.consid
select distinct consid,
case when g.giftorder = 1 then 'Previous giving' else g.giftyear end as GiftYear,
case when h.amount is null then 0 else h.amount end as HC,
case when s.amount is null then 0 else s.amount end as SC,
FYTotal,
FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
GiftOrder
from giftsummary g
left join (select giftyear, amount from giftsummary where gtype = 'S') s
on s.giftyear = g.giftyear
left join (select giftyear, amount from giftsummary where gtype = 'H') h
on h.giftyear = g.giftyear
union
select distinct consid,
'Total' as GiftYear,
isnull(SumHC,0) as HC,
isnull(SumSC,0) as SC,
'' as FYTotal,
SumCount as FYCount,
isnull(TotalGiving,0) as TotalGiving,
SumCount,
isnull(SumHC,0) as SumHC,
isnull(SumSC,0) as SumSC,
PriorYear ,
'0' as GiftOrder
from giftsummary g
order by consid, GiftOrder desc
END
January 24, 2012 at 1:54 pm
Aha!!! So what you really want is to modify this proc to show the last x years?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 2:05 pm
Wow reading through that it looks like it could a total rewrite. There are just dozens and dozens of calls to the same scalar function. It inserts detail rows then updates each "group" as an aggregate to finally eliminate duplicates (select distinct) that it created. If you have much data I would guess the performance of this is really slow? If a total rewrite isn't on the books for now you are going to have to kludge this together with some duct tape and bailing wire. 🙂
You can probably use a tally table and isnull on every column to keep this together until you have time to think about a rewrite. I will see if I can scrape together some time in the morning to throw some porkchops at this unless somebody else comes along before then.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 2:33 pm
Hahaha! Thanks! Yeah, the whole system was written by a junior programmer. I have lots of areas to re-write so I was hoping I could just quickly add the missing years for now until I could re-write this part. Luckily it's for a system that only a couple of people access every so often.
January 24, 2012 at 2:37 pm
Are you familiar with tally tables? If so you can tape this together fairly easily. You can read Jeff Moden's article about them here. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
I am think you could use the tally table to get your years with a left join to this table and just throw an isnull around each field to load them with "defaults" when there is no data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2012 at 4:04 pm
I'm not familiar with these but I will take a look. Thanks for the link!
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply