Creating a View with detailed informations

Hello,
I have a code for creating view in T-SQL. I want to ask you guys, i want to make this result set should grouped by DepoAdi column and StokKodu (this is an alias sure you can get it from code). Did i make it on group by line? My second problem is i want to add 2 columns to this query. This 2 column will calculate some values with SUM function and - operator. At CRM.Depolar.DepoBilgileri table i have a column named Miktar (this one stores int type datas) and i have a column named islemturu(this one stores 1 or 0). I want to calculate Miktar values which rows has islemturu column 0 and subtract them from which rows has islemturu column 1 value and this computing action must be based on Grouped columns.

DepoAdi | StokKodu | Miktar | IslemTuru
ABS SK101 5 0
ABS SK101 3 1
ABS SK102 4 0
ABS SK102 3 1

This is the table and i'm imaging view what i want now...

DepoAdi | StokKodu | Miktar
ABS SK101 2
ABS SK102 1

How can i add this resultset to my code..

Thanks for reading... Waiting your answers.. Happy coding...

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT DepoBilgileri

FROM [CRM.Depolar]

WHERE Id IN (SELECT Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT ObjeGrubu

FROM [CRM.Objeler]

WHERE Id IN (SELECT Id

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE Id IN (SELECT KartBilgileri

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

Select DepoAdi

, StokKodu

, (Giren - Cikan) As Miktar

From

(

Select Depo.DepoAdi

, Depo.StokKodu

, Sum(Depo.Miktar) As Giren

, 0 As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 0

Group By Depo.DepoAdi, Depo.StokKodu

Union All

Select Depo.DepoAdi

, Depo.StokKodu

, 0 As Giren

, Sum(Depo.Miktar) As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 1

Group By Depo.DepoAdi, Depo.StokKodu

) As Core

|||

Thanks for your reply.. I solved problem with making some changes in my code.

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Miktar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Miktar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Miktar,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Tutar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Tutar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Tutar

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT [CRM.Depolar].DepoBilgileri

FROM [CRM.Depolar]

WHERE [CRM.Depolar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT [CRM.Objeler].ObjeGrubu

FROM [CRM.Objeler]

WHERE [CRM.Objeler].Id IN (SELECT [CRM.StokKartlar].Id

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT [CRM.StokKartlar.KartBilgileri].StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE [CRM.StokKartlar.KartBilgileri].Id IN (SELECT [CRM.StokKartlar].KartBilgileri

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartlar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu