گرفتن خروجی سفارشی از دو جدول متفاوت

ضمن عرض سلام و خسته نباشید

و تشکر از پاسخ شما

من سعی کردم کوری که شما نوشته بودید رو با join دوباره بنویسمش ولی نمی دونم چرا ردیف آخر که باید total رو حساب کنه

یا دقیقا مشابه ستون Sum میشه یا جمع کل رو توی همه ستون ها می زنه  لطفا راهنمایی کنید

با تشکر

کوئری اول

SELECT aa.Name,
    aa.Familly,
    aa.StartDate AS StartDate,
    CASE WHEN aa.StartDate = aa.EndDate THEN NULL ELSE aa.EndDate END AS EndDate ,
    SUM(aa.Price) AS Sum,
    aa.Total
FROM (
SELECT P.Name,
  P.Familly,
  CONVERT(DATE,T.TransactionDate,111) AS StartDate
  ,Maxx.endDate AS EndDate,
  T.Price,
  SUM(T.Price) OVER (PARTITION BY Maxx.PersonId,Maxx.endDate ORDER BY P.Name) AS Total
FROM dbo.Person_TBL P
JOIN
dbo.Transaction_TBL T
ON T.PersonId = P.PersonId
JOIN
(SELECT TM.PersonId,MAX(CONVERT(DATE,TM.TransactionDate,111)) AS endDate FROM dbo.Transaction_TBL TM GROUP BY TM.PersonId) Maxx
ON Maxx.PersonId = P.PersonId
JOIN
(SELECT TM.PersonId,Min(CONVERT(DATE,TM.TransactionDate,111)) AS stDate FROM dbo.Transaction_TBL TM GROUP BY TM.PersonId) Minn
ON Minn.PersonId = P.PersonId
) aa
GROUP BY aa.Name,aa.Familly,aa.StartDate,aa.EndDate,aa.Total

ORDER BY aa.Name DESC

----------------------------------

کوئری دوم

SELECT *,SUM(bb.Sum) OVER(PARTITION BY bb.Name,bb.StartDate,bb.EndDate ORDER BY bb.Name)  FROM(
SELECT aa.Name,
    aa.Familly,
    aa.StartDate AS StartDate,
    CASE WHEN aa.StartDate = aa.EndDate THEN NULL ELSE aa.EndDate END AS EndDate ,
       SUM(aa.Price) AS Sum
FROM (
SELECT P.Name,
  P.Familly,
  CONVERT(DATE,T.TransactionDate,111) AS StartDate
  ,Maxx.endDate AS EndDate,
  T.Price AS Price
FROM dbo.Person_TBL P
JOIN
dbo.Transaction_TBL T
ON T.PersonId = P.PersonId
JOIN
(SELECT TM.PersonId,MAX(CONVERT(DATE,TM.TransactionDate,111)) AS endDate FROM dbo.Transaction_TBL TM GROUP BY TM.PersonId) Maxx
ON Maxx.PersonId = P.PersonId
JOIN
(SELECT TM.PersonId,Min(CONVERT(DATE,TM.TransactionDate,111)) AS stDate FROM dbo.Transaction_TBL TM GROUP BY TM.PersonId) Minn
ON Minn.PersonId = P.PersonId
) aa
GROUP BY aa.Name,aa.Familly,aa.StartDate,aa.EndDate
) bb
GROUP BY bb.Name,bb.Familly,bb.StartDate,bb.EndDate,bb.Sum
ORDER BY bb.Name DESC

با سلام

ضمن تشکر از پاسخ شما

باید بگم که این روش رو امتحان کرده بودم ولی به جواب نرسیدم!

همون طورکه توی سوال گفتم خروجی دقیقا باید عین جدول سوم بشه البته فقط جواب نهایی مهمه و نه راه حل!

چند سوال و نکته:

اولا تاجایی که فهمیدم تاریخ شروع رو باید ثابت و بدون تغییر از جدول Transaction_TBL فراخوانی کرد و قرار داد

دوما اینکه نمی دونم چطوری باید برای تاریخ پایان که به ازای هر شخص (یا هر آی دی) مجموع خرید های شخص در یک

روز براساس تاریخ روز را  نشان می دهد را مشخص کرد

البته فکر کنم باید بازه تاریخ رو از اولین تاریخ به ازای هر شخص تا تاریخ بعدی خرید همان شخص باشد که نمی دونم چطور باید این کار رو انجام بدم؟

سلام دوست عزیز .. فکر کنم کوئری باید اینطور باشه :

SELECT p.Name, p.Family, Min(CONVERT(DATE,TransactionDate,111)) StDate ,
Max(CONVERT(DATE,TransactionDate,111)) EnDate , SUM(T.Price) AS Price 
FROM dbo.Person_TBL P , dbo.Transaction_TBL T 
where p.PersonId=T.PersonId
GROUP BY rollup(p.Name ,p.Family ,CONVERT(DATE,TransactionDate,111))
ORDER BY P.Name DESC

