Last 12 Months 'Crystal' Formula - Convert to SQL?

[Kenny100]

Hi Folks

I managed to find a nice formula for 'Crystal' that selected data from my DB that had a date within the last 12 months. I put this in the 'Select Expert' for the 'Date' field:

{MyTable.Date} in dateserial(year(currentdate - day(currentdate)),
month(currentdate-day(currentdate))-11,01) to
date(year(currentdate - day(currentdate)),
month(currentdate-day(currentdate)),day(currentdate-day(currentdate)))

Basically this acts like a moving window for data from the last 12 months.

This was all fine and dandy until today when I tried to do the same thing using a SQL statement. Does anyone know how I can replicate this in SQL?

Cheers!

 

[pandpp]

Kenny,

When you say SQL Statement, do you mean an SQL Expression or SQL Command?

A VERY important question at this point is, what DB? SQL is NOT SQL. EVERY flavour has different functions, which in this case, will be invaluable.

Peter.

 

[Kenny100]

Thanks Peter and sorry for the lack of clarity. Yes, after doing some more reading I can see that SQL varies quite a lot for different DBs.

Anyway, I'm using SQL Server 2000 (SP3). The thing is I'm not sure what the difference between a SQL Expression and a SQL Command is! Care to inform me so that I can let you know the one I'm trying to use?

 

[pandpp]

A SQL Expression is a bit like a formula. It can be displayed on the report, can be used within other formulas, including the record selection.
A SQL Expression will return just a single value.

A SQL Command is a data source, that uses straight SQL to select the records, which may contain a UNION or the like, joining tables as required.
A SQL Command will return a record set.

 

[dgillz]

On your report, click on database, show SQL query.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Kenny100]

Thanks guys. Yep, it's a SQL command I'm after. I want to limit the data coming into the report so that it is only the last 12 months I'm looking at.

 

[synapsevampire]

I think that you are trying to design architecture when you should be asking for assistance in doing so.

Why is it that you think that a SQL Command or SQL Expression is going to help? Especially since you didn't know what either was?

As dgillz alluded to, what you really want is to make sure that the SQL is passed to the database, which doesn't require either of the things you're requiring about.

SQL can be passed to the database via the Record selection formula.

If you post technical information you'll get better, more accurate results:

Crystal version (always supply your software version with any post about any product)
Database/connectivity used
Example data
Expected output

-k

 

[Kenny100]

Sorry, what? All I'm asking for is some help with designing a SQL query with the resulting record set only including data from the last 12 months.

I previously used the formula I wrote above, in 'Crystal', but I'm trying to replicate this functionality in a program called iReport and wondered if I could do the same thing using just SQL.

Sample Data:
Name Date (dd/mm/yyyy)
Apple, A 01/01/2005
Arston, J 15/03/2005
Colins, G 24/04/2005
Smith, J 01/01/2003
Tann, L 10/07/2004
Ulis, H 20/01/2004

Expected Output:
Name Date (dd/mm/yyyy)
Apple, A 01/01/2005
Arston, J 15/03/2005
Colins, G 24/04/2005
Tann, L 10/07/2004

Crystal Version: 9.2
Database: SQL Server 2000 & ODBC

Surely I'm not completely wrong in thinking that a SQL statement (pretty sure I'm looking for a Command) will solve this?

 

[synapsevampire]

OK, I guess you'd prefer to use SQL.

Just remember that Crystal creates SQL and passes it to the database, so using a SQL Command in Crystal will only limit what can be done with parameters, it doesn't buy speed normaintenance nor reusability.

If you go to the Database->Show SQL Query you'll see hat the current report is passing as SQL to the database, so you can copy and paste this into whatever this SQL thing is you intend to use instead.

Good luck with it, I'm sure that you can work it out using SQL if you insist.

-k.

 

[Kenny100]

Thanks for being so patient synapsevampire. I'd already checked that option (Database > SQL Query) and it shows me the hard-coded SQL query i.e.

SELECT "MyTable"."Name", "MyTable"."Date"
FROM "MyDB"."dbo"."MyTable" "MyTable"
WHERE ("MyTable"."Date">={ts '2004-05-01 00:00:00'} AND "MyTable"."Date"<{ts '2005-05-01 00:00:00'})
ORDER BY "MyTable"."Date"

The problem is that I need a SQL Command that will change to reflect the date that the query is being run. In other words, the query needs to act like a 12-month Moving Window, producing a record set that shows data from the last 12 months depending on what date it is run. If I simply paste the SQL above into my new report then it'll work fine for this month, but when we hit June it'll still produce the previous month's results!

Is it fair to say that I'm barking up the wrong tree? )

 

[WantMoreKnowledge]

To Kenny100, simply said I think you want the Sql date function "GETDATE ()", that will pull today's date.

To synapsevampire, lighten up dude, if you can't say anything nice get off the boards. no need to put down anybody or be sarcastic. check out your spelling and grammar before you get so high and mighty.

had to take time from my own research to join this forum just so I could say that, unncessary insensitivity annoys me to no end!

 

[pandpp]

Kenny,

I think SV's point is (and I agree with it), if you have a Record Selection formula that you are happy with & works for you, WHY are you trying to replace it with an SQL Command?

SQL Commands are only useful for doing things that CR itself can't do, like UNION, GROUP BY, etc...

Peter.

 

[kskid]

If you are wanting to write it in complete SQL, then I would suggest Forum183 (Microsoft SQL Server). I know how to do it in Oracle using sysdate, add_months, to_char and to_date together with a series of concatenations but I do not know SQL Server language.

-LW

 

[Kenny100]

All I wanted was this:

SELECT *
FROM MyTable
WHERE [Date] BETWEEN dateadd(m, -12, getdate()) AND getdate()

Finally figured it out myself. Pretty simple now I look at it!

 

[kskid]

Hmmmm.... I didn't know you have the dateadd function in SQL Server. Learn some new every day.

However, with that function, how would you get the first of the month that you specified in your original post using dateserial?

-LW