1 Table Subquery with Group - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

1 Table Subquery with Group

Author

Topic

capjlp
Starting Member

19 Posts

Posted - 2015-02-05 : 11:58:27

Hi Folks

I have a table that has Finance transactions in it. I want to find the last time a transaction was put in and the balance that was recorded for that last entry for each account.

The Table looks like this

CustomerCode, AccountID,BillDate,Balance

1,1234,2014-11-31,1000.00
1,1234,2014-12-31,900.00
2,1234,2014-10-30,2000.00
2,1234,2014-11-30,1500.00
3,4567,2013-10-30,2200.00
3,4567,2013-11-30,2000.00

I am trying to write a query statement that can show me this.

1,1234,2014-12-31,900.00
2,1234,2014-11-30,1500.00
3,4567,2013-11-30,2000.00

Once I have this part working I actually need to make it a sub-query for more customer information from another table.

Thanks in advance for the help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 12:23:40

BTW, 30 days hath November!


declare @t1 table (customercode int, accountid int, billdate date, balance decimal(10,2))
insert into @t1 (customercode, accountid, billdate, balance) values

(1,1234,'20141130',1000.00),
(1,1234,'20141231',900.00 ),
(2,1234,'20141030',2000.00),
(2,1234,'20141130',1500.00),
(3,4567,'20131030',2200.00),
(3,4567,'20131130',2000.00)

select customercode, accountid, billdate, balance
from @t1 t1
where billdate = (
    select max(billdate) from @t1 t2
    where t1.customercode = t2.customercode
    and   t1.accountid = t2.accountid
    group by customercode, accountid
)
order by  customercode, accountid, billdate

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-05 : 13:00:55

Alternative:

select *
  from (select *
              ,row_number() over(partition by CustomerCode,AccountID order by BillDate desc) as rn
          from yourtable
       ) as a
 where a.rn=1

capjlp
Starting Member

19 Posts

Posted - 2015-02-05 : 13:01:32

quote:

Originally posted by gbritton

BTW, 30 days hath November!


declare @t1 table (customercode int, accountid int, billdate date, balance decimal(10,2))
insert into @t1 (customercode, accountid, billdate, balance) values

(1,1234,'20141130',1000.00),
(1,1234,'20141231',900.00 ),
(2,1234,'20141030',2000.00),
(2,1234,'20141130',1500.00),
(3,4567,'20131030',2200.00),
(3,4567,'20131130',2000.00)

select customercode, accountid, billdate, balance
from @t1 t1
where billdate = (
    select max(billdate) from @t1 t2
    where t1.customercode = t2.customercode
    and   t1.accountid = t2.accountid
    group by customercode, accountid
)
order by  customercode, accountid, billdate

Hey

My table exists already with data in it. I just listed the values for example we take payments throughout the month so really any day of the month we can have a payment. I don't really want to make a temp table to run this query. I am actually wanting to run this query through an excel connected spreadsheet. What would I change in your example if the table is already there.

My table has more data I just need pulled out what I had asked for. The table looks like this.
Billing Period Billing Dt LocationID CoopCode ACHNo Payment Principal Interest Balance StatusInd fk_ReqID
Jul-11 2011-07-26 00:00:00.000 203749 CUSTOMER1 20088971 18.22 11.62 6.60 2626.91 A 2037491
Aug-11 2011-08-25 00:00:00.000 203749 CUSTOMER1 20088971 18.22 11.65 6.57 2615.25 A 2037491
Aug-11 2011-08-19 00:00:00.000 204119 CUSTOMER1 20088971 18.71 11.94 6.77 2697.56 A 2041191
Aug-11 2011-08-19 00:00:00.000 23247 CUSTOMER1 20088971 35.17 22.44 12.73 5070.73 A 232471
Jul-11 2011-07-26 00:00:00.000 24397 CUSTOMER1 20088971 22.34 14.26 8.08 3218.56 A 243971
Aug-11 2011-08-25 00:00:00.000 24397 CUSTOMER1 20088971 22.34 14.29 8.05 3204.27 A 243971
Sep-11 2011-09-20 00:00:00.000 23247 CUSTOMER1 20089939 35.17 22.44 12.73 5048.23 A 232471
Sep-11 2011-09-26 00:00:00.000 24397 CUSTOMER1 20089939 22.33 14.25 8.08 3189.98 A 243971
Sep-11 2011-09-26 00:00:00.000 203749 CUSTOMER1 20089939 18.22 11.62 6.60 2603.57 A 2037491
Sep-11 2011-09-20 00:00:00.000 204119 CUSTOMER1 20089939 18.71 11.94 6.77 2685.60 A 2041191
Oct-11 2011-10-31 00:00:00.000 625206084021 CUSTOMER2 155822 30.00 29.90 0.10 4268.06 AL 252060840211
Jan-12 2012-02-01 00:00:00.000 169032 CUSTOMER3 45607 32.70 26.60 6.10 2448.24 A 1690321
Jan-12 2012-02-01 00:00:00.000 418008 CUSTOMER3 45607 12.32 9.74 2.58 1040.26 AB 4180081
Jan-12 2012-02-01 00:00:00.000 479038 CUSTOMER3 45607 19.81 12.74 7.07 2855.86 A 4790381
Jan-12 2012-02-01 00:00:00.000 545050 CUSTOMER3 45607 23.23 19.51 3.72 1489.65 A 5450501

Thank you

capjlp
Starting Member

19 Posts

Posted - 2015-02-05 : 13:15:30

quote:

Originally posted by bitsmed

Alternative:

select *
  from (select *
              ,row_number() over(partition by CustomerCode,AccountID order by BillDate desc) as rn
          from yourtable
       ) as a
 where a.rn=1

Thanks this did the Trick!

capjlp
Starting Member

19 Posts

Posted - 2015-02-05 : 13:59:00

Now to put all of these query's together. I have this first query

SELECT tblCoops.CoopName AS [Co-op Name],
tblCoops.RMF,
tblCustomer.LocationID,
tblCustomer.CustomerName,
tblCustomer.LMI,
tblCustomer.InactiveAccountDate,
DateDiff("m",[InactiveAccountDate],GetDate()) AS [Months Inactive]
FROM ((tblReqHeader INNER JOIN tblCustomer ON (tblReqHeader.fk_LocationID = tblCustomer.LocationID) AND (tblReqHeader.fk_CoOpID = tblCustomer.CoopCode)) INNER JOIN tblCoops ON tblCustomer.CoopCode = tblCoops.CoopCode)
WHERE (((tblCustomer.InactiveAccountFlag)='I'))
ORDER BY tblCoops.CoopName, [Months Inactive];

Which pulls data like this

Co-op Name RMF LocationID CustomerName LMI InactiveAccountDate Months Inactive
Coop1 0.040 12345 John Doe 1 0 2013-12-17 14
Coop2 0.040 45678 John Doe 1 -1 2013-08-16 18
Coop2 0.040 90112 John Doe 1 0 2013-01-02 25
Coop2 0.040 13145 John Doe 1 0 2012-10-01 28
Coop3 0.040 16178 John Doe 1 -1 2013-10-01 16

My second Query that I used from bitsmed's example

(select CoopCode,LocationID,[Billing Dt],Balance
from (select *
,row_number() over(partition by CoopCode,LocationID order by [Billing Dt] desc) as rn
from tblFinanceHistory
) as a
where a.rn=1)

CoopCode LocationID Billing Dt Balance
Coop1 12345 2014-12-31 00:00:00.000 5092.05
Coop2 45678 2014-12-31 00:00:00.000 3448.60
Coop2 90112 2014-12-31 00:00:00.000 3863.52
Coop2 13145 2014-12-31 00:00:00.000 7028.18

I need to combine them to have all the information from the first query plus the balance from the second query. Whats the best way to do that?

Thanks again

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-05 : 15:04:55

Perhaps this:

select a.fk_CoOpID as [Co-op Name]
      ,c.RMF
      ,a.fk_LocationID as LocationID
      ,b.CustomerName
      ,b.LMI
      ,b.InactiveAccountDate
      ,datediff(m,b.InactiveAccountDate,getdate()) as [Months Inactive]
      ,d.Balance
  from tblReqHeader as a
       inner join tblCustomer as b
               on b.CoopCode=a.fk_CoOpID
              and b.LocationID=a.fk_LocationID
              and b.InactiveAccountFlag='I'
       inner join tblCoops as c
               on c.CoopCode=a.fk_CoOpID
       left outer join (select CoopCode
                              ,LocationID
                              ,Balance
                              ,row_number() over(partition by CoopCode,LocationID order by [Billing Dt] desc) as rn
                          from tblFinanceHistory
                       ) as d
                    on d.CoopCode=a.fk_CoOpID
                   and d.LocationID=a.fk_LocationID
                   and d.rn=1
 order by a.fk_CoOpID
         ,b.InactiveAccountDate desc

