We have to run every week a SQL Server query taking into accounts transactions starting from the latest Saturday at 00:01 until Saturday at 23:59 (Kuwait Time). The process takes into account thousands of rows and is there fore very heavy. I decided to do it in two steps, mostly because i could not manage to make it into a single one;)
I first have to offset the SQL Server date column of each transaction to Kuwait time.
I proceed in two steps since I use this script via PHP: I first define the latest Saturday date range (so for example, if the query is run on a Saturday it will calculate the range for the previous Saturday), and then use the variables for the second script.
Here here my first query getting the last Saturday :
SELECT
(datetime, CASE
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Sunday'
THEN DATEADD(DAY, -1, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Monday'
THEN DATEADD(DAY, -2, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Tuesday'
THEN DATEADD(DAY, -3, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Wednesday'
THEN DATEADD(DAY, -4, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Thursday'
THEN DATEADD(DAY, -5, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Friday'
THEN DATEADD(DAY, -6, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Saturday'
THEN DATEADD(DAY, -0, CAST(CURRENT_TIMESTAMP AS date))
END) at time zone 'Arab Standard Time' AS start_time,
CONVERT(datetime,
CASE
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN dateadd(day,0,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN dateadd(day,-1,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Tuesday' THEN dateadd(day,-2,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Wednesday' THEN dateadd(day,-3,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Thursday' THEN dateadd(day,-4,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Friday' THEN dateadd(day,-5,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Saturday' THEN dateadd(day,-6,cast(CURRENT_TIMESTAMP AS date))
END) at time zone 'Arab Standard Time' as end_time
then I insert the variable $start_date and $end_date coming from above query in this other query to get the final result:
SELECT
USERID,
DEPOSIT,
RMwin,
CASE
WHEN DEPOSIT > 0
AND RMwin <= DEPOSIT
AND RMwin > 0 THEN
RMWIN * 0.3
WHEN RMwin > DEPOSIT
AND DEPOSIT > 0 THEN
DEPOSIT *.3
WHEN RMwin <= 0 THEN
0 ELSE 0
END AS CB,
CASE
WHEN DEPOSIT + RMwin = 0 THEN
0 ELSE 1
END AS check_,
FORMAT ( min_lt, 'dd/MM/yy HH:mm' ) AS min_lt,
format ( min_kuw, 'dd/MM/yy HH:mm' ) AS min_kuw,
format ( max_lt, 'dd/MM/yy HH:mm' ) AS max_lt,
format ( max_kuw, 'dd/MM/yy HH:mm' ) AS max_kuw,
min_id,
max_id
FROM
(
SELECT
UserID,
SUM ( DEPOSIT ) AS DEPOSIT,
SUM ( RMwin ) AS RMwin,
MIN ( AccountTranID ) AS min_id,
MAX ( AccountTranID ) AS max_id,
MIN ( local_time ) AS min_lt,
MIN ( kuwait_date ) AS min_kuw,
MAX ( local_time ) AS max_lt,
MAX ( kuwait_date ) AS max_kuw
FROM
(
SELECT
AccountTranID,
TranType,
UserID,
datetime,
datetime AT TIME ZONE 'Romance Standard Time' AS local_time,
datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' AS kuwait_date,
CASE
TranType
WHEN 'GAME_B' THEN
- AmountReal
WHEN 'GAME_W' THEN
- AmountReal
WHEN 'REFUND' THEN
- AmountReal ELSE 0
END AS RMwin,
CASE
WHEN TranType = 'DEPOSIT' THEN
AmountReal ELSE 0
END AS DEPOSIT
FROM
admin_all.DataFeed
WHERE
datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' >= ( convert(datetime,'".$start_date."') AT TIME ZONE 'Arab Standard Time' )
AND datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' < ( convert(datetime,'".$end_date."') AT TIME ZONE 'Arab Standard Time' )
) trans
GROUP BY
UserID
) ok
ORDER BY
CB DESC
This query is unfortunately extremely heavy for our server resources and I am trying to find a way to streamline it to reach the same result.
Would anyone have a solution to make that possible?
