Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trying to add a date parameter in Report Builder 3.0 Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 8:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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?
Post #1598293
Posted Thursday, July 31, 2014 8:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
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
Post #1598310
Posted Thursday, July 31, 2014 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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
------------------------------
Post #1598313
Posted Thursday, July 31, 2014 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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
Post #1598331
Posted Thursday, July 31, 2014 9:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
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
Post #1598336
Posted Thursday, July 31, 2014 9:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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.
Post #1598343
Posted Thursday, July 31, 2014 10:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
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
Post #1598365
Posted Thursday, July 31, 2014 10:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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!
Post #1598375
Posted Thursday, July 31, 2014 10:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 2,068, Visits: 3,599
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)
Post #1598383
Posted Monday, August 4, 2014 6:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:04 AM
Points: 22, 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

Post #1599240
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse