- Katılım
- 16 Haziran 2007
- Mesajlar
- 56
- Excel Vers. ve Dili
- 2003 türçe
Stok hareketlerindeki Tarih ve hareket olan kayıtlar var bunları gruplayıp toplayarak toplanan aylarıda sütunlarda göstermek isityorum
Gruplama ve toplama yaptırdım ama sorunum var nasıl yapacağımı bilemiyorum
örnek
a grubu ocak
a grubu şubat
a grubu mart
diye satır atlıyor bunu nasıl engellerim komutlarım aşağıdaki gibidir
yapmak istediğim
a grubu ocak - şubat - mart - nisan .. aylarının toplamlarını yan yana yazdırmak
SELECT
LG_029_ITEMS.STGRPCODE AS 'GRUP KODU',
LG_029_01_STLINE.TRCODE AS 'KOD',
OCAK = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=1
then SUM(LG_029_01_STLINE.AMOUNT)
END,
SUBAT = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=2
then SUM(LG_029_01_STLINE.AMOUNT)
END,
MART = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=3
then SUM(LG_029_01_STLINE.AMOUNT)
END,
NİSAN = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=4
then SUM(LG_029_01_STLINE.AMOUNT)
END,
MAYIS = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=5
then SUM(LG_029_01_STLINE.AMOUNT)
END,
HAZİRAN = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=6
then SUM(LG_029_01_STLINE.AMOUNT)
END,
TEMMUZ = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=7
then SUM(LG_029_01_STLINE.AMOUNT)
END,
AGUSTOS= CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=8
then SUM(LG_029_01_STLINE.AMOUNT)
END,
EYLUL = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=9
then SUM(LG_029_01_STLINE.AMOUNT)
END,
EKİM = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=10
then SUM(LG_029_01_STLINE.AMOUNT)
END,
KASIM = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=11
then SUM(LG_029_01_STLINE.AMOUNT)
END,
ARALIK = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=12
then SUM(LG_029_01_STLINE.AMOUNT)
END
FROM
LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE,
LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE
LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF
AND
((LG_029_01_STLINE.TRCODE In (3,8))
AND
(LG_029_ITEMS.CARDTYPE=12)
AND
(LG_029_ITEMS.STGRPCODE Like '25%'))
GROUP BY
DATEPART(mm,LG_029_01_STLINE.DATE_),
LG_029_01_STLINE.TRCODE,
LG_029_ITEMS.STGRPCODE
ORDER BY
LG_029_ITEMS.STGRPCODE
NOT:
RAPORLAMA YAPACAK KADAR SQL BİLGİM VARDIR ONA GÖRE TARİF EDİNİZİ
Gruplama ve toplama yaptırdım ama sorunum var nasıl yapacağımı bilemiyorum
örnek
a grubu ocak
a grubu şubat
a grubu mart
diye satır atlıyor bunu nasıl engellerim komutlarım aşağıdaki gibidir
yapmak istediğim
a grubu ocak - şubat - mart - nisan .. aylarının toplamlarını yan yana yazdırmak
SELECT
LG_029_ITEMS.STGRPCODE AS 'GRUP KODU',
LG_029_01_STLINE.TRCODE AS 'KOD',
OCAK = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=1
then SUM(LG_029_01_STLINE.AMOUNT)
END,
SUBAT = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=2
then SUM(LG_029_01_STLINE.AMOUNT)
END,
MART = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=3
then SUM(LG_029_01_STLINE.AMOUNT)
END,
NİSAN = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=4
then SUM(LG_029_01_STLINE.AMOUNT)
END,
MAYIS = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=5
then SUM(LG_029_01_STLINE.AMOUNT)
END,
HAZİRAN = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=6
then SUM(LG_029_01_STLINE.AMOUNT)
END,
TEMMUZ = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=7
then SUM(LG_029_01_STLINE.AMOUNT)
END,
AGUSTOS= CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=8
then SUM(LG_029_01_STLINE.AMOUNT)
END,
EYLUL = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=9
then SUM(LG_029_01_STLINE.AMOUNT)
END,
EKİM = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=10
then SUM(LG_029_01_STLINE.AMOUNT)
END,
KASIM = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=11
then SUM(LG_029_01_STLINE.AMOUNT)
END,
ARALIK = CASE when DATEPART(mm,LG_029_01_STLINE.DATE_)=12
then SUM(LG_029_01_STLINE.AMOUNT)
END
FROM
LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE,
LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE
LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF
AND
((LG_029_01_STLINE.TRCODE In (3,8))
AND
(LG_029_ITEMS.CARDTYPE=12)
AND
(LG_029_ITEMS.STGRPCODE Like '25%'))
GROUP BY
DATEPART(mm,LG_029_01_STLINE.DATE_),
LG_029_01_STLINE.TRCODE,
LG_029_ITEMS.STGRPCODE
ORDER BY
LG_029_ITEMS.STGRPCODE
NOT:
RAPORLAMA YAPACAK KADAR SQL BİLGİM VARDIR ONA GÖRE TARİF EDİNİZİ
Ekli dosyalar
-
52.4 KB Görüntüleme: 24
Son düzenleme: