0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

declare @CustOrder table
(
    id int,
    CreateDate date
 )
 declare @DenominationOrder table
 (
   id int,
   DenID int,
   OrderID int
 )
 declare @Denomination table
 (
    id int,
    amount decimal(12,2)
 )

--    order 1 total denominations 3, total amount = 250+250+250=750
--    order 2 total denominations 2, total amount = 250+250=500


insert into @CustOrder VALUES(1, '2012-04-01'), (2, '2012-04-02');
insert into @Denomination VALUES(1, 250), (2, 250), (3, 250), (4, 250), (5, 250);
insert into @DenominationOrder VALUES(1, 1, 1),
                                     (2, 2, 1),
                                     (3, 3, 1),
                                     (4, 4, 2),
                                     (5, 5, 2);

SELECT o.CreateDate, COUNT(o.id), SUM(d.amount) AS 'Total Amount' 
        FROM  @CustOrder o 
        INNER JOIN @DenominationOrder do ON o.id = do.OrderID
        INNER JOIN @Denomination      d  ON do.DenId = d.id
        GROUP BY o.CreateDate

view current version

edited apr 11 12

created apr 11 12 Mahmoud Gamal