Joins with calculation?

Question

0.00/5 (No votes)

See more:

SQL

select WH.date,PN.partyname,RA.recamount,(DE.totalweight*DE.rateperkg)as totalAmount,
((DE.totalweight*DE.rateperkg)-RA.recamount)as dueamount from K_RT_WarehouseDetails WH
inner join K_RT_Dailyentryretail DE on WH.sno=DE.branchdate
inner join K_RT_AmountReceived RA on RA.partyname=DE.partyname
inner join K_RT_PartyName PN on DE.partyname=PN.sno

Out put like this:

CSS

Date                     partyname       recamount     totalamount       dueamount
  2013-12-19 00:00:00.000     ABC             5000          15000            10000
  2013-12-20 00:00:00.000     ABC             5000          15000            14000

But I want to display Output like this

CSS

Date                     partyname       recamount     totalamount       dueamount
  2013-12-19 00:00:00.000     ABC             5000          15000            10000
  2013-12-20 00:00:00.000     ABC             1000          10000             9000

Second output total amount will be change ,if we recieved amount..

Posted 21-Dec-13 19:53pm

Siva Hyderabad

Updated 22-Dec-13 17:57pm

v2

Add a Solution

Comments

André Kraak 22-Dec-13 4:17am

Would you please supply the data use to produce the result?
If the data set is to large please post a subset and adjust the output based on the subset.

Siva Hyderabad 22-Dec-13 4:35am

--------------

CREATE TABLE [dbo].[K_RT_AmountReceived](
[sno] [int] IDENTITY(1,1) NOT NULL,
[partyname] [varchar](50) NULL,
[date] [datetime] NULL,
[recamount] [float] NULL,
[chequeno] [varchar](50) NULL,
[updatedby] [varchar](50) NULL,
[updatedon] [datetime] NULL
) ON [PRIMARY]

------------------

CREATE TABLE [dbo].[K_RT_Dailyentryretail](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[branchdate] [varchar](50) NULL,
[partyname] [varchar](50) NULL,
[birdtype] [varchar](50) NULL,
[totalweight] [float] NULL,
[rateperkg] [float] NULL,
[dcno] [varchar](50) NULL,
[attrited] [bit] NULL,
[updatedon] [datetime] NULL,
[updatedby] [varchar](50) NULL
) ON [PRIMARY]

-----------------

CREATE TABLE [dbo].[K_RT_WarehouseDetails](
[sno] [int] IDENTITY(1,1) NOT NULL,
[branch] [varchar](50) NULL,
[date] [datetime] NULL,
[noofbirds] [int] NULL,
[weight] [float] NULL,
[meatyield] [float] NULL,
[remarks] [varchar](50) NULL,
[attrited] [bit] NULL,
[updatedby] [varchar](50) NULL,
[updatedon] [datetime] NULL
) ON [PRIMARY]

-----------------------------

CREATE TABLE [dbo].[K_RT_PartyName](
[sno] [int] IDENTITY(1,1) NOT NULL,
[partyname] [varchar](50) NULL,
[address] [varchar](50) NULL,
[referredby] [varchar](50) NULL,
[paymenttype] [int] NULL,
[phoneno] [varchar](50) NULL,
[amountincash] [float] NULL,
[chequeamount] [float] NULL,
[contracttype] [varchar](50) NULL,
[discount] [float] NULL,
[filename] [varchar](50) NULL,
[filepath] [varchar](50) NULL,
[fileext] [varchar](50) NULL,
[updatedby] [varchar](50) NULL,
[updatedon] [datetime] NULL
) ON [PRIMARY]

-------------------------------

André Kraak 22-Dec-13 4:40am

The table structures is a good start, but a small sample of data (records) from the tables would really be useful.

Siva Hyderabad 22-Dec-13 4:47am

Amount received Table

SNO PN DATE RECAM

53 60 2013-12-20 00:00:00.000 5000
54 60 2013-12-14 00:00:00.000 1000

DAILY ENTRY TABLE

SNO BRANCHDATE PN BIRDTYPE TOTWT RATEPERKG

125 53 60 6 1500 10

Siva Hyderabad 22-Dec-13 4:48am

WAREHOUSE DETAILS

SNO BRANCH DATE NOFBIRDS WEIGHT MEATYEILD

53 13 2013-12-19 00:00:00.000 1500 3000 1500

Siva Hyderabad 22-Dec-13 4:50am

PARTY NAME TABLE

SNO PARTYNAME

60 ABC

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)

Maciej Los · Answer 1 · 2013-12-22T20:47:00

You should use SUM() aggregate function[^]:

SQL

SELECT WH.date,PN.partyname,SUM(RA.recamount),(DE.totalweight*DE.rateperkg)as totalAmount, (DE.totalweight*DE.rateperkg)-RA.recamount) as dueamount
FROM K_RT_WarehouseDetails WH
inner join K_RT_Dailyentryretail DE on WH.sno=DE.branchdate
inner join K_RT_AmountReceived RA on RA.partyname=DE.partyname
inner join K_RT_PartyName PN on DE.partyname=PN.sno
GROUP BY WH.date, PN.partyname
ORDER BY WH.date, PN.partyname

Christian Graus · Answer 2 · 2013-12-21T20:54:00

Solution 1

Do you mean you want to do group by on the date so that you get values for each date alone instead of totals ? Without knowing what the data is, you're question makes zero sense.

Posted 21-Dec-13 20:54pm

Christian Graus