F
Fir5tSight
Hi All,
I have a "SELECT" statement in a stored procedure that looks like the
follows:
--------------------------------------------------------------------------------------------------------------
SELECT
CASE WHEN ( ri.Status NOT LIKE '%COMPLETE%' ) -- report not
completed
THEN 1
WHEN ( ri.Status NOT LIKE '%COMPLETE%' AND
ri.UpdateDate = '' ) -- report not printed
THEN 2
WHEN (ri.Status LIKE '%COMPLETE%' AND
(ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
dbo.VWdisPrintReceipts.UpdateDate = '') -- report not scanned
THEN 3
WHEN (ri.Status LIKE '%COMPLETE%' AND
( ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
( dbo.VWdisPrintReceipts.UpdateDate <> ''
AND dbo.VWdisPrintReceipts.UpdateDate <= GetDate())) -- report
completed, printed, and scanned
THEN 4
END AS 'Status',
CASE WHEN ( ri.Status NOT LIKE '%COMPLETE%' ) -- report not
completed
THEN 'Report not completed'
WHEN ( ri.Status NOT LIKE '%COMPLETE%' AND
ri.UpdateDate = '' ) -- report not printed
THEN 'report not printed'
WHEN (ri.Status LIKE '%COMPLETE%' AND
(ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
dbo.VWdisPrintReceipts.UpdateDate = '') -- report not scanned
THEN 'Report not scanned'
WHEN (ri.Status LIKE '%COMPLETE%' AND
( ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
( dbo.VWdisPrintReceipts.UpdateDate <> ''
AND dbo.VWdisPrintReceipts.UpdateDate <= GetDate())) -- report
completed, printed, and scanned
THEN 'report completed, printed, and scanned'
END AS 'Reason' -- Reason for report not being reconciled
--------------------------------------------------------------------------------------------------------------
As you can see, the two "CASE" statements are almost redundant except
that the first selects an integer, and the second selects a text
string.
Is there anyway to avoid the redundancy in the two "CASE" statements?
Thanks!
-Emily
I have a "SELECT" statement in a stored procedure that looks like the
follows:
--------------------------------------------------------------------------------------------------------------
SELECT
CASE WHEN ( ri.Status NOT LIKE '%COMPLETE%' ) -- report not
completed
THEN 1
WHEN ( ri.Status NOT LIKE '%COMPLETE%' AND
ri.UpdateDate = '' ) -- report not printed
THEN 2
WHEN (ri.Status LIKE '%COMPLETE%' AND
(ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
dbo.VWdisPrintReceipts.UpdateDate = '') -- report not scanned
THEN 3
WHEN (ri.Status LIKE '%COMPLETE%' AND
( ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
( dbo.VWdisPrintReceipts.UpdateDate <> ''
AND dbo.VWdisPrintReceipts.UpdateDate <= GetDate())) -- report
completed, printed, and scanned
THEN 4
END AS 'Status',
CASE WHEN ( ri.Status NOT LIKE '%COMPLETE%' ) -- report not
completed
THEN 'Report not completed'
WHEN ( ri.Status NOT LIKE '%COMPLETE%' AND
ri.UpdateDate = '' ) -- report not printed
THEN 'report not printed'
WHEN (ri.Status LIKE '%COMPLETE%' AND
(ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
dbo.VWdisPrintReceipts.UpdateDate = '') -- report not scanned
THEN 'Report not scanned'
WHEN (ri.Status LIKE '%COMPLETE%' AND
( ri.UpdateDate <> '' AND ri.UpdateDate <= GetDate()) AND
( dbo.VWdisPrintReceipts.UpdateDate <> ''
AND dbo.VWdisPrintReceipts.UpdateDate <= GetDate())) -- report
completed, printed, and scanned
THEN 'report completed, printed, and scanned'
END AS 'Reason' -- Reason for report not being reconciled
--------------------------------------------------------------------------------------------------------------
As you can see, the two "CASE" statements are almost redundant except
that the first selects an integer, and the second selects a text
string.
Is there anyway to avoid the redundancy in the two "CASE" statements?
Thanks!
-Emily