Return all rows for todays date, and yesterday.

Question

Post reply

Return all rows for todays date, and yesterday.

A wide range of data-types, such as UNICODE character data types, IMAGE, MONEY, and IDENTITY.

249 indexes per table as well as up to 16 columns in a multicolumn index.
Supports database size up to 4 gigabytes
128-bit file-level encryption.
Data definition language (DDL) and data manipulation language (DML).
SET Functions (aggregates), INNER/OUTER JOIN, subselect, and GROUP BY/HAVING.

Supported cursor types are Base table, Static, Forward-only, Forward-only/Read-only, and Keyset-driven.

Parameterized queries.
Intrinsic functions.
Connectivity setup wizards.
Enhanced interactive structured query language (ISQL).

Notice that they advertise that it uses "ISQL" which is a bit deprecated when compared to Query Analyzer and OSQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Lynn,

this will not work.. maybe if you include DISTINCT. As to now, select in the IN clause will return two rows with exactly the same value... supposing there are two or more entries of today.

I don't know what SQL Mobile allows regarding TOP, so I didn't try to offer this (or similar) solution.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Vladen,

I realized that as soon as I had posted it. Hopefully, and only testing in a mobile environment (which I don't have), the second option will work. Looks cleaner than the query Trystan got to work.

Lynn

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Sorry for cluttering this thread with posts, but (if we stick to a solution with IN clause), this would be simpler and it does precisely the same as SQL in my previous post:

SELECT col1, col2, ...

FROM tbl_NSP_Inspection i

WHERE ((i.CreatedDate >= (select DATEADD(d, DATEDIFF(d, 0, max(i2.CreatedDate)), 0)

from tbl_NSP_Inspection i2

where i2.CreatedDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))

AND (i.CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)))

But I don't like it. Is here anyone who knows how to work with SQL Mobile? I have no idea... there should be some simpler and performance-wise better solution.

PS: Well, now you have 2 solutions, Trystan... you can try them both and find out what seems to work better.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

That solution looks good, but why don't you like it?

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Because "WHERE something IN (SELECT ...)" is generally a bad idea because of performance and I prefer to avoid it. I would prefer a solution with a derived table, if anything... can you use derived tables in SQL Mobile, Tryst?

If yes, try this one:

SELECT col1, col2, ...

FROM tbl_NSP_Inspection i

JOIN

(select DATEADD(d, DATEDIFF(d, 0, max(i2.CreatedDate)), 0) as thedate

from tbl_NSP_Inspection i2

where i2.CreatedDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AS Q

ON i.DateCreated >= Q.thedate

This time, I have entirely dropped the upper date limit - it seems to be unnecessary. Of course, unless you want to have it foolproof against possibly corrupted data with DateCreated 2106-10-17 and similar. In this case, just add the upper limit into the WHERE clause:

SELECT col1, col2, ...

FROM tbl_NSP_Inspection i

JOIN

(select DATEADD(d, DATEDIFF(d, 0, max(i2.CreatedDate)), 0) as thedate

from tbl_NSP_Inspection i2

where i2.CreatedDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AS Q

ON i.DateCreated >= Q.thedate

WHERE i.CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I'll agree that IN could result in a performance issue, especially if you have a very large list. However, with a short list, as in this case only two values, I'm not sure it would result in a significant hit in performance.

The best thing to do is test a variety of solutions that work and take the best. Isn't that what we are here trying to do, help each other with solutions? I think I am going to start using some of the ideas you presented here regarding dates myself. I hadn't tried using DATEDIFF and DATEADD to strip the time portion from a datetime value, and it seems cleaner once you experiment and test.

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 7

Hi all,

and thanks for your replies.

Not sure on DERIVED TABLES, but looking at this article, it seems it is allowed.

(http://msdn2.microsoft.com/en-us/library/ms174633.aspx)

I do know that the TOP keyword is not allowed though, for some strange reason.

I haven't tried it yet, but Vladan, that query you produced will give me all the rows for today, and all for the last day in which rows were inserted?

Thanks

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 8

I hope this gives u what u want.

SELECT col1, col2

FROM tbl

/* Get Max Date where Date is not today*/

WHERE tbl.date >= (Select max([Date]) from tbl

where Cast(Convert(Char(10),[Date],101) as datetime) < Cast(Convert(Char(10),getdate(),101) as datetime))

and tbl.date < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Yes Trystan,

if I didn't make any mistake in the code (I didn't have time to create tables and sample data to test it), the query with derived table I posted yesterday (8:58 AM) should give you all the required results : all rows from the last day before today when at least one row was inserted, and from today.

You see, if you would want to get all rows entered x days ago and today (with a possible gap in between, even if rows were entered in these days), you need to specify 2 date ranges. Your requirement was to get rows from today and the day immediately before today, only skipping days when nothing was entered. This makes one nice continuous interval, so the conditions can be pretty simple.

Logic of Sreejith's solution is the same as that in my previous attempt, but it uses conversion instead of datetime functions in the subquery - which is, according to many sources, less effective and is not recommended. The use of derived table instead of IN (select...) should further improve performance, although the difference will probably not be too big in this particular case - as Lynn correctly pointed out, gain increases with grownig number of rows returned by the select in IN clause.

I think the best for you will be to test solutions in your system with all the data and you'll see what works best.

Good luck!

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 10

Hi all,

yes it seems that derived tables is DEF not allowed in SQL mobile, and also queries that contain statements similar to the following will cause an error...

WHERE (CreatedDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND (I.CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0))) OR (SUBSTRING(CONVERT(NVARCHAR(20), I.CreatedDate, 21), 1, 10) IN

(SELECT SUBSTRING(CONVERT(NVARCHAR(20), MAX(I2.CreatedDate), 21), 1, 10) FROM tbl_NSP_Inspection I2 WHERE ((DATEPART(DAY, I2.CreatedDate) != DATEPART(DAY, GETDATE())) OR (DATEPART(MONTH, I2.CreatedDate) != DATEPART(MONTH, GETDATE())) OR (DATEPART(YEAR, I2.CreatedDate) != DATEPART(YEAR, GETDATE())))))) ORDER BY I.CreatedDate DESC

Thanks

Tryst

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 11

Because GETDATE() has a time associated with it. The want all of yesterday... in order to do that, you must strip the time back to midnight of yesterday... all that DATEDIFF/DATEADD stuff does that very quickly... usually much more quickly than using a CONVERT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 12

Crud... didn't see it before but Vladan beat me to the explanation for Cory a long time ago... sorry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 13

According to the datasheet available at:

http://download.microsoft.com/download/e/a/1/ea185159-79f1-43f7-8bc6-2027c6a7ebeb/SQLServerMobileDatasheet.doc

Subqueries are allowed and parameters are allowed...

Compact Yet Capable

SQL Server Mobile offers rich relational database functionality in the small footprint necessary for the memory limitations of today's mobile devices. Developers familiar with SQL Server will appreciate the robust feature set, which includes:

Full referential integrity with cascading deletes and updates.