MySQL Forums
Forum List  »  Oracle

Help with intervals
Posted by: Craig Blakemore
Date: January 24, 2014 06:51AM

So I currently have the query below, all works fine and gives the output we want, but the line

(DATEDIFF(TransactionProcessedDateTime, FirstDepositDateTime) - 1) DIV 28 AS MON,

Gives us the first month deposits, then follows along fine for deposits for the 28 day periods after that. all works fine, but we want to tweak it a bit so it gives the first 15 days then every 30 days after that

So the div 28 somehow needs to be replaced to run 15 once, then continue doing 30 for the rest.

SELECT Date_Format(PlayerBookkeeping.FirstDepositDateTime,"%M,%Y") as FirstDeposit,(DATEDIFF(TransactionProcessedDateTime, FirstDepositDateTime) - 1) DIV 28 AS MON, PlayerAccounts.AffiliateID,
COUNT(DISTINCT CASE WHEN TransactionDepositAmount > 0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount) / 100,
SUM(TransactionWithdrawAmount) / 100, BrandID
PlayerAccounts, TransactionLogs, PlayerBookkeeping, Affiliates
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID AND PlayerAccounts.AffiliateID = Affiliates.AffiliateID
#AND FirstDepositDateTime between "FirstDeposit"
and PlayerAccounts.AffiliateID = 2975

GROUP BY (DATEDIFF(TransactionProcessedDateTime, FirstDepositDateTime) - 1) DIV 28, AffiliateID, FirstDeposit;

any help will be appreciated


Options: ReplyQuote

Written By
Help with intervals
January 24, 2014 06:51AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.