Logo

Merhaba,

Destek aldığınız logo partner firması kim ise onun yapması gereken derinlikte bir konu, forum üzerinden birisinin oturup bu şekilde tamamen size özel bir konuda cevap vermesi çok doğru olmaz, ama genel bir konudur, bir cevap yazılması sonradan okuyacak üyelerede yardımcı olur o zaman mantıklı olur.

Merhaba,

Arkadaşlar açmış olduğum bu konuya ilişkin bulduğum çözümü sizlerle paylaşmak isterim.Uzun bir query oldu ama istediğim sonucu Northwind database üzerinde tam da istediğim gibi aldım.Bunu sizlerle paylaşmak ve ihtiyacınız olduğunuz da size de yol gösterebilir niyeti ile paylaşmak isterim.

 SELECT EMP.EmployeeID, EMP.FirstName, cust.CustomerID, CUST.CompanyName,
(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1996-01-01 00:00:00', 20)
AND convert(DATETIME, '1996-12-31 00:00:00', 20)) AS Q1Q2Q3_1996,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) AS Q1Q2Q3_1997,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-04-30 00:00:00', 20)) AS Q1_1997,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-05-01 00:00:00', 20)
AND convert(DATETIME, '1997-08-31 00:00:00', 20)) AS Q2_1997,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-09-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) AS Q3_1997,

'30' AS HEDEF_ORANI,

((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) AS CIRO_1998_HEDEF,

(SELECT ISNULL(ISNULL(SUM(ORD.Freight),0),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-01-01 00:00:00', 20)
AND convert(DATETIME, '1998-04-30 00:00:00', 20)) + (SELECT ISNULL(ISNULL(SUM(ORD.Freight),0),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-05-01 00:00:00', 20)
AND convert(DATETIME, '1998-08-30 00:00:00', 20)) + (SELECT ISNULL(ISNULL(SUM(ORD.Freight),0),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-09-01 00:00:00', 20)
AND convert(DATETIME, '1998-12-31 00:00:00', 20)) AS CIRO_1998,

((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3 AS Q1_1998,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-01-01 00:00:00', 20)
AND convert(DATETIME, '1998-01-31 00:00:00', 20)) AS Q1_OCAK,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-02-01 00:00:00', 20)
AND convert(DATETIME, '1998-02-28 00:00:00', 20)) AS Q1_SUBAT,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-03-01 00:00:00', 20)
AND convert(DATETIME, '1998-03-31 00:00:00', 20)) AS Q1_MART,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-04-01 00:00:00', 20)
AND convert(DATETIME, '1998-04-30 00:00:00', 20)) AS Q1_NISAN,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-01-01 00:00:00', 20)
AND convert(DATETIME, '1998-04-30 00:00:00', 20)) AS Q1_1998_TOPLAM,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-01-01 00:00:00', 20)
AND convert(DATETIME, '1998-04-30 00:00:00', 20)) - (((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3) AS Q1_FARK,

((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3 AS Q2_1998,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-05-01 00:00:00', 20)
AND convert(DATETIME, '1998-05-31 00:00:00', 20)) AS Q2_MAYIS,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-06-01 00:00:00', 20)
AND convert(DATETIME, '1998-06-30 00:00:00', 20)) AS Q2_HAZIRAN,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-07-01 00:00:00', 20)
AND convert(DATETIME, '1998-07-31 00:00:00', 20)) AS Q2_TEMMUZ,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-08-01 00:00:00', 20)
AND convert(DATETIME, '1998-08-31 00:00:00', 20)) AS Q2_AGUSTOS,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-05-01 00:00:00', 20)
AND convert(DATETIME, '1998-08-31 00:00:00', 20)) AS Q2_1998_TOPLAM,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-05-01 00:00:00', 20)
AND convert(DATETIME, '1998-08-31 00:00:00', 20)) - (((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3) AS Q2_FARK,

((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3 AS Q3_1998,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-09-01 00:00:00', 20)
AND convert(DATETIME, '1998-09-30 00:00:00', 20)) AS Q3_EYLUL,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-10-01 00:00:00', 20)
AND convert(DATETIME, '1998-10-31 00:00:00', 20)) AS Q3_EKIM,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-11-01 00:00:00', 20)
AND convert(DATETIME, '1998-11-30 00:00:00', 20)) AS Q3_KASIM,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-12-01 00:00:00', 20)
AND convert(DATETIME, '1998-12-31 00:00:00', 20)) AS Q3_ARALIK,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-09-01 00:00:00', 20)
AND convert(DATETIME, '1998-12-31 00:00:00', 20)) AS Q3_1998_TOPLAM,

(SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1998-09-01 00:00:00', 20)
AND convert(DATETIME, '1998-12-31 00:00:00', 20)) - (((SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) * 30 / 100) + (SELECT ISNULL(SUM(ORD.Freight),0)
FROM dbo.Orders AS ORD
WHERE
EMP.EmployeeID = ORD.EmployeeID
AND CUST.CustomerID = ORD.CustomerID
AND ORD.ShippedDate
BETWEEN convert(DATETIME, '1997-01-01 00:00:00', 20)
AND convert(DATETIME, '1997-12-31 00:00:00', 20)) / 3) AS Q3_FARK

FROM dbo.Orders AS ORD
LEFT OUTER JOIN dbo.Customers CUST ON ORD.CustomerID = CUST.CustomerID
LEFT OUTER JOIN dbo.Employees EMP ON ORD.EmployeeID = EMP.EmployeeID
GROUP BY EMP.EmployeeID, EMP.FirstName, CUST.CompanyName, cust.CustomerID
ORDER BY EMP.FirstName

Yukarıda ki query bir view içerisinde listelediğinizde dışarıdan göndereceğiniz EmployeeID değerine göre size ilgili satıcının bağlı olduğu firmalar ve bu firmalara yıllar, aylar içerisinde yaptığı toplam satış ve ciro listesini size sunacaktır.

Çıktısı aşağıda ki gibidir;
[img] [img]

 

Süper olmuş, eline sağlık.

Merhaba,

Öncelikle kendim için elimden geleni yapmaya çalıştım bu konuda, inşallah başka arkadaşlara da lazım olur kullanırlar.Rica ederim.

Size son bir sorum daha olucak.Yukarıda ki mantık ile query'i Procedure çevirdim ve sayfama çektim sadece 1 adet kayıt döndükten sonra "Nesne başvurusu bir nesnenin örneğine ayarlanmadı." hatası alıyorum.Bunun null kontrolünü yaparsam sıkıntıyı gidericem diye düşünüyorum da bu konuda nasıl bir yol izleyebilirim yada bir kod örneği iletebilir misiniz?

 Teşekkür ederim, kolay gelsin.

TAMAMDIR.ÇÖZDÜM SORUNU.

 SAYFA İÇERİSİNDE TANIMALDIĞIM LABEL DEĞERLERİNİ ALAMDIĞINDAN DOLAYI DOLDURMAYA ÇALIŞIYOR ITEMDATABOUND'DA BU SEBEPLE SIKINTI YARATIYOR.ŞUAN DA ÇALIŞIYOR.

TEŞEKKÜRLER.

Kendin çalıyorsun kendin oynuyorsun gibi oldu 🙂 eline sağlık.

Haklısınız, buraya soru yazarken aynı zamanda uğraşmaya devam ettiğim için çözümü de bulunca paylaşıyım dedim. 🙂 

Süpersin.