About summarizing data from twol tables SQL

Question

0.00/5 (No votes)

See more:

Hello CodeProject.

I Have 5 Tables in My DataBase.
1.Accounts
2. Arrival Master
3. Arrival Details
4. Return Master
5. Retrun Details

Table Account

Partyid             Title
225                 Mushtaq
2211                Akram

Table Arrival Master

ArrivalID         Adate         RefDate        PartyID
1                 24-Sep        24-Sep          225
2                 25-Sep        25-Sept         2211

Table Arrival Details

ArrivalID          ItemID       ItemQty          ItemAMount
1                  1              10              500
1                  11             25              230
2                  10             45              70
2                  25             1               90
2                  1              5               25

Table ReturnMaster

ReturnID        REtDate       RefDate      PartyID
r1              24-Sep        24-Sep       225
R2              25-Sep        25-Sep       2211

Table ReturnDetails

ReturnID         ItemID        ItemQty    ItemAMount
1                50              10       250
1                51              20       350
2                50              9        70
2                51              8        60

I want to get Summary of Single Date For Example 24-Sep Summary. Data Should Be Like This:

ArrivalDate      ArrivalRefDate      ArrivalID      PartyTitle      ArrivalAmount       ReturnAmount
24-Sep           24-Sep               1              225            730                 600

Keep in mind that return data should be linked with arrival RefDate and Arrival PArty Title or ParyID.

data of a party should not go to other Party's row based on Main date. Where Refdate and PArtyID is same in Arrivalmaster And returnMaster. that data should be in One Row.

What I have tried:

I can get result in two different query results. but i am unable to combine result in sigle row.

Posted 24-Sep-19 21:12pm

Ghayyas Awan Mahtaab

Updated 25-Sep-19 0:27am

Maciej Los

v4

Add a Solution

Comments

Maciej Los 25-Sep-19 3:23am

Post your data instead of image.

Ghayyas Awan Mahtaab 25-Sep-19 3:53am

Table Account
Partyid Title
225 Mushtaq
2211 Akram

Table Arrival Master
ArrivalID Adate RefDate PartyID
1 24-Sep 24-Sep 225
2 25-Sep 25-Sept 2211

Table Arrival Details
ArrivalID ItemID ItemQty ItemAMount
1 1 10 500
1 11 25 230
2 10 45 70
2 25 1 90
2 1 5 25

Table ReturnMaster

ReturnID REtDate RefDate PartyID
r1 24-Sep 24-Sep 225
R2 25-Sep 25-Sep 2211

Table ReturnDetails
ReturnID ItemID ItemQty ItemAMount
1 50 10 250
1 51 20 350
2 50 9 70
2 51 8 60

I want to get Summary of Single Date For Example 24-Sep Summary. Data Should Be Like This

ArrivalDate ArrivalRefDate ArrivalID PartyTitle ArrivalAmount ReturnAmount
24-Sep 24-Sep 1 225 730 600

Keep in mind that return data should be linked with arrival RefDate and Arrival PArty Title or ParyID.

data of a party should not go to other Party's row based on Main date. Where Refdate and PArtyID is same in Arrivalmaster And returnMaster. that data should be in One Row.

Ghayyas Awan Mahtaab 25-Sep-19 3:59am

I cant Adjust Spaces in Data. Sorry i am new to Forum.

Maciej Los 25-Sep-19 4:11am

Don't worry. I've done this for you. You should use tags, like <pre>some piece of code here</pre>

Ghayyas Awan Mahtaab 25-Sep-19 4:27am

Thanx Dude. Can I Get Solution from You?

1 solution

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 · Accepted Answer · 2019-09-25T00:27:00

This is bit complicated due to the relationship between Arrivals and Returns has been set only on PartyId. There's no exact relationship between specifc arrival and return.

At this moment i was able to achieve that by using below query:

SQL

DECLARE @Accounts TABLE(Partyid INT, Title NVARCHAR(250))
INSERT INTO @Accounts(Partyid, Title)
VALUES(225, 'Mushtaq'), (2211, 'Akram');

DECLARE @ArrivalMaster TABLE(ArrivalID INT, Adate DATETIME, RefDate DATETIME, PartyID INT)
INSERT INTO @ArrivalMaster(ArrivalID, Adate, RefDate, PartyID)
VALUES(1, '2019-09-24', '2019-09-24', 225), (2, '2019-09-25', '2019-09-25', 2211);

DECLARE @ArrivalDetails TABLE(ArrivalID INT, ItemID INT, ItemQty INT, ItemAMount INT)
INSERT INTO @ArrivalDetails(ArrivalID, ItemID, ItemQty, ItemAMount)
VALUES(1, 1, 10, 500), (1, 11, 25, 230), (2, 10, 45, 70), (2, 25, 1, 90), (2, 1, 5, 25);

DECLARE @ReturnMaster TABLE(ReturnID INT, REtDate DATETIME, RefDate DATETIME, PartyID INT)
INSERT INTO @ReturnMaster(ReturnID, REtDate, RefDate, PartyID)
VALUES(1, '2019-09-24', '2019-09-24', 225), (2, '2019-09-25', '2019-09-25', 2211);

DECLARE @ReturnDetails TABLE(ReturnID INT, ItemID INT, ItemQty INT, ItemAMount INT)
INSERT INTO @ReturnDetails(ReturnID, ItemID, ItemQty, ItemAMount)
VALUES(1, 50, 10, 250), (1, 51, 20, 350), (2, 50, 9, 70), (2, 51, 8, 60);


SELECT am.ArrivalID, am.Adate ArrivalDate, am.RefDate ArrivalRefDate, a.Partyid PartyTitle, rm.ReturnID, ad.ArrivalAmount, rd.ReturnAmount
FROM @Accounts a
	LEFT JOIN @ArrivalMaster am ON a.Partyid = am.PartyID 
	LEFT JOIN (
			SELECT ArrivalID, SUM(ItemAmount) ArrivalAmount
			FROM @ArrivalDetails 
			GROUP BY ArrivalID
		) ad ON am.ArrivalID  = ad.ArrivalID
	LEFT JOIN @ReturnMaster rm ON a.Partyid = rm.PartyID 
	LEFT JOIN (
			SELECT ReturnID, SUM(ItemAMount) AS ReturnAmount
			FROM @ReturnDetails 
			GROUP BY ReturnID
		) rd ON rm.ReturnID  = rd.ReturnID