I've looked at as many Stack posts as I can which has gotten me as far as I have but I'm still not quite there.
I have a table which contains the dates of when a pen test is due, I'm creating a view which will show me pen tests due this quarter and next quarter.
SELECT
`pen_test_cycle`.`ptc_uid` AS `ptc_uid`,
`pen_test_cycle`.`ptc_system` AS `ptc_system`,
`pen_test_cycle`.`ptc_cycle` AS `ptc_cycle`,
`pen_test_cycle`.`ptc_start_date` AS `ptc_start_date`,
QUARTER(`pen_test_cycle`.`ptc_start_date`) AS `Qtr`,
`pen_test_cycle`.`ptc_compliance` AS `ptc_compliance`,
`inscope_systems`.`iss_uid` AS `iss_uid`,
`inscope_systems`.`system_name` AS `system_name`,
`inscope_systems`.`system_compliance` AS `system_compliance`,
QUARTER(CURDATE()) AS `current Qtr`
FROM
((`pen_test_cycle`
JOIN `inscope_systems` ON ((`pen_test_cycle`.`ptc_system` = `inscope_systems`.`iss_uid`)))
JOIN `compliance_standard` ON ((`pen_test_cycle`.`ptc_compliance` = `compliance_standard`.`cs_uid`)))
Where QUARTER(`pen_test_cycle`.`ptc_start_date`) = QUARTER(CURDATE())
or
QUARTER(`pen_test_cycle`.`ptc_start_date`) = QUARTER(CURDATE())+1
the code works fine unless we are in Q4 and then Obviously the next Qtr would be Q5 which clearly does not exist
I'm assuming the way forward is to use an "If current Qtr is 4 then Next Qtr is 1" type statement, so I came up with this (Aster several other attempts)
SELECT
`pen_test_cycle`.`ptc_uid` AS `ptc_uid`,
`pen_test_cycle`.`ptc_system` AS `ptc_system`,
`pen_test_cycle`.`ptc_cycle` AS `ptc_cycle`,
`pen_test_cycle`.`ptc_start_date` AS `ptc_start_date`,
QUARTER(`pen_test_cycle`.`ptc_start_date`) AS `Qtr`,
`pen_test_cycle`.`ptc_compliance` AS `ptc_compliance`,
`inscope_systems`.`iss_uid` AS `iss_uid`,
`inscope_systems`.`system_name` AS `system_name`,
`inscope_systems`.`system_compliance` AS `system_compliance`,
QUARTER(CURDATE()) AS `current Qtr`,
(CASE QUARTER(`pen_test_cycle`.`ptc_start_date`)
WHEN 1 Then 2
When 2 Then 3
When 3 Then 4
WHEN 4 Then 1
Else 0
End) as nqt
FROM
((`pen_test_cycle`
JOIN `inscope_systems` ON ((`pen_test_cycle`.`ptc_system` = `inscope_systems`.`iss_uid`)))
JOIN `compliance_standard` ON ((`pen_test_cycle`.`ptc_compliance` = `compliance_standard`.`cs_uid`)))
Where QUARTER(`pen_test_cycle`.`ptc_start_date`) = QUARTER(CURDATE())
or
QUARTER(`pen_test_cycle`.`ptc_start_date`) = 'nqt'
As I say I'm clearly doing something inherently wrong but I just cant find the issue, if anyone could suggest how to make this work or suggest a more sensible soluition that would be great.
Many Thanks
Aucun commentaire:
Enregistrer un commentaire