Query Results to display rows across depending upon parameters 1

[awaria]

Table Structure: A record set for each combination of Year and period

Year Period Account Balance Budget
2011 1 100-1000 $100 $90
2011 2 100-1000 $120 $110
2010 1 100-1000 $200 $150
==================================
Parameters to pass in are:
Year 2011
Period 1
Account 100-1000
===================================
Desired Query Results:

Account CurYearBal PriorYearBal CurYearBdgt PriorYearBdgt
100-1000 $100 $200 $90 $150

Appreciate assistance.

Thank you,

ZAW

 

[gmmastros]

Unless I misunderstand, the simplest query to get your desired results would be to use a "self" join. There's nothing magical about self joins. You are simply writing a query that joins back to itself. When you do this, you need to make sure you use aliases so that SQL Server knows which (version) of the table you are referring to.

Select A.Account,
       A.Balance As CurYearBal,
       B.Balance As PriorYearBal,
       A.Budget As CurYearBudget,
       B.Budget As PriorYearBudget
From   [!]YourTableName[/!] As A
       Left Join [!]YourTableName[/!] As B
         On  A.Account = B.Account
         And A.Year = B.Year+1
Where  A.Account = '100-1000'
       And A.Year = 2011
       And A.Period = 1

Notice a couple things:

I use a left join here so that you will still get results even if there is no prior "year" for a particular account. Under this situation, you will get NULL's for prior info (balance and budget).

Also note the join condition. We join on account but also on year from one table is the same as year + 1 from the other.

You may also need to include period in the join. It's hard to tell based on your sample data.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

This was perfect.

Many Thanks,

ZAW

 

[awaria]

George,

Is there a way to calc YTD balance?
Such as periodid always begins at 1 and goes to @enddate that is entered.

If @enddate = 2, then balance and budget should be sum of periodids 1 and 2.

Again, Thanks for your expertise and assistance.

ZAW

 

[gmmastros]

I don't understand the YTD Balance calculation the way you described it. Can you show some sample data and expected results?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

YEAR PERIOD ACCOUNT BAL BDGT
2011 1 100-1000 $100 $90
2011 2 100-1000 $200 $100
2010 1 100-1000 $50 $50
2010 2 100-1000 $150 $175

Results would have the following:

Parameters feed in:

Year 2011
Period 2
Account 100-1000

Acct CurPerBal CurPerBdg PriorBal PriorBdg YTDCurPer YTDCurBdgt

100-1000 $200 $100 $150 $175 $300 $190

YTD values are sum of period 1 and 2.

Thanks,

ZAW

 

[jbenson001]

George,

I don't understand part of your join

 And A.Year = B.Year+1

If table A is the current year(2011), and table B is the data for the previous year(2010), shouldn't it be B.Year-1 ?

Am I missing something?

Thanks
Jim

 

[gmmastros]

Jim. We want to add 1 to the b table so that 2010 + 1 = 2011

I actually made that same mistaken when composing my post but caught it before posting.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

OK now I get it. You explanation helped. I have never done a self join, so I got a bit confused. Thanks!

 

[gmmastros]

ZAW,

Sorry about the late reply. You can get the year to date info by writing a query that calculates the sum of the budget and balance and then using that query as a derived table that you join back to the original query, like this:

Select A.Account,
       A.Balance As CurYearBal,
       B.Balance As PriorYearBal,
       A.Budget As CurYearBudget,
       B.Budget As PriorYearBudget,
       YTD.YTDBalance,
       YTD.YTDBudget
From   [!]YourTableName[/!] As A
       Inner Join (
         Select  Account,
                 YEAR,
                 SUM(Balance) As YTDBalance,
                 SUM(Budget) As YTDBudget
         From    [!]YourTableName[/!]
         Group By Account, Year       
         ) As YTD
         On A.Account = YTD.Account
         And A.Year = YTD.Year
       Left Join [!]YourTableName[/!] As B
         On  A.Account = B.Account
         And A.Year = B.Year+1
Where  A.Account = '100-1000'
       And A.Year = 2011
       And A.Period = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,
Thanks for the post. The YTD numbers are not working out. I may have left out some details, as you elluded to above, periodid is another join attribute. I updated your script with the correct tables/fields, the results are that the data is accurate, except for the two YTD fields. Here is my script with a specified account (actnumst) 954-5100, and period (periodid) 1. When I update the periodid to 1, or 2 or 3, the CurBal, CurBudgt, PriorBal and PriorBdgt all are correct. However, the YTD fields are not correct, and their value does not change when I update the periodid.
--------------------
Select a.actnumbr_1
,a.dscriptn
,a.actdescr
,A.actnumst
,A.perdblnc As CurYearBal
,B.perdblnc As PriorYearBal
,a.variance
,A.Budgetamt As CurYearBudget
,B.Budgetamt As PriorYearBudget
,YTD.YTDBalance
,YTD.YTDBudget
From aa_GLAccountSummary As A
Inner Join (Select actnumst,YEAR1,SUM(perdblnc) As YTDBalance,SUM(Budgetamt) As YTDBudget
From aa_GLAccountSummary
Group By actnumst, Year1) As YTD
On A.actnumst = YTD.actnumst
And A.Year1 = YTD.Year1
Left Join aa_GLAccountSummary As B
On A.actnumst = B.actnumst
and a.periodid = b.periodid
And A.Year1 = B.Year1+1
Where A.actnumst = '954-5100'
And A.Year1 = 2012
And A.Periodid =
---------------------
My apoligies if my initial sample lead you down the wrong path.
Year1 (Year), Periodid (Period) and Actnumst (account) are the key joining/linking fields.

Thanks for all your help.

ZAW

 

[gmmastros]

Try this:

Select a.actnumbr_1
,a.dscriptn
,a.actdescr
,A.actnumst
,A.perdblnc As CurYearBal
,B.perdblnc As PriorYearBal
,a.variance
,A.Budgetamt As CurYearBudget
,B.Budgetamt As PriorYearBudget
,YTD.YTDBalance
,YTD.YTDBudget
From aa_GLAccountSummary As A
Inner Join (Select actnumst,YEAR1,SUM(perdblnc) As YTDBalance,SUM(Budgetamt) As YTDBudget         
From aa_GLAccountSummary         
[!]Where PeriodId <= 2[/!]
Group By actnumst, Year1) As YTD         
On A.actnumst = YTD.actnumst         
    And A.Year1 = YTD.Year1
Left Join aa_GLAccountSummary As B         
On  A.actnumst = B.actnumst
    and a.periodid = b.periodid          
    And A.Year1 = B.Year1+1
Where  A.actnumst = '954-5100'       
And A.Year1 = 2012       
And A.Periodid = [!]2[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

That's it! Works perfectly.

Thank you for all your help!!

ZAW

 

[awaria]

George, Sorry, I have one more request.

I left off a Prior YTD Balance, which would be similar to YTDBalance, just the sum of PriorYearBalance.

How would that get done with the sum(perblnc) providing the YTDBalance, where the sum(perblnc) would also have the provide the value for the prior year.

ZAW

 

[gmmastros]

Have you tried modifying the query to return this value. I would prefer to help you understand what's going on instead of just giving you the answer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

Yes, and I understand your point. I do not want to abuse your expertise.

I would have never written the script above on my own. Never heard of a join on the same table, let alone then doing another join on that same table.

Any guidance you can provide without providing the answer is appreciated. Again, I appreciate your help and do not want to take advantage of your expertise.

Thanks,

ZAW

 

[gmmastros]

try this:

Select A.Account,
       A.Balance As CurYearBal,
       B.Balance As PriorYearBal,
       A.Budget As CurYearBudget,
       B.Budget As PriorYearBudget,
       YTD.YTDBalance,
       YTD.YTDBudget,
       PriorYTD.YTDBalance As PriorYTDBalance,
       PriorYTD.YTDBudget As PriorYTDBudget
From   [!]YourTableName[/!] As A
       Inner Join (
         Select  Account,
                 YEAR,
                 SUM(Balance) As YTDBalance,
                 SUM(Budget) As YTDBudget
         From    [!]YourTableName[/!]
         Where   PeriodId <= 2
         Group By Account, Year       
         ) As YTD
         On A.Account = YTD.Account
         And A.Year = YTD.Year
       Left Join [!]YourTableName[/!] As B
         On  A.Account = B.Account
         And A.Year = B.Year+1
       Left Join (
         Select  Account,
                 YEAR,
                 SUM(Balance) As YTDBalance,
                 SUM(Budget) As YTDBudget
         From    [!]YourTableName[/!]
         Where   PeriodId <= 2
         Group By Account, Year       
         ) As PriorYTD
         On A.Account = YTD.Account
         And A.Year = PriorYTD.Year+1

Where  A.Account = '100-1000'
       And A.Year = 2011
       And A.PeriodId = 2

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom