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

Creating a group at run-time

I have a request for a report to allow the user to render the output grouped
by start of last name (A's, B's, C's, etc) and have each group print on its
own page. This action would be controlled from a report parameter ideally
and the user would be able to toggle from normal output to this grouped
style. Creating a new group on the first character of the last name and
setting Page Break after was easy. The problem that I am having is how to
enforce the toggling of styles. The Visibility property of the group doesn't
appear to do what I want. Can groups be created at run-time? What is the
best way to achieve what I am after?
ThanksPlease check this article about dynamically changing groupings in a report:
http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
In your particular situation, you would want to group on the first character
of the last name or group on a constant value in the other case (read step 3
in the article carefully).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:C3BEEC01-9974-49B6-A2C8-94084374CBC5@.microsoft.com...
> I have a request for a report to allow the user to render the output
grouped
> by start of last name (A's, B's, C's, etc) and have each group print on
its
> own page. This action would be controlled from a report parameter ideally
> and the user would be able to toggle from normal output to this grouped
> style. Creating a new group on the first character of the last name and
> setting Page Break after was easy. The problem that I am having is how to
> enforce the toggling of styles. The Visibility property of the group
doesn't
> appear to do what I want. Can groups be created at run-time? What is the
> best way to achieve what I am after?
> Thanks