SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to add a date parameter in Report Builder 3.0


Trying to add a date parameter in Report Builder 3.0

Author
Message
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
I have the following code:


Select create_time
From ticket_history
As CreateDate



This of course works the way it supposed to and lists every ticket.

I need to add a date parameter similar to the following the parameter is @BeginningDate


SELECT TransactionTypeCd, BookDt
FROM spi.dbo.AccountStats
WHERE TransactionTypeCd = 'FlatCancel'
AND
BookDt BETWEEN (@StartDate) AND (@EndDate)



I have tried adding a Where CreateDate = @BeginnignDate but that does not work.

Can someone help me with the syntax on this?
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 5843
Hi,
Think this is what you're after? If not, please give us more detail on what's not working, e.g. any errors you receive, or incorrect results.

Select create_time
From ticket_history
where create_time = @BeginningDate


Note in your first piece of code you're aliasing the table as Create_Date, not the field, not sure if that's what you're trying to do?

Thanks
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
Thanks for the prompt reply.

I tried this:


Select create_time,
From ticket_history
WHERE create_time = @BeginningDate



And I got this error message:

TITLE: Microsoft SQL Server Report Builder
------------------------------

An error occurred while executing the query.
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From ticket_history
WHERE create_time = @BeginningDate' at line 2

------------------------------
ADDITIONAL INFORMATION:

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From ticket_history
WHERE create_time = @BeginningDate' at line 2 (myodbc3.dll)

------------------------------
BUTTONS:

OK
------------------------------
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
OK I tried this again:

Select create_time
From ticket_history
WHERE create_time = @BeginningDate



and it just returns the column name and no values
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 5843
Ok, that's fixed the syntax error.

What are the datatypes of the create_time field and the @BeginningDate variable, and what value of @BeginningDate are you using?

Thanks
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
Thanks for the reply.

The data types of the column are DATETIME in the format mm/dd/yyy hh:mm:ss and I am using the Date Picker in Report Builder 3.0 for the @BeginningDate.

If you need more info please let me know.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 5843
Thanks.
If the values in create_time contain a non-midnight time portion you won't get any matches, e.g.
create_time 07/31/2014 17:13:00 does not match 07/31/2014 00:00:00 which you'll get from the date picker.

If you change the query to one of these hopefully you'll get some results:

Select create_time
From ticket_history
WHERE cast(create_time as date) = @BeginningDate

Select create_time
From ticket_history
WHERE cast(cast(create_time as date) as datetime) = @BeginningDate

Note this isn't SARGable so if the ticket_history table is large it might get slow.
This might perform better:

Select create_time
From ticket_history
WHERE create_time >= @BeginningDate
and create_time < DATEADD(DD, 1, @BeginningDate)

(Or change DATEADD(DD, 1, @BeginningDate to DATE_ADD(@BeginningDate, INTERVAL 1 DAY) for MySQL which I think you're querying here)

Cheers
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
The first two work but they just return the column header.

Somehow we are not selecting(*) in the first line of the statement.

Many many thanks for your efforts so far.

This is the error I get on the last line

Select create_time
From ticket_history
WHERE create_time >= @BeginningDate
and create_time < DATEADD(DD, 1, @BeginningDate)

TITLE: Microsoft SQL Server Report Builder
------------------------------

An error occurred while executing the query.
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]FUNCTION otrs.DATEADD does not exist

------------------------------
ADDITIONAL INFORMATION:

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.41-3ubuntu12.10]FUNCTION otrs.DATEADD does not exist (myodbc3.dll)

------------------------------
BUTTONS:

OK
------------------------------


Cheers!
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 5843
No problem.
As you're querying MySQL, try this:

Select create_time
From ticket_history
WHERE create_time >= @BeginningDate
and create_time < DATE_ADD(@BeginningDate, INTERVAL 1 DAY)
kdnicholsster
kdnicholsster
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 54
This code works except for the DISTINCT. Please note from earlier posts this is MYSQL.

Also how can I create a create to run for every month to get a count?


SELECT DISTINCT ticket.tn, ticket.id, ticket_history.ticket_id, ticket_history.state_id, ticket_history.create_time, MONTH( ticket_history.create_time ) , YEAR( ticket_history.create_time )
FROM `ticket`
INNER JOIN ticket_history ON ticket.id = ticket_history.ticket_id
WHERE ticket_history.state_id =2
AND freetext7 = 'small'
AND MONTH( ticket_history.create_time ) =1
AND YEAR( ticket_history.create_time ) =2014
ORDER BY `ticket`.`tn` ASC


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search