July 31, 2014 at 8:12 am
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?
July 31, 2014 at 8:55 am
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
July 31, 2014 at 9:00 am
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
------------------------------
July 31, 2014 at 9:26 am
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
July 31, 2014 at 9:36 am
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
July 31, 2014 at 9:45 am
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.
July 31, 2014 at 10:25 am
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
July 31, 2014 at 10:38 am
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!
July 31, 2014 at 10:46 am
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)
August 4, 2014 at 6:55 am
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
August 4, 2014 at 10:35 am
Hi Kurt,
Can't see anything syntactically wrong with the DISTINCT, is it throwing an error or just not returning the results you'd expect?
As for 'run for every month to get a count', not quite sure what you're after there, something like this?
(I've added the @Start and @end variables, they can be removed if you want to consider all dates)
SELECT YEAR( ticket_history.create_time ) , MONTH( ticket_history.create_time ) , COUNT()
FROM `ticket`
INNER JOIN ticket_history ON ticket.id = ticket_history.ticket_id
WHERE ticket_history.state_id =2
AND freetext7 = 'small'
AND ticket_history.create_time >= @start
AND ticket_history.create_time < @end
GROUP BY YEAR( ticket_history.create_time ) , MONTH( ticket_history.create_time )
ORDER BY YEAR( ticket_history.create_time ) , MONTH( ticket_history.create_time )
I did get your email over the weekend, sorry I haven't responded to it yet, will do tonight.
Cheers
Gaz
August 5, 2014 at 9:05 am
I am having problems with a alias.
Can someone shed some light on this for me?
SELECT 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
INNER JOIN ticket_history ON ticket_state.id
WHERE ticket_state.id.create_time = 1 > ticket_state.id.create_time = 2
AND ticket_state.id.create_time=10 > ticket_state.id.create_time = 2
AND ticket_state_id IN (1,4,6,11,12,13,14,15,16,17,18,23,24)
AND freetext7 = 'small'
AND MONTH(ticket_history.create_time) =7
AND YEAR(ticket_history.create_time) =2014
ORDER BY `ticket`.`tn` ASC
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy