Filter In-DB - Filter data by Latest Date

[PKoya]

Hi,

I am trying to use the In-DB tools for the first time and using MS SQL Server. 

Database I am using contains records for each month. Date is always the 1st of every month.

What query do I use to filter the date column by latest date?

Thank you!!!

[echuong1]

This looks like it's working for me. Can you try the below?

select *
from TicketSales
where TicketSales.EventDate =
(
select Max(TicketSales.EventDate)
from TicketSales
)

[AngelosPachis]

Thanks for that @echuong1 ,

I'm trying a similar syntax in a Microsoft SQL Server and I still get an error that No Column was returned 

but the column is there

Any idea why this might be?

Thanks,

Angelos

[echuong1]

Try wrapping Order Date in brackets so it looks like this [Order Date]. SQL doesn't like spaces in field names 🙂 

[AngelosPachis]

Can I mark this as a solution @echuong1 ? 🙄

It worked !!  Τhank you for that 🥳

[BrandonB]

His query is working properly for me. Can you provide the error message that you are seeing? 

Edit: ah sorry, didn't see the other responses. Glad you two figured it out!

[PKoya]

I am still receiving an error when trying this out but when I am removing the sub-query and just clicking on test query its working.

Not sure why this isn't working for me.

[TomWelgemoed]

Hi @PKoya ,

That's helpful. What that proves is that your database connection (and table) specification is fine, but that there is an issue with the date column or syntax.

Can you run this query and see if it succeeds/fails?

Select [Reporting Period]

from dbo.YourTableName

If the above succeeds, then there must be a tiny error in the syntax somewhere. I noticed a full stop at the end of your query (see below) and am not sure if that is perhaps the issue?

Best,
Tom

[PKoya]

Hi @TomWelgemoed,

Ran the workflow and it ran without a problem.

The full stop you mentioned is actually the end of the line at the bottom of the letter L.

[TomWelgemoed]

Hi @PKoya ,

Ah, so not a full-stop then! 🙂 Ignore that then. 

I must admit, I'm stumped - the query then looks perfect to me and I cannot see why it won't succeed. The only remaining thing I can think of given the error (Unspecified error), is that it's something with the in-database tool itself or the connection. Could you try the same thing in a "standard" input tool in the SQL Editor?

I also saw this comment in another thread that may be helpful:

Due to this - my first instinct now whenever getting a DB error on SQL which does not behave the same in a standard query window, is to switch to an ODBC connnection, and make sure that you're using the latest ODBC driver from Microsoft (I think that the SQL Server Native v. 11 still ships with SQL 2016).    This solves 90% of our weird DB errors without any additional steps 

Hope either helps!

Regards,

Tom