Switch query from user input - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Switch query from user input

Author

Topic

Alain_TV
Starting Member

12 Posts

Posted - 2015-01-09 : 06:50:19

Hi All,

I have a query that runs on MS Report Builder 3, in which the user has the option to select a day, month, year and certain text from a field to return specific data.

This query works fine, however, I wish to be able to add the ability, of not using any date ranges within the same query, given the option to return any data matching the text criteria entered, for any date found.

Is there a way to achieve this within the same query?

I have tried few options, but it returns null, which is not a valid entry for the query. I have also tried to cast/convert the date field into string, to avoid null, however it then returns a date range that appears to be the default lowest date that MS SQL works with.

Any ideas?

Query as follow:

SELECT
(resultA.MonitoredPoint) As AlarmPoint, (resultA.DateTimeStamp) As FromTime, (resultB.DateTimeStamp) As ToTime,
DATEDIFF(MINUTE, min(resultA.DateTimeStamp), min(resultB.DateTimeStamp)) As ActiveTime

FROM
(

SELECT
tbAlarmsEvents.MonitoredPoint
,tbAlarmsEvents.UniqueAlarmId
,tbAlarmsEvents.AlarmState
,tbAlarmsEvents.MonitoredValue
,tbAlarmsEvents.[Count]
,tbAlarmsEvents.AlarmText
,tbAlarmsEvents.DateTimeStamp
FROM
tbAlarmsEvents
WHERE AlarmText Like '%' + (@AlarmText) + '%'
AND tbAlarmsEvents.MonitoredValue = '1'
AND DATEPART(day, tbAlarmsEvents.DateTimeStamp)=@Sel_Day
AND DATEPART(month, tbAlarmsEvents.DateTimeStamp)=@Sel_Month
AND DATEPART(year, tbAlarmsEvents.DateTimeStamp)=@Sel_Year

) As resultA,

(
SELECT
tbAlarmsEvents.MonitoredPoint
,tbAlarmsEvents.UniqueAlarmId
,tbAlarmsEvents.AlarmState
,tbAlarmsEvents.MonitoredValue
,tbAlarmsEvents.[Count]
,tbAlarmsEvents.AlarmText
,tbAlarmsEvents.DateTimeStamp
FROM
tbAlarmsEvents
WHERE AlarmText Like '%' + (@AlarmText) + '%'
AND tbAlarmsEvents.MonitoredValue = '0'
AND DATEPART(day, tbAlarmsEvents.DateTimeStamp)=@Sel_Day
AND DATEPART(month, tbAlarmsEvents.DateTimeStamp)=@Sel_Month
AND DATEPART(year, tbAlarmsEvents.DateTimeStamp)=@Sel_Year
) As resultB

Where
(
resultA.UniqueAlarmId = resultB.UniqueAlarmId
AND resultA.[Count] = resultB.[Count]
)
Group by resultA.MonitoredPoint, resultA.DateTimeStamp, resultB.DateTimeStamp

Thanks for your help and time.

Alain

Regards,
Alain

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 08:45:33

Let @option be a bit. When @option <> 1, use the date parameters; when @option = 1, ignore the date parameters. Then write your WHERE clause like this:


WHERE @option=1 
   OR 
  (
       DATEPART(day, tbAlarmsEvents.DateTimeStamp)=@Sel_Day
   AND DATEPART(month, tbAlarmsEvents.DateTimeStamp)=@Sel_Month
   AND DATEPART(year, tbAlarmsEvents.DateTimeStamp)=@Sel_Year
   )

AlainTV
Starting Member

5 Posts

Posted - 2015-01-12 : 19:41:29

Hi gbritton,

Thanks so much for your help. Worked really good :)

Regards,

Alain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources