Click here to Skip to main content
15,891,567 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Have a nice day;

I do not know english, I use to google translate, I apologize if there is a translation error.

Thanks in advance for your help.

To create GridView3
what kind of query should I write.

asp.net,C#,sql experss,I am broadcasting local.

gridview1-table1(sql)/gridview2-table2(sql)/gridview3-query ?

incoming table
column1 -column2
product1 -5 kg
product2 -3 kg
product2 -4 kg
product1 -3 kg

Outgoing table
column1-column2
product1-3 kg
product2-3 kg
product1-4 kg
product2-2 kg

gridview3 result
column1-column2
product1-1 kg
product2-2 kg

What I have tried:

SqlDataSource3.SelectCommand = "WITH    entries AS( SELECT marka, kimyasal_kodu, kimyasal_adi, SUM(kilo) AS kilo FROM byh_kimyasal_giris_tbl GROUP BY marka, kimyasal_kodu, kimyasal_adi), exits AS(SELECT marka, kimyasal_kodu, kimyasal_adi, SUM(kilo) AS kilo FROM byh_kimyasal_kullanim_tbl GROUP BY marka, kimyasal_kodu, kimyasal_adi )SELECT g.marka, g.kimyasal_kodu, g.kimyasal_adi, g.kilo, COALESCE(c.kilo, 0) AS kilo, g.kilo - COALESCE(c.kilo, 0) AS remaining FROM entries g LEFT JOIN exits c ON g.marka = c.marka AND g.kimyasal_kodu = c.kimyasal_kodu AND g.kimyasal_adi = c.kimyasal_adi";

GridView3.DataSource = SqlDataSource3;
GridView3.DataBind();
Posted
Updated 19-Feb-18 22:58pm

Hi Try Below query

DECLARE @IncommingT1 AS TABLE (C1 VARCHAR(64), Data int)
DECLARE @OutGoingT2 AS TABLE (C1 VARCHAR(64), Data int)

INSERT INTO @IncommingT1
(
    C1,
    Data
)
VALUES
(   'product1', -- C1 - varchar(64)
    5   -- Data - int
),(   'product2', -- C1 - varchar(64)
    3   -- Data - int
),(   'product2', -- C1 - varchar(64)
    4   -- Data - int
),(   'product1', -- C1 - varchar(64)
    3   -- Data - int
);
INSERT INTO @OutGoingT2
(
    C1,
    Data
)
VALUES
(   'product1', -- C1 - varchar(64)
    3   -- Data - int
),(   'product2', -- C1 - varchar(64)
    3   -- Data - int
),(   'product1', -- C1 - varchar(64)
    4   -- Data - int
),(   'product2', -- C1 - varchar(64)
    2   -- Data - int
)

SELECT it.C1,(SUM(DISTINCT it.Data)) - (SUM(DISTINCT ogt.Data)) AS C2  FROM  @IncommingT1 AS it 
LEFT JOIN @OutGoingT2 AS ogt ON ogt.C1 = it.C1
GROUP BY it.C1,ogt.c1
 
Share this answer
 
MS SQL Management Studioda is working the result is correct but aspx.cs also gives incorrect results.

"incomming_tbl.kilo (12+3+5)" - "outgoing_tbl.kilo(2+2)"= 16 but "result=18" the result is wrong.

Where am I making the mistake?

C#
SqlDataSource1.SelectCommand = "SELECT Incomming_tbl.colorName,(SUM(DISTINCT Incomming_tbl.kilo)) - (SUM(DISTINCT OutGoing_tbl.kilo)) AS kilo FROM Incomming_tbl AS Incomming_tbl LEFT JOIN OutGoing_tbl AS OutGoing_tbl ON OutGoing_tbl.colorName = Incomming_tbl.colorName GROUP BY Incomming_tbl.colorName,OutGoing_tbl.colorName";
 
Share this answer
 
Comments
khanabrar 20-Feb-18 5:19am    
Try this
SqlDataSource1.SelectCommand = "SELECT Incomming_tbl.colorName,((SUM(DISTINCT Incomming_tbl.kilo)) - (SUM(DISTINCT OutGoing_tbl.kilo))) AS kilo
FROM Incomming_tbl AS Incomming_tbl
LEFT JOIN OutGoing_tbl AS OutGoing_tbl ON OutGoing_tbl.colorName = Incomming_tbl.colorName
GROUP BY Incomming_tbl.colorName,OutGoing_tbl.colorName";

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900