Den 17.07.2017 12:30, skrev Vishal Tiwari vishualsoft@...
[firebird-support]:

> Hi All,
> As per the attache files or below specified table DDL, DML and SQL, I need to get below details:
> Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold maximum Copy for a day2. Who sold maximum amount of book for a day3. Who got maximum order for a day
> As per below SQL, I am able to fetch the result only in one record that is correct. But I need to get the data for every single date i.e. for all distinct date only, i.e. Date should not repeat in the row, which satisfy above three condition. The result could be for different Salesmen or same Salesman depending on the data in the table.
> So, I need to get data for every distinct date in the table. Please help how to get it.
> One option I see to put "Order_ID" column in Max() function to avoid Group By issue.
>
> Kindly suggest, If possible please correct the above SQL and share.
>
> ...
> ----------
>
> Get Order_ID, Order_Date, Salesman_ID for different Salesmen :
> 1. Who sold maximim Copy for a day
> 2. Who sold maximum amount of book for a day
> 3. Who got maximum order for a day
>
>
>
> With Max_Copy (Order_ID, Order_Date, Salesman_ID, Max_Copy_Sold, ToTal_Amount) As
> (Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold) As Max_Copy_Sold , Sum(ToTal_Amount)
> From Ordered_Book_Details
> Group By Order_ID, Order_Date, Salesman_ID Order By Max_Copy_Sold Desc
> ),
>
> Max_ToTal (Order_ID, Order_Date, Salesman_ID, Copy_Sold, Max_ToTal_Amount) As
> (Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold), Sum(ToTal_Amount) As Max_ToTal_Amount
> From Ordered_Book_Details
> Group By Order_ID, Order_Date, Salesman_ID Order By Max_ToTal_Amount Desc
>
> ),
>
> Max_Orders (Order_ID, Order_Date, Salesman_ID, Max_Orders_Count) As
> (Select List(Order_ID), Order_Date, Salesman_ID, Count(Salesman_ID) As Max_Orders_Count
> From Ordered_Book_Details
> Group By Salesman_ID, Order_Date Order By Max_Orders_Count Desc
> )
>
> Select First 1 Max_Copy.Order_ID, Max_Copy.Order_Date, Max_Copy.Salesman_ID, Max_Copy.Max_Copy_Sold, Max_Copy.ToTal_Amount,
> Max_ToTal.Order_ID, Max_ToTal.Order_Date, Max_ToTal.Salesman_ID, Max_ToTal.Copy_Sold, Max_ToTal.Max_ToTal_Amount,
> Max_Orders.Order_ID, Max_Orders.Order_Date, Max_Orders.Salesman_ID, Max_Orders.Max_Orders_Count
> From
> Max_Copy, Max_ToTal, Max_Orders

Hi Vishal!

This is somewhat different from what you asked for, but I hope it can
serve as a starting point.

With CopyTotals ( Order_ID, Order_Date, Salesman_ID, Copy_Sold,
ToTal_Amount ) As
( Select Order_ID, Order_Date, Salesman_ID, Sum( Copy_Sold ), Sum(
ToTal_Amount )
From Ordered_Book_Details
Group By Order_ID, Order_Date, Salesman_ID
),
Orders (Order_ID, Order_Date, Salesman_ID, Max_Orders_Count) As
( Select List( Order_ID ), Order_Date, Salesman_ID, Count( * ) As
Max_Orders_Count
From Ordered_Book_Details
Group By Salesman_ID, Order_Date
)

Select ct.Order_ID, ct.Order_Date, ct.Salesman_ID, ct.Copy_Sold,
ct.ToTal_Amount,
o.Order_ID, o.Salesman_ID, o.Max_Orders_Count
From CopyTotals ct
Join Orders o on ct.Order_Date = o.Order_Date
Where ( not exists( select * from CopyTotals ct2
where ct.Order_Date = ct2.Order_Date
and ct.Copy_Sold < ct2.Copy_Sold )
or not exists( select * from CopyTotals ct2
where ct.Order_Date = ct2.Order_Date
and ct.Total_Amount < ct2.Total_Amount ) )
and not exists( select * from Orders o2
where o.Order_Date = o2.Order_Date
and o.Max_Orders_Count < o2.Max_Orders_Count )

It returns the max copies and totals in one or two rows depending on
whether or not it is the same Order_ID that is maximum. The query will
probably be slow since the CTEs are also mentioned in the NOT EXISTs.

I'm not convinced this is an acceptable solution, but my head is a bit
too tired to come up with any good suggestion today.

HTH,
Set