Hi,
I'm hoping someone can help, because my knowledge of commands is severely limited!
I have the following command, and all I want to do is remove the two parameter fields, datefrom and datetill, and replace them with the current date, the datetill parameter, and the current date -7 days, the datefrom parameter.
I have tried adding currentdate, an actual date and other things, but nothing seems to work!
Any ideas please?
SELECT
act.id AS action_id
, usr.name AS creditmanager
, usr.id AS creditmanager_id
, adm.terminology_text AS administration
, CASE ausr.language_code
WHEN 100
THEN actt.lang_dutch
WHEN 110
THEN actt.lang_french
WHEN 120
THEN actt.lang_german
WHEN 130
THEN actt.lang_english
WHEN 140
THEN actt.lang_spanish
WHEN 150
THEN actt.lang_portuguese
WHEN 160
THEN actt.lang_italian
/*WHEN 170
THEN actt.lang_polish*/
ELSE lang_english
END AS actiontype
, CASE actt.e_num
WHEN 1
THEN 1
WHEN 5
THEN 2
WHEN 14
THEN 3
WHEN 0
THEN 4
WHEN 21
THEN 5
WHEN 15
THEN 6
WHEN 17
THEN 7
WHEN 23
THEN 8
WHEN 26
THEN 9
WHEN 47
THEN 10
END AS actiontype_id
, CASE
WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
THEN 1
ELSE 0
END AS planned
, CASE
WHEN act.action_status IN (0, 1, 2)
AND actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
AND usr.id = act.done_by_user_id
THEN 1
ELSE 0
END AS executed
, CASE
WHEN act.action_status IN (0, 1, 2)
AND actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
AND usr.id <> act.done_by_user_id
AND NOT act.action_date_done IS NULL
THEN 1
ELSE 0
END AS executed_by_colleague
, CASE
WHEN (act.action_status IN (0, 1, 2) OR (act.action_type = 21 AND act.action_date_done IS NULL))
AND usr.id = act.done_by_user_id
THEN 1
ELSE 0
END AS done_by_collector
, CASE
WHEN (act.action_status IN (0, 1, 2) OR (act.action_type = 21 AND act.action_date_done IS NULL))
AND usr.id <> act.done_by_user_id
AND NOT act.action_date_done IS NULL
THEN 1
ELSE 0
END AS done_by_colleague
, ISNULL(act2.actions, 0) AS done_for_colleague
, act.action_status AS actionstatus_id
, act.action_date AS action_date
, act.action_date_done AS action_date_done
, repd.user_id AS user_id
, ausr.language_code AS language_code
, CASE
WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN actt.e_num IN (15, 17, 21)
THEN 1
ELSE 0
END is_profile_action
, ISNULL(act2.is_profile_action, 1) is_profile_action2
, ISNULL(ad.deleted, 0) deleted
, CASE
WHEN act.action_type IN (15, 17, 21)
THEN 'unplanned'
ELSE 'planned'
END unplanned
, cc.string_text AS controller
FROM actions act
INNER JOIN debtors deb
ON deb.id = act.debtor_id
INNER JOIN users usr
ON deb.creditmanager_id = usr.id
INNER JOIN terminology_actions actt
ON actt.e_num = act.action_type
INNER JOIN (SELECT DISTINCT administration_id, user_id FROM reports_data) repd
ON repd.administration_id = deb.administration_id
INNER JOIN (
SELECT DISTINCT deb.creditmanager_id, repd.user_id
FROM debtors deb
INNER JOIN reports_data repd
ON repd.debtor_id = deb.id
) crman
ON crman.creditmanager_id = deb.creditmanager_id
AND crman.user_id = repd.user_id
INNER JOIN active_users ausr
ON ausr.user_id = repd.user_id
INNER JOIN terminology adm
ON adm.terminology_id = deb.administration_id
AND adm.terminology_language_code = ausr.language_code
AND adm.terminology_type = 15
INNER JOIN language_strings cc
ON cc.language_code = ausr.language_code
AND cc.string_id = 486
LEFT OUTER JOIN users done_by
ON act.done_by_user_id = done_by.id
LEFT OUTER JOIN ( SELECT act.done_by_user_id, act.action_type, CASE
WHEN act.action_type IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN act.action_type IN (15, 17, 21)
THEN 1
ELSE 0
END is_profile_action, COUNT(act.id) actions
FROM actions act
INNER JOIN debtors deb
ON deb.id = act.debtor_id
AND act.done_by_user_id <> deb.creditmanager_id
WHERE CAST(LEFT(act.action_date, 11) AS DATETIME) <= {?Datetill}
AND (act.action_date_done >= {?Datefrom})
GROUP BY act.done_by_user_id, act.action_type, CASE
WHEN act.action_type IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN act.action_type IN (15, 17, 21)
THEN 1
ELSE 0
END
) act2
ON act2.action_type = act.action_type
AND act2.done_by_user_id = deb.creditmanager_id
AND act2.is_profile_action = (CASE WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN actt.e_num IN (15, 17, 21)
THEN 1
ELSE 0
END)
LEFT OUTER JOIN (
SELECT user_id, action_type, COUNT(*) deleted
FROM actions_deleted_moved adm
WHERE action_performed = 0
AND action_date BETWEEN {?Datefrom} AND {?Datetill}
GROUP BY user_id, action_type
) ad
ON ad.user_id = deb.creditmanager_id
AND ad.action_type = act.action_type
WHERE actt.e_num IN (0, 1, 5, 26, 14, 15, 17, 21, 23, 47)
AND NOT act.action_status = -2
AND CAST(LEFT(act.action_date, 11) AS DATETIME) <= {?Datetill}
AND (act.action_date_done >= {?Datefrom}
OR act.action_date_done IS NULL)
Many Thanks!
Di
I'm hoping someone can help, because my knowledge of commands is severely limited!
I have the following command, and all I want to do is remove the two parameter fields, datefrom and datetill, and replace them with the current date, the datetill parameter, and the current date -7 days, the datefrom parameter.
I have tried adding currentdate, an actual date and other things, but nothing seems to work!
Any ideas please?
SELECT
act.id AS action_id
, usr.name AS creditmanager
, usr.id AS creditmanager_id
, adm.terminology_text AS administration
, CASE ausr.language_code
WHEN 100
THEN actt.lang_dutch
WHEN 110
THEN actt.lang_french
WHEN 120
THEN actt.lang_german
WHEN 130
THEN actt.lang_english
WHEN 140
THEN actt.lang_spanish
WHEN 150
THEN actt.lang_portuguese
WHEN 160
THEN actt.lang_italian
/*WHEN 170
THEN actt.lang_polish*/
ELSE lang_english
END AS actiontype
, CASE actt.e_num
WHEN 1
THEN 1
WHEN 5
THEN 2
WHEN 14
THEN 3
WHEN 0
THEN 4
WHEN 21
THEN 5
WHEN 15
THEN 6
WHEN 17
THEN 7
WHEN 23
THEN 8
WHEN 26
THEN 9
WHEN 47
THEN 10
END AS actiontype_id
, CASE
WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
THEN 1
ELSE 0
END AS planned
, CASE
WHEN act.action_status IN (0, 1, 2)
AND actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
AND usr.id = act.done_by_user_id
THEN 1
ELSE 0
END AS executed
, CASE
WHEN act.action_status IN (0, 1, 2)
AND actt.e_num IN (0, 1, 5, 26, 14, 23, 47)
AND usr.id <> act.done_by_user_id
AND NOT act.action_date_done IS NULL
THEN 1
ELSE 0
END AS executed_by_colleague
, CASE
WHEN (act.action_status IN (0, 1, 2) OR (act.action_type = 21 AND act.action_date_done IS NULL))
AND usr.id = act.done_by_user_id
THEN 1
ELSE 0
END AS done_by_collector
, CASE
WHEN (act.action_status IN (0, 1, 2) OR (act.action_type = 21 AND act.action_date_done IS NULL))
AND usr.id <> act.done_by_user_id
AND NOT act.action_date_done IS NULL
THEN 1
ELSE 0
END AS done_by_colleague
, ISNULL(act2.actions, 0) AS done_for_colleague
, act.action_status AS actionstatus_id
, act.action_date AS action_date
, act.action_date_done AS action_date_done
, repd.user_id AS user_id
, ausr.language_code AS language_code
, CASE
WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN actt.e_num IN (15, 17, 21)
THEN 1
ELSE 0
END is_profile_action
, ISNULL(act2.is_profile_action, 1) is_profile_action2
, ISNULL(ad.deleted, 0) deleted
, CASE
WHEN act.action_type IN (15, 17, 21)
THEN 'unplanned'
ELSE 'planned'
END unplanned
, cc.string_text AS controller
FROM actions act
INNER JOIN debtors deb
ON deb.id = act.debtor_id
INNER JOIN users usr
ON deb.creditmanager_id = usr.id
INNER JOIN terminology_actions actt
ON actt.e_num = act.action_type
INNER JOIN (SELECT DISTINCT administration_id, user_id FROM reports_data) repd
ON repd.administration_id = deb.administration_id
INNER JOIN (
SELECT DISTINCT deb.creditmanager_id, repd.user_id
FROM debtors deb
INNER JOIN reports_data repd
ON repd.debtor_id = deb.id
) crman
ON crman.creditmanager_id = deb.creditmanager_id
AND crman.user_id = repd.user_id
INNER JOIN active_users ausr
ON ausr.user_id = repd.user_id
INNER JOIN terminology adm
ON adm.terminology_id = deb.administration_id
AND adm.terminology_language_code = ausr.language_code
AND adm.terminology_type = 15
INNER JOIN language_strings cc
ON cc.language_code = ausr.language_code
AND cc.string_id = 486
LEFT OUTER JOIN users done_by
ON act.done_by_user_id = done_by.id
LEFT OUTER JOIN ( SELECT act.done_by_user_id, act.action_type, CASE
WHEN act.action_type IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN act.action_type IN (15, 17, 21)
THEN 1
ELSE 0
END is_profile_action, COUNT(act.id) actions
FROM actions act
INNER JOIN debtors deb
ON deb.id = act.debtor_id
AND act.done_by_user_id <> deb.creditmanager_id
WHERE CAST(LEFT(act.action_date, 11) AS DATETIME) <= {?Datetill}
AND (act.action_date_done >= {?Datefrom})
GROUP BY act.done_by_user_id, act.action_type, CASE
WHEN act.action_type IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN act.action_type IN (15, 17, 21)
THEN 1
ELSE 0
END
) act2
ON act2.action_type = act.action_type
AND act2.done_by_user_id = deb.creditmanager_id
AND act2.is_profile_action = (CASE WHEN actt.e_num IN (0, 1, 5, 26, 14, 23, 47) AND NOT act.profile_actions_id IS NULL
THEN 1
WHEN actt.e_num IN (15, 17, 21)
THEN 1
ELSE 0
END)
LEFT OUTER JOIN (
SELECT user_id, action_type, COUNT(*) deleted
FROM actions_deleted_moved adm
WHERE action_performed = 0
AND action_date BETWEEN {?Datefrom} AND {?Datetill}
GROUP BY user_id, action_type
) ad
ON ad.user_id = deb.creditmanager_id
AND ad.action_type = act.action_type
WHERE actt.e_num IN (0, 1, 5, 26, 14, 15, 17, 21, 23, 47)
AND NOT act.action_status = -2
AND CAST(LEFT(act.action_date, 11) AS DATETIME) <= {?Datetill}
AND (act.action_date_done >= {?Datefrom}
OR act.action_date_done IS NULL)
Many Thanks!
Di