Posted By

konami99 on 10/28/11


Tagged


Versions (?)

sql technique


 / Published in: SQL
 

  1. CREATE TABLE referrers
  2. AS (
  3. SELECT NULL AS agebracket, `referred_members`.`DateOfBirth` AS `DateOfBirth`,((YEAR(CURDATE()) - YEAR(`referred_members`.`DateOfBirth`)) - (RIGHT(CURDATE(),5) < RIGHT(`referred_members`.`DateOfBirth`,5))) AS `age`,`referred_members`.`FullName` AS `Name`,`referred_members`.`DateCreated` AS `DateCreated`,`ourdeal_backup`.`states`.`StateName` AS `StateName`,`referred_members`.`Gender` AS `Gender`,`referred_members`.`Postcode` AS `Postcode`, SUM((`ourdeal_backup`.`orders`.`Quantity` * `ourdeal_backup`.`orders`.`Price`)) AS `GDV`, COALESCE(((`ourdeal_backup`.`deals`.`Margin` / 100) * SUM(`ourdeal_backup`.`orders`.`Amount`)),0) AS `NetRevenue`, COUNT(`ourdeal_backup`.`orders`.`OrderID`) AS `OrderCount`
  4. FROM ((((`ourdeal_backup`.`member_referrals`
  5. JOIN `ourdeal_backup`.`members` `referred_members` ON((`referred_members`.`Email` = `ourdeal_backup`.`member_referrals`.`ReferralEmail`)))
  6. JOIN `ourdeal_backup`.`orders` ON((`ourdeal_backup`.`orders`.`MemberID` = `referred_members`.`MemberID`)))
  7. JOIN `ourdeal_backup`.`deals` ON((`ourdeal_backup`.`deals`.`DealID` = `ourdeal_backup`.`orders`.`DealID`)))
  8. JOIN `ourdeal_backup`.`states` ON((`ourdeal_backup`.`states`.`StateID` = `referred_members`.`StateID`)))
  9. GROUP BY `referred_members`.`DateOfBirth`,((YEAR(CURDATE()) - YEAR(`referred_members`.`DateOfBirth`)) - (RIGHT(CURDATE(),5)<RIGHT(`referred_members`.`DateOfBirth`,5))),`referred_members`.`FullName`,`referred_members`.`DateCreated`,`ourdeal_backup`.`states`.`StateName`,`referred_members`.`Gender`,`referred_members`.`Postcode`
  10. );

Report this snippet  

You need to login to post a comment.