Hi BOB,
I have this date prompt query i use as both Date From and Date To,
I have now added a new option called “Fiscal Year”, but cant get it to work, keeps on complaining about the “>” sign. Can somone help me
correct this? The fiscal year for my customer is the 1st October.
My “Date from” is used to calculate the date based on the selected value and the “Date To” is always todays date. The query works fine if i remove the new Fiscal entry i have added to it…
DATE FROM
CASE @Prompt(‘1 Please Select Reporting Period’,‘A’,{‘All’,‘Heute: today 0am to now’,‘Diese Woche: Monday 0am of this week to now’,‘Dieser Monat: 1st of this month 0am to now’,‘1. des Vormonats bis heute: 1st of previous month 0am to now’,‘Dieses Kalenderjahr: last 1st January 0am to now’,‘Dieses Fiskaljahr: last 1st October 0am to now’,‘Custom’},mono,constrained,persistent,)
WHEN ‘All’ THEN DATEADD(yy, DATEDIFF(yy,0, GETDATE())-5,0)
WHEN ‘Heute: today 0am to now’ THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) WHEN ‘Diese Woche: Monday 0am of this week to now’ THEN DATEADD(wk,DATEDIFF(wk,6,GETDATE()),6)
WHEN ‘Dieser Monat: 1st of this month 0am to now’ THEN DATEADD(mm, DATEDIFF(mm,0, GETDATE()),0)
WHEN ‘1. des Vormonats bis heute: 1st of previous month 0am to now’ THEN DATEADD(mm, DATEDIFF(mm,0, GETDATE())-1,0)
WHEN ‘Dieses Kalenderjahr: last 1st January 0am to now’ THEN DATEADD(yy, DATEDIFF(yy,0, GETDATE()),0)
WHEN ‘Dieses Fiskaljahr: last 1st October 0am to now’ THEN (CASE month(getdate())>=10 THEN cast((year(getdate())) as varchar)+’-10-01’ ELSE cast((year(getdate())-1) as varchar)+’-10-01’ END)
WHEN ‘Custom’ THEN @Prompt(‘2 Enter Custom Start Date’,‘A’,mono,persistent,{‘MM/DD/YYYY’})
END
DATE TO
CASE @Prompt(‘1 Please Select Reporting Period’,‘A’,{‘All’,‘Heute: today 0am to now’,‘Diese Woche: Monday 0am of this week to now’,‘Dieser Monat: 1st of this month 0am to now’,‘1. des Vormonats bis heute: 1st of previous month 0am to now’,‘Dieses Kalenderjahr: last 1st January 0am to now’,‘Dieses Fiskaljahr: last 1st October 0am to now’},mono,constrained,persistent,)
WHEN ‘All’ THEN GETDATE()
WHEN ‘Heute: today 0am to now’ THEN GETDATE()
WHEN ‘Diese Woche: Monday 0am of this week to now’ THEN GETDATE()
WHEN ‘Dieser Monat: 1st of this month 0am to now’ THEN GETDATE()
WHEN ‘1. des Vormonats bis heute: 1st of previous month 0am to now’ THEN GETDATE()
WHEN ‘Dieses Kalenderjahr: last 1st January 0am to now’ THEN GETDATE()
WHEN ‘Dieses Fiskaljahr: last 1st October 0am to now’ THEN GETDATE()
WHEN ‘Custom’ THEN @Prompt(‘3 Enter Custom Start End’,‘A’,mono,persistent,{‘MM/DD/YYYY’})
END
This has been fixed by adding the THEN CASE ( and ) closure brackets as updated above.
Regards,
Ajay
Ajayp007 (BOB member since 2009-05-23)