January 24th, 2014, 07:01 AM
Help with SQL date period query
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