quote:

---

Originally posted by madhivanan


select * from
(
select *,row_number() over (partition by col1 order by datecol desc) as sno from your_table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

---

SELECT DISTINCT TOP (100) PERCENT
                            (SELECT      SUM(Charge) AS Expr1
                              FROM           dbo.BillsSections AS BillsSections_2
                              WHERE       (BillIndex = a.BillIndex)) AS Sums,
                            (SELECT      SUM(Charge) AS Expr1
                              FROM           dbo.BillsSections AS BillsSections_1
                              WHERE       (BillIndex = a.BillIndex) AND (Type = 7)) AS Tax, a.BillIndex, a.Charge, b.BillStartDate, b.BillEndDate, b.CreateDate AS BillCreateDate, 
                        c.UserID, c.UserPaymentBalance, d .UserIndex, d .FirstName, d .MiddleName, d .LastName, d .CreateDate, c.StartDate AS ActivationDate, d .Company,
                         d .Email, d .LastCharge, d .TotalCharge, d .CustomInfo1 AS Deposit, dbo.AccountTypes.AccountName AS Package, dbo.AccountTypes.AccountCost, 
                        dbo.AccountTypes.AccountSetupFee AS SetupFee, tmp.Balance AS Amount_Due, tmp1.PBalance AS Previous_Amount
FROM          dbo.AccountTypes INNER JOIN
                        dbo.Users AS c ON dbo.AccountTypes.AccountIndex = c.AccountIndex RIGHT OUTER JOIN
                        dbo.Bills AS b RIGHT OUTER JOIN
                        dbo.BillsSections AS a ON b.BillIndex = a.BillIndex ON c.UserIndex = b.UserIndex LEFT OUTER JOIN
                        dbo.UserDetails AS d ON c.UserIndex = d .UserIndex LEFT OUTER JOIN
                        dbo.Services AS e ON a.Info1 = e.ServiceIndex INNER JOIN
                            (SELECT      b.UserIndex, b.Reference, b.Total + ISNULL(c.PrevBalance, 0) AS Balance
                              FROM           BillingTransactions b OUTER APPLY
                                                          (SELECT      SUM(Total) AS PrevBalance
                                                            FROM           BillingTransactions
                                                            WHERE       UserIndex = b.UserIndex AND BillingTransactionIndex < b.BillingTransactionIndex) c) tmp ON 
                        tmp.UserIndex = c.UserIndex AND tmp.Reference = a.BillIndex INNER JOIN
                            (SELECT      t1.*, COALESCE
                                                          ((SELECT      TOP (1) Balance
                                                              FROM          BillingTransactions2 t2
                                                              WHERE      t2.TransType = 1 AND t1.UserIndex = t2.UserIndex AND t1.BillingTransactionIndex > t2.BillingTransactionIndex
                                                              ORDER BY BillingTransactionIndex DESC), 0) AS 'PBalance'
                              FROM           BillingTransactions2 t1) tmp1 ON tmp1.UserIndex = c.UserIndex AND tmp1.Reference = a.BillIndex
WHERE      (b.BillType = 1) AND (a.Type <> 7)
ORDER BY a.BillIndex

quote:

---

Originally posted by madhivanan

What is the column name that returns the value AntonPieters?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by tkotey

Thanks madhivanan. I managed to use your SQL statement and it worked

---