Need help in sql query

Question

0.00/5 (No votes)

See more:

Hi all

below is my 3 tables

tbl_recieved

r_amt       r_date
500.00     "2014-01-23 15:55:29"
100.00     "2014-01-29 14:01:03"
3000.00    "2013-11-18 14:04:01"
3330000    "2013-11-19 17:14:09"

tbl_bill

b_amt      b_date
228.00  "2014-01-24 10:15:31"
198.00  "2014-01-27 11:12:49"
165.00  "2014-01-28 16:27:38"
165.00  "2014-01-27 11:39:26"
279.00  "2014-01-27 15:25:18"
198.00  "2014-01-29 10:18:08"

tbl_paid_out

p_amt     p_date
3000.00  "2013-11-18 16:37:01"
100000   "2013-11-25 16:24:17"
1000.00  "2014-01-23 15:55:10"
100.00   "2014-01-29 14:00:16"
960000   "2013-11-19 17:14:28"

now what i have to get is i have to check all the tables and if they have same value in date column i have to return that row through below calculation

(sum(tbl_recieved.recieved_amount) + sum(tbl_transaction_master.bill_net_total) - sum(tbl_paidout.paid_amount)) as petty_cash

please tell me how to do this..

i have to display the petty cash after sum of all the amount having common date

Posted 28-Jan-14 21:46pm

Snehasish_Nandy

Add a Solution

Comments

RAHUL(10217975) 29-Jan-14 5:53am

What is the relation between these tables?? How can I join them for grouping ?

[no name] 29-Jan-14 23:33pm

there is a common date in all the 3 tables i.e. 2014-01-29

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAHUL(10217975) · Answer 1 · 2014-01-29T00:03:00

If relation is only Date then try this query, it should work

SQL

With X (Date1,Amount)
AS
(
Select CONVERT(varchar,Date,105),Amount as Date1 from table_2
),
Y (Date1,Amount)
AS
(
Select CONVERT(varchar,Date,105),Amount as Date1 from table_3
)
select SUM (X.Amount) + SUM (X.Amount),X.Date1 as 's' from X
inner Join Y ON X.Date1=Y.Date1
Group BY X.Date1

Ahsann90 · Answer 2 · 2014-01-29T00:08:00

SQL

You need to join the tables to get the result. In the above given data, there is no date where all three tables have same matching date.

select ISNULL((sum(r.received_amount) + sum(t.bill_net_total) - sum(p.paid_amount)),0) as petty_cash

from tbl_recieved r inner join tbl_transaction_master t  on r.received_date = t.bill_date inner join tbl_paid_out p on r.received_date= p.paid_date

Need help in sql query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0