YOU ARE MY JUST A SUPERSTAR.....
JUST RAN THE SQL AND MESSAGING YOU.
LET ME GO THROUGH YOUR MAIL COMPLETELY...
YOU AGAIN PROVED YOU ARE THE MASTER-BLASTER SET...
LOVE YOU A LOT...WILL BACK TO YOU SOOOOONNNNNN....
THANK YOUUUUUUU!!!!!!!!!!!!!!!!!!!!
On Wednesday, 26 July 2017 7:05 AM, "setysvar setys...@gmail.com
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
Hi again, Vishal!
Here's a different query that will reasonably often give you what you want:
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, Order_Count ) as
( select list(Order_ID), Order_Date, Salesman_ID, count( * )
from Ordered_Book_Details
group by Order_Date, Salesman_ID
),
Maxes( Order_Date, Max_Copy_Sold, Max_ToTal_Amount, Max_Order_Count ) as
( select ct.Order_Date, max( ct.Copy_Sold ), max( ct.ToTal_Amount ), max(
o.Order_Count )
from CopyToTals ct
join Orders o on ct.Order_Date = o.Order_Date
group by ct.Order_Date
)
select c.Order_ID, c.Order_Date, c.Salesman_ID, c.Copy_Sold, c.ToTal_Amount,
t.Order_ID, t.Salesman_ID, t.Copy_Sold, t.ToTal_Amount,
o.Order_ID, o.Salesman_ID, o.Order_Count
from Maxes m
join CopyToTals c on m.Order_Date = c.Order_Date and m.Max_Copy_Sold =
c.Copy_Sold
join CopyToTals t on m.Order_Date = t.Order_Date and m.Max_ToTal_Amount =
t.ToTal_Amount
join Orders o on m.Order_Date = o.Order_Date and m.Max_Order_Count =
o.Order_Count
I say reasonably often because you may have ties, i.e. the maximum of
Copy_Sold, ToTal_Amount and/or Order_Count being reached by different
Salesman_ID. If there's only one tie, then no problem (you just get two rows),
but if you have two ties, e.g. both S001 and S002 selling 500 copies for a
total of 200000, then you will get both of them for both c and t and hence,
four rows.
Moreover, this query works nicely on the 16 rows in the test table, but I
suspect it may be too slow if there are many rows. Hence, this is a query that
I'd recommend you to consider replacing with an EXECUTE BLOCK (or a stored
procedure) if Ordered_Book_Details is a huge table.
I didn't understand why you wanted the Order_Date repeated three times for
each row, so I removed two of them.
HTH,
Set
Den 21.07.2017 10:05, skrev Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support]:
First of all Thank You for the SQL. Let me go through.
I am ready if you get enough time to look into good solution for this, and I
would wait for you. This is also different type of SQL I am seeing and feeling
happy to learn valuable things from it. Thank You Sooooo much SET. But I would
also prefer to wait for another solution. But no hurry, take your time.
Whenever you are free to look into this, please have a look.
On Friday, 21 July 2017 2:26 AM, "setysvar setys...@gmail.com
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
Den 17.07.2017 12:30, skrev Vishal Tiwari vishuals...@yahoo.co.in
[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
#yiv3023394000 #yiv3023394000 -- #yiv3023394000ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3023394000
#yiv3023394000ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3023394000
#yiv3023394000ygrp-mkp #yiv3023394000hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv3023394000 #yiv3023394000ygrp-mkp #yiv3023394000ads
{margin-bottom:10px;}#yiv3023394000 #yiv3023394000ygrp-mkp .yiv3023394000ad
{padding:0 0;}#yiv3023394000 #yiv3023394000ygrp-mkp .yiv3023394000ad p
{margin:0;}#yiv3023394000 #yiv3023394000ygrp-mkp .yiv3023394000ad a
{color:#0000ff;text-decoration:none;}#yiv3023394000 #yiv3023394000ygrp-sponsor
#yiv3023394000ygrp-lc {font-family:Arial;}#yiv3023394000
#yiv3023394000ygrp-sponsor #yiv3023394000ygrp-lc #yiv3023394000hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3023394000
#yiv3023394000ygrp-sponsor #yiv3023394000ygrp-lc .yiv3023394000ad
{margin-bottom:10px;padding:0 0;}#yiv3023394000 #yiv3023394000actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3023394000
#yiv3023394000activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3023394000
#yiv3023394000activity span {font-weight:700;}#yiv3023394000
#yiv3023394000activity span:first-child
{text-transform:uppercase;}#yiv3023394000 #yiv3023394000activity span a
{color:#5085b6;text-decoration:none;}#yiv3023394000 #yiv3023394000activity span
span {color:#ff7900;}#yiv3023394000 #yiv3023394000activity span
.yiv3023394000underline {text-decoration:underline;}#yiv3023394000
.yiv3023394000attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv3023394000 .yiv3023394000attach div a
{text-decoration:none;}#yiv3023394000 .yiv3023394000attach img
{border:none;padding-right:5px;}#yiv3023394000 .yiv3023394000attach label
{display:block;margin-bottom:5px;}#yiv3023394000 .yiv3023394000attach label a
{text-decoration:none;}#yiv3023394000 blockquote {margin:0 0 0
4px;}#yiv3023394000 .yiv3023394000bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv3023394000
.yiv3023394000bold a {text-decoration:none;}#yiv3023394000 dd.yiv3023394000last
p a {font-family:Verdana;font-weight:700;}#yiv3023394000 dd.yiv3023394000last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3023394000
dd.yiv3023394000last p span.yiv3023394000yshortcuts
{margin-right:0;}#yiv3023394000 div.yiv3023394000attach-table div div a
{text-decoration:none;}#yiv3023394000 div.yiv3023394000attach-table
{width:400px;}#yiv3023394000 div.yiv3023394000file-title a, #yiv3023394000
div.yiv3023394000file-title a:active, #yiv3023394000
div.yiv3023394000file-title a:hover, #yiv3023394000 div.yiv3023394000file-title
a:visited {text-decoration:none;}#yiv3023394000 div.yiv3023394000photo-title a,
#yiv3023394000 div.yiv3023394000photo-title a:active, #yiv3023394000
div.yiv3023394000photo-title a:hover, #yiv3023394000
div.yiv3023394000photo-title a:visited {text-decoration:none;}#yiv3023394000
div#yiv3023394000ygrp-mlmsg #yiv3023394000ygrp-msg p a
span.yiv3023394000yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3023394000
.yiv3023394000green {color:#628c2a;}#yiv3023394000 .yiv3023394000MsoNormal
{margin:0 0 0 0;}#yiv3023394000 o {font-size:0;}#yiv3023394000
#yiv3023394000photos div {float:left;width:72px;}#yiv3023394000
#yiv3023394000photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv3023394000
#yiv3023394000photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3023394000
#yiv3023394000reco-category {font-size:77%;}#yiv3023394000
#yiv3023394000reco-desc {font-size:77%;}#yiv3023394000 .yiv3023394000replbq
{margin:4px;}#yiv3023394000 #yiv3023394000ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv3023394000 #yiv3023394000ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3023394000
#yiv3023394000ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3023394000
#yiv3023394000ygrp-mlmsg select, #yiv3023394000 input, #yiv3023394000 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv3023394000
#yiv3023394000ygrp-mlmsg pre, #yiv3023394000 code {font:115%
monospace;}#yiv3023394000 #yiv3023394000ygrp-mlmsg *
{line-height:1.22em;}#yiv3023394000 #yiv3023394000ygrp-mlmsg #yiv3023394000logo
{padding-bottom:10px;}#yiv3023394000 #yiv3023394000ygrp-msg p a
{font-family:Verdana;}#yiv3023394000 #yiv3023394000ygrp-msg
p#yiv3023394000attach-count span {color:#1E66AE;font-weight:700;}#yiv3023394000
#yiv3023394000ygrp-reco #yiv3023394000reco-head
{color:#ff7900;font-weight:700;}#yiv3023394000 #yiv3023394000ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv3023394000 #yiv3023394000ygrp-sponsor
#yiv3023394000ov li a {font-size:130%;text-decoration:none;}#yiv3023394000
#yiv3023394000ygrp-sponsor #yiv3023394000ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv3023394000
#yiv3023394000ygrp-sponsor #yiv3023394000ov ul {margin:0;padding:0 0 0
8px;}#yiv3023394000 #yiv3023394000ygrp-text
{font-family:Georgia;}#yiv3023394000 #yiv3023394000ygrp-text p {margin:0 0 1em
0;}#yiv3023394000 #yiv3023394000ygrp-text tt {font-size:120%;}#yiv3023394000
#yiv3023394000ygrp-vital ul li:last-child {border-right:none
!important;}#yiv3023394000