جمع price به ازای هر روز نشون داده میشه و بعد جمع کلی rollout میشه و در انتها نمایش داده میشه 

ضمن عرض سلام و خسته نباشید

فقط می تونم بگم یک دنیا ممنون دم شما گرم خیلی زحمت کشیدید

اما چند سوال من خیلی سرچ کردم ولی نتونستم کاملش کنم از کد قبلی بهتر شد ولی به جواب درستی که شما نوشتید نرسیدم

اینم کوئری من:

SELECT * FROM (
 SELECT 
 --pp.PersonId,
  PP.Name,
  PP.Familly,
  CAST(TT.TransactionDate AS Date) StartDate,
  CAST(TT.TransactionDate AS Date) EndDate,
  ROW_NUMBER() OVER (PARTITION BY PP.PersonId,TT.TransactionDate ORDER BY PP.Familly) prn,
  ROW_NUMBER() OVER (PARTITION BY PP.PersonId,TT.TransactionDate ORDER BY PP.Familly) prn2
 FROM dbo.Person_TBL PP
 JOIN 
 dbo.Transaction_TBL TT ON TT.PersonId = PP.PersonId
 --UNION
 --SELECT * FROM dbo.Transaction_TBL
)AS query
PIVOT (MAX(StartDate) FOR prn IN([1])) as pvt
PIVOT (MAX(EndDate) FOR prn2 IN([2])) as pvt2

سوالم در مورد موارد کاربرد کلمه کلیدی pivot هستش

1- ظاهرا در قسمت اولش فقط میشه aggregate function استفاده کرد و در قسمت دوم باید از کلمه های کلیدی For/Over استفاده کرد و بعد بازه مورد پردازش که معمولا با توابع رنکینگ تولید میشه و بعد نام یا نامهای تولید شیده براساس مجموعه وارد شده که میشه خروجی رو تولید کرد؟ لطفا کمی در مورد موارد کاربردش توضیح بدید؟

2- اینکه آیا می توان با استفاده از join کردن و روش cross apply دو تیبل اصلی و اولیه رو به الصاق کرد و بعد جدول نهایی رو تولید کرد؟

SELECT 
 p.Name,
 p.Familly,
 (SELECT CAST(T.TransactionDate AS DATE)) AS StartDate ,
 (SELECT CAST(T.TransactionDate AS DATE)) AS EndDate ,
 (ROW_NUMBER() OVER(PARTITION BY P.Name ORDER BY P.Familly))  AS Row_Num,
 (SELECT COUNT(*) FROM dbo.Transaction_TBL),
 SUM(T.Price) AS Price,
 SUM(T.Price) AS Total
FROM dbo.Person_TBL P OUTER APPLY
(SELECT * FROM  dbo.Transaction_TBL 
WHERE  p.PersonId=PersonId
)T
 GROUP BY p.Name,p.Familly,Price,TransactionDate
ORDER BY P.Name DESC

3- روش With Test_Name AS هم در صورت امکان توضیح بدید با تشکر

WITH  Persons_List AS
 (
 SELECT 
 p.PersonId,
 p.Name,
 p.Familly
 FROM dbo.Person_TBL P 
 GROUP BY p.Name,P.Familly,P.PersonId
),
Transaction_List AS
(
 SELECT 
 T.PersonId,
 (CAST(T.TransactionDate AS DATE)) AS StartDate ,
 MAX((CAST(T.TransactionDate AS DATE))) AS EndDate ,
 --COUNT(T.PersonId) AS Count_ID,
 SUM(T.Price) AS Price,
    SUM(T.Price) AS Total
 FROM dbo.Transaction_TBL T
 GROUP BY T.PersonId,T.TransactionDate 
)
SELECT
PL.Name,PL.Familly,TL.StartDate,TL.EndDate,tl.Price,TL.Total
FROM Persons_List AS PL JOIN Transaction_List AS TL
ON PL.PersonId = TL.PersonId
GROUP BY PL.Name,PL.Familly,TL.StartDate,TL.EndDate,TL.Price,TL.Total
ORDER BY PL.Name DESC

با تشکر فراوان

سلام دوست عزیز .. مورد استفاده از pivot تو گزارش گیری هست .. موقعی که ما می خواهیم مقادیر داده ها مون رو از توی ستون ها به Header گزارش تبدیل کنیم (توی خیلی جاها بهش cross tab کوئری هم میگن) .. بطور خیلی ساده aggregate کردن مقادیر سطر ها و نشون دادن اونها در قالب ستون های جدا .. این شکل فکر کنم کاملا واضح باشه برای کارکرد pivot ..

1. توی pivot ما از for و یک تابع aggregate استفاده می کنیم .. از for برای درست کردن ستون ها و تابع aggregate  برای نشون دادن جمع مقادیر داده ها در سطر و زیر مجموع ستون مشخص .

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

-----------------------

2- اینکه آیا می توان با استفاده از join کردن و روش cross apply دو تیبل اصلی و اولیه رو به الصاق کرد و بعد جدول نهایی رو تولید کرد؟

اره می تونی و باید از sub query هم استفاده کنی