[SQL] طلب تجميع استعلام - نسخة قابلة للطباعة

كود :

DECLARE @d1 datetime 

set @d1 = '01.31.2021'





Select Distinct(ID_Prdct),Products,



 (StartQntt+COALESCE((select sum(Quantite)from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation >10),0))- 

 COALESCE((select sum(Quantite) 

 from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation<=10),0)as Reste,Prix_Achat, 

 ((StartQntt+COALESCE((select sum(Quantite)from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation >10),0))- 

 COALESCE((select sum(Quantite) 

 from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation<=10),0))*Prix_Achat as Decompte  

 from TB_Product 

 full JOIN TB_Sell_Buy on TB_Sell_Buy.ID_products = TB_Product.ID_Prdct  order by Products

كود :

     Connect()

        If sqlCnn.State = ConnectionState.Closed Then sqlCnn.Open()



        Try

            Dim da1 As New SqlDataAdapter("select Distinct(Op_Date) as Date from TB_Sell_Buy order by Op_Date ", sqlCnn)

 

            dt1.Clear()

            da1.Fill(dt1)

            dt1.Columns.Add("Stocke")

            da1.Dispose()

            For Each rw As DataRow In dt1.Rows

                Try

                    Dim da2 As New SqlDataAdapter("Select ID_Prdct, " & _

                    "cast(((StartQntt+COALESCE((select sum(Quantite)from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation >10),0))- " & _

                    " COALESCE((select sum(Quantite)from TB_Sell_Buy where TB_Sell_Buy.ID_products = TB_Product.ID_Prdct and Op_Date <= @d1 and Code_opperation<=10),0))*Prix_Achat as Decimal(18,2)) as Decompte " & _

                    " from TB_Product " & _

                    " full JOIN TB_Sell_Buy on TB_Sell_Buy.ID_products = TB_Product.ID_Prdct " & _

                    " group by ID_Prdct,StartQntt,Prix_Achat ", sqlCnn)



                    dt2.Clear()

                    da2.SelectCommand.Parameters.AddWithValue("@d1", rw(0))

                    da2.Fill(dt2)



                    rw(1) = dt2.Compute("SUM(Decompte)", "")



                Catch ex As Exception

                    MsgBox(Err.Description, MsgBoxStyle.Information)

                End Try

            Next

            da2.Dispose()

            dt2.Clear()

            dt2.Dispose()

            DataGridView1.DataSource = dt1

           

        Catch ex As Exception

            MsgBox(Err.Description, MsgBoxStyle.Information)

        End Try



        If sqlCnn.State = ConnectionState.Open Then sqlCnn.Close()