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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply