September 19, 2011 at 1:27 pm
Hi everyone,
I'm new to the forum and a SQL 101 person, so let's hope everyone here is kind....
I'm trying to display a matrix of our grant data. We have a list of grant recipients, check amounts, and dates the check were cut. Is there a way to write "if the date is between 07/01/2010 and 06/30/11, then FY 2011", "if the date is between 07/01/2011 and 06/30/12, then FY 2012"?
I want to sum the amounts by this date range so these fiscal years are columns
Please let me know what else you need from me to explain the problem. I've struggled with it forever, so if anyone can solve this somewhat painlessly, I'll be your new BFF.
Thanks,
Tina
September 19, 2011 at 1:37 pm
The easiest solution would be a calendar table where you'd have a separate column to identify the fiscal year.
But you could also use a CASE function:
SELECT
SUM (SELECT CASE WHEN SomeDate > ='20100701' and SomeDate < '20110701' THEN YourValue ELSE 0 END) as FY2011,
SUM (SELECT CASE WHEN SomeDate > ='20110701' and SomeDate < '20120701' THEN YourValue ELSE 0 END) as FY2012
FROM YourTable
Did you notice how I separated the fiscal years? If you'd use BETWEEN '20100701' and '20110630' you'll exclude any values after midnight June30th, e.g. 2001/06/30 2:32am.
I, personally, made it a habit to always use > = and < (or < = and >) with the end date of one range being the start date of the next one just to make sure I'm not facing any gaps.
September 19, 2011 at 2:04 pm
Thanks for the reply. I think I understand your statement. I tried putting it in my overly complicated query (below) and received an error (SELECT not recognized). Does that have anything to do with the use of DISTINCT? Or preferably I just overlooked something obvious?
SELECT DISTINCT
V_GRRPT_Grants.GrantId, V_GRRPT_Grants.PayDate AS GrantDate, V_GRRPT_Grants.FundNumber, V_GRRPT_Grants.FundName, V_GRRPT_Grants.RecipientName,
V_GRRPT_Grants.PayeeName, V_GRRPT_Grants.GrantType, V_GRRPT_Grants.GrantStatus, V_GRRPT_Grants.PaymentAmount AS GrantAmount,
V_GRRPT_PAYMENTS.PayDate AS DatePaid, V_GRRPT_PAYMENTS.TotalAmount AS PaymentAmount, V_GRRPT_PAYMENTS.PayStatus,
V_GRRPT_PAYMENTS.CheckNumber, V_GRRPT_PAYMENTS.CheckDate, V_GRRPT_PAYMENTS.AdjustmentAmount,
V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.TopLevelProgramArea, V_GRRPT_Grants.RecipientCity, V_Coastal_Constituent_Website.Email,
SUM (SELECT CASE WHEN V_GRRPT_Grants.PayDate > = '20100701' and V_GRRPT_Grants.PayDate < '20110701' THEN V_GRRPT_Grants.PaymentAmount ELSE 0 END) as FY2011,
SUM (SELECT CASE WHEN V_GRRPT_Grants.PayDate > = '20110701' and V_GRRPT_Grants.PayDate < '20120701' THEN V_GRRPT_Grants.PaymentAmount ELSE 0 END) as FY2012
FROM V_GRRPT_Grants INNER JOIN
V_GRRPT_PAYMENTS ON V_GRRPT_PAYMENTS.GrantId = V_GRRPT_Grants.GrantId INNER JOIN
V_GRQRY_GrantCharacteristics ON V_GRQRY_GrantCharacteristics.GrantId = V_GRRPT_PAYMENTS.GrantId INNER JOIN
GRGrantCharacteristics ON GRGrantCharacteristics.GrantId = V_GRQRY_GrantCharacteristics.GrantId INNER JOIN
GRCharacteristicCodes ON GRCharacteristicCodes.CharacteristicId = GRGrantCharacteristics.CharacteristicId INNER JOIN
GRCharacteristics ON GRCharacteristicCodes.CharacteristicId = GRCharacteristics.CharacteristicId LEFT OUTER JOIN
V_Coastal_Constituent_Website ON V_GRRPT_PAYMENTS.GrantId = V_Coastal_Constituent_Website.GrantId LEFT OUTER JOIN
V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM ON
V_GRQRY_GrantCharacteristics.GrantId = V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.GrantId
WHERE (V_GRRPT_Grants.FundNumber = @FundNumber) AND (V_GRRPT_PAYMENTS.PayStatus = N'paid')
ORDER BY V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM.TopLevelProgramArea, V_GRRPT_Grants.RecipientName
September 19, 2011 at 2:18 pm
OUCH!!! My fault!! Total simple error:
SELECT
SUM (CASE WHEN SomeDate > ='20100701' and SomeDate < '20110701' THEN YourValue ELSE 0 END) as FY2011,
SUM (CASE WHEN SomeDate > ='20110701' and SomeDate < '20120701' THEN YourValue ELSE 0 END) as FY2012
FROM YourTable
I'm really sorry for causing the confusion... :blush:
When using aggregation, you'll have to use GROUP BY insted of DISTINCT.
As a side note: if you use alias names the query will be much more easy to read:
SELECT
Grants.GrantId,
Grants.PayDate AS GrantDate,
Grants.FundNumber,
Grants.FundName,
Grants.RecipientName,
Grants.PayeeName,
Grants.GrantType,
Grants.GrantStatus,
Grants.PaymentAmount AS GrantAmount,
Payments.PayDate AS DatePaid,
Payments.TotalAmount AS PaymentAmount,
Payments.PayStatus,
Payments.CheckNumber,
Payments.CheckDate,
Payments.AdjustmentAmount,
TL.TopLevelProgramArea,
Grants.RecipientCity, WS.Email,
SUM (CASE WHEN Grants.PayDate > = '20100701' AND Grants.PayDate < '20110701' THEN Grants.PaymentAmount ELSE 0 END) AS FY2011,
SUM (CASE WHEN Grants.PayDate > = '20110701' AND Grants.PayDate < '20120701' THEN Grants.PaymentAmount ELSE 0 END) AS FY2012
FROM V_GRRPT_Grants AS Grants
INNER JOIN V_GRRPT_PAYMENTS Payments
ON Payments.GrantId = Grants.GrantId
INNER JOIN V_GRQRY_GrantCharacteristics GrantChara
ON GrantChara.GrantId = Payments.GrantId
INNER JOIN GRGrantCharacteristics GRGrantChara
ON GRGrantChara.GrantId = GrantChara.GrantId
INNER JOIN GRCharacteristicCodes GRCode
ON GRCode.CharacteristicId = GRGrantChara.CharacteristicId
INNER JOIN GRCharacteristics GRCh
ON GRCode.CharacteristicId = GRCh.CharacteristicId
LEFT OUTER JOIN V_Coastal_Constituent_Website WS
ON Payments.GrantId = WS.GrantId
LEFT OUTER JOIN V_GRRPT_CCF_GrantCharacteristics_TOPLEVELPROGRAM TL
ON GrantChara.GrantId = TL.GrantId
WHERE (Grants.FundNumber = @FundNumber) AND (Payments.PayStatus = N'paid')
GROUP BY
Grants.GrantId,
Grants.PayDate,
Grants.FundNumber,
Grants.FundName,
Grants.RecipientName,
Grants.PayeeName,
Grants.GrantType,
Grants.GrantStatus,
Grants.PaymentAmount,
Payments.PayDate,
Payments.TotalAmount,
Payments.PayStatus,
Payments.CheckNumber,
Payments.CheckDate,
Payments.AdjustmentAmount,
TL.TopLevelProgramArea,
Grants.RecipientCity, WS.Email,
ORDER BY TL.TopLevelProgramArea, Grants.RecipientName
September 19, 2011 at 2:42 pm
Wow. I think that is the answer. I'll mess with the whole report tomorrow. Thanks Lutz, my new BFF.
Best site ever.
September 19, 2011 at 2:57 pm
tina 77078 (9/19/2011)
Thanks Lutz, my new BFF..
can i be ur BFF also ?? :ermm:
September 19, 2011 at 4:22 pm
ColdCoffee (9/19/2011)
tina 77078 (9/19/2011)
Thanks Lutz, my new BFF..can i be ur BFF also ?? :ermm:
Hey folks,
just keep in mind, this is still a professional SQL Server site. 😎
September 19, 2011 at 4:25 pm
LutzM (9/19/2011)
ColdCoffee (9/19/2011)
tina 77078 (9/19/2011)
Thanks Lutz, my new BFF..can i be ur BFF also ?? :ermm:
Hey folks,
just keep in mind, this is still a professional SQL Server site. 😎
U are a cruel man,Mr. Lutz Mueller! You share only ur knowldege, not ur BFF 😀
😛 :hehe:
{No pun intended, just for a little gag}
September 19, 2011 at 4:55 pm
ColdCoffee (9/19/2011)
LutzM (9/19/2011)
ColdCoffee (9/19/2011)
tina 77078 (9/19/2011)
Thanks Lutz, my new BFF..can i be ur BFF also ?? :ermm:
Hey folks,
just keep in mind, this is still a professional SQL Server site. 😎
U are a cruel man,Mr. Lutz Mueller! You share only ur knowldege, not ur BFF 😀
😛 :hehe:
{No pun intended, just for a little gag}
You might want to be a little more specific regarding the direction of your BFF "proposal".:-D
September 19, 2011 at 5:05 pm
Sorry - didn't mean to be unprofessional. You just have NO IDEA how long I was struggling with this issue. I just wish I found this forum a LONG time ago.
September 19, 2011 at 6:01 pm
tina 77078 (9/19/2011)
Sorry - didn't mean to be unprofessional. You just have NO IDEA how long I was struggling with this issue. I just wish I found this forum a LONG time ago.
Woooow, tina, no need to be apologetic. I was just trying to make gags 🙂 thats all... u are fine, and u are a lovely lady ( if only "tina" is a female name 😀 )
September 20, 2011 at 2:09 am
Don't get confused by CELKO's reply.
He's a strong advocat of ISO style programming and the usage of exactly the right terms / names when asking questions or naming a column. Sometimes the message he's trying to transport is hidden between the lines...
Even though he's right in general that stuff like a matrix report should be done at the front end layer, there are always exceptions to this rule (e.g. no front end that's capable of doing it, a one-time job or a rather complex query where the matrix result is just one part of it). In the given scenario I would still use T-SQL.
Regarding this site in general:
The vast majority of the folks around will help you to better understand SQL Server and help you to write more efficient code. So: Welcome aboard and enjoy your ride 😀
September 20, 2011 at 7:33 am
Ok, good! It would kinda suck to find the site that helps with all my SQL needs, then get kicked off for being ridiculous.
I love all the input and know I'll have a lot more questions, so thanks!
September 20, 2011 at 3:14 pm
I'm back. The data looks good, but I can't seem to get my mind around the report. I'm using visual studio and selected a matrix from the toolbox so it would look like the below.
FY2010 FY2011.....
Recipient 1 sum(amount)
Recipient 2
etc.
I'm getting duplicates and know I'm doing something wrong with my groups. Can anyone help with that limited info?
September 20, 2011 at 3:16 pm
Well you need a group on year for the columns
and a group for recipient on rows.
Then the data is sum(whatever).
This assume that you didn't pivot the data sql server side.
If you did then you don't really need a matrix, a table would do with a simple group of recipient.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply