x

Need Help creating a 12 Month Trailing report

Hi All, Need help in generating report for Trailing Twelve Month (TTM)of data. SQL query is listed below. Any links if known would also be helpful. Thanks.

 select p.saddr1 PropAddr,
        p.scode  scode,
        sum(t.smtd * 
            case when a.neg = 0 
                 then 1 
                 else -1 
            end)                                      Amount
   from total t
  inner join (select at.scode  Tree
                   , a.hmy     ahmy
                   , a.scode   Acct
               , tot.sdesc AcctTot
           , ad.bnegate neg
                from accttree at
               inner join accttreedetail ad on ad.htree = at.hmy
               inner join accttreedetailxref adx on adx.hdetail = ad.hmy
               inner join accttreexref ax on ax.hdetail = ad.hmy
               inner join accttreedetail tot on tot.hmy = adx.htotal
               inner join acct a on a.hmy = ax.hacct
               Union All
              select Distinct 
                   at.scode  Tree
                   , a.hmy     ahmy
                   , a.scode   Acct
                   , ad.sdesc  AcctTot
           , ad.bnegate neg
                from accttree at
               inner join accttreedetail ad on ad.htree = at.hmy
               inner join accttreedetailxref adx on adx.hdetail = ad.hmy
               inner join accttreexref ax on ax.hdetail = ad.hmy
               inner join acct a on a.hmy = ax.hacct
               Union All
              Select Distinct 
                     at.scode
                   , a.hmy
                   , a.scode
                   , a.scode
                   , ad.bnegate 
                from accttree at
               inner join accttreedetail ad on ad.htree = at.hmy
               inner join accttreexref ax on ax.hdetail = ad.hmy
               inner join acct a on a.hmy = ax.hacct
             ) a on a.ahmy = t.hacct  
  inner join property p on p.hmy = t.hppty
  inner join propattributes pa on pa.hproperty = p.hmy
  where 1 = 1
 #Conditions#
  group by p.saddr1, p.scode
more ▼

asked Feb 15 at 04:49 PM in Default

avatar image

Bashadb18
1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x417
x107
x36
x11
x1

asked: Feb 15 at 04:49 PM

Seen: 35 times

Last Updated: Feb 16 at 08:24 AM

Copyright 2016 Redgate Software. Privacy Policy