0

I am working on a Cash Book project in Wpf and want to generate report of cash paid for expenses. Every time on cash paid an expense head is assigned to it. And I have another table for these Expense Headings. Now I want to show daily cash expense report with separate expense heading.

CashBook Expense table :

  1. Date
  2. details
  3. previousBalance
  4. cashPaid
  5. closingBalance
  6. cashExpenseHead_ID (foreign key)

CashBookExpenseHeadings table :

  1. expenseHeadID
  2. expenseHeadName

I have almost 60 expense heads, can anyone help me in sql query for this scenario.

Thanks in advance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Abdul Salam
  • 3
  • 1
  • So, a crosstab/pivot? – Caius Jard Dec 19 '18 at 11:41
  • It looks like LEFT JOIN to me. You want data from table A and add header from table B using cashExpenseHEad_Id https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Michał K. Dec 19 '18 at 11:45
  • I would think an inner join and order by ExpenseHeaderName. Then have a band for headername and another detail one for the expense details if this is a banded type of report you're using. – Andy Dec 19 '18 at 12:09

1 Answers1

1

Not sure I fully understand your requirement so going to play it back to you:

A report which has the amount paid (sum) of expenses per a heading per a day including when a heading has no expense on a day.

If so you need to get a set of all the dates plus the existing table of expense headers and then OUTER JOIN them to the actual expenses. OUTER JOIN will result in getting results where there are no expenses of a particular heading on a particular day. Then you can GROUP BY with a SUM to get the actual amounts per a day.

If so the SQL would look something like:

SELECT d.date, h.expenseHeadID, h.expenseHeadName, SUM(e.cashPaid)
FROM CashBookExpenseHeadings h
LEFT OUTER JOIN CashBookExpenses e ON h.expenseHeadID = e.cashExpenseHead_ID 
RIGHT OUTER JOIN (SELECT DISTINCT date FROM CashBookExpenses) d ON e.Date = d.Date
GROUP BY d.date, h.expenseHeadID, h.expenseHeadName

If that is not what you need could you clarify.

PhilS
  • 624
  • 3
  • 5
  • Thanks for response, you got the idea but I what i want to do is to show number of cloumns equal to total number of Expense Heads and with one additional column of Date. – Abdul Salam Dec 20 '18 at 05:35
  • Like a single row or record Shows all expenses in their specific expense headings columns of per Date. – Abdul Salam Dec 20 '18 at 05:37
  • I'd recommend you do the transformation to a crosstab representation on the client side as its probably easier. However if you want to do it in the database you can use PIVOT. Unless the headers are static and you can write them directly into the query you'll need to create some dynamic sql - see https://stackoverflow.com/q/18914735/3805124 – PhilS Dec 20 '18 at 10:11