capjlp
Starting Member

19 Posts

Posted - 2015-02-05 : 16:09:51

Your kung fu is strong!

That's works great!

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 16:38:19

quote:
Originally posted by capjlp

quote:
Originally posted by gbritton

BTW, 30 days hath November!

declare @t1 table (customercode int, accountid int, billdate date, balance decimal(10,2))
insert into @t1 (customercode, accountid, billdate, balance) values

(1,1234,'20141130',1000.00),
(1,1234,'20141231',900.00 ),
(2,1234,'20141030',2000.00),
(2,1234,'20141130',1500.00),
(3,4567,'20131030',2200.00),
(3,4567,'20131130',2000.00)

select customercode, accountid, billdate, balance
from @t1 t1
where billdate = (
select max(billdate) from @t1 t2
where t1.customercode = t2.customercode
and t1.accountid = t2.accountid
group by customercode, accountid
)
order by customercode, accountid, billdate

Hey

My table exists already with data in it. I just listed the values for example we take payments throughout the month so really any day of the month we can have a payment. I don't really want to make a temp table to run this query. I am actually wanting to run this query through an excel connected spreadsheet. What would I change in your example if the table is already there.

My table has more data I just need pulled out what I had asked for. The table looks like this.
Billing Period Billing Dt LocationID CoopCode ACHNo Payment Principal Interest Balance StatusInd fk_ReqID
Jul-11 2011-07-26 00:00:00.000 203749 CUSTOMER1 20088971 18.22 11.62 6.60 2626.91 A 2037491
Aug-11 2011-08-25 00:00:00.000 203749 CUSTOMER1 20088971 18.22 11.65 6.57 2615.25 A 2037491
Aug-11 2011-08-19 00:00:00.000 204119 CUSTOMER1 20088971 18.71 11.94 6.77 2697.56 A 2041191
Aug-11 2011-08-19 00:00:00.000 23247 CUSTOMER1 20088971 35.17 22.44 12.73 5070.73 A 232471
Jul-11 2011-07-26 00:00:00.000 24397 CUSTOMER1 20088971 22.34 14.26 8.08 3218.56 A 243971
Aug-11 2011-08-25 00:00:00.000 24397 CUSTOMER1 20088971 22.34 14.29 8.05 3204.27 A 243971
Sep-11 2011-09-20 00:00:00.000 23247 CUSTOMER1 20089939 35.17 22.44 12.73 5048.23 A 232471
Sep-11 2011-09-26 00:00:00.000 24397 CUSTOMER1 20089939 22.33 14.25 8.08 3189.98 A 243971
Sep-11 2011-09-26 00:00:00.000 203749 CUSTOMER1 20089939 18.22 11.62 6.60 2603.57 A 2037491
Sep-11 2011-09-20 00:00:00.000 204119 CUSTOMER1 20089939 18.71 11.94 6.77 2685.60 A 2041191
Oct-11 2011-10-31 00:00:00.000 625206084021 CUSTOMER2 155822 30.00 29.90 0.10 4268.06 AL 252060840211
Jan-12 2012-02-01 00:00:00.000 169032 CUSTOMER3 45607 32.70 26.60 6.10 2448.24 A 1690321
Jan-12 2012-02-01 00:00:00.000 418008 CUSTOMER3 45607 12.32 9.74 2.58 1040.26 AB 4180081
Jan-12 2012-02-01 00:00:00.000 479038 CUSTOMER3 45607 19.81 12.74 7.07 2855.86 A 4790381
Jan-12 2012-02-01 00:00:00.000 545050 CUSTOMER3 45607 23.23 19.51 3.72 1489.65 A 5450501

Thank you

you only need to change the name of the table from @t to whatever your table name is . I just use @t when testing things like this.

capjlp
Starting Member

19 Posts

Posted - 2015-02-06 : 08:59:04

Ok great that works too!

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources