|
|
Greetings,
How do I ensure that the following code only queries records that are submitted between March and May 2024?
If a record has been submitted between the above date range, display 1. Otherwise, display 0.
Currently, we have a code that does this but for entire year.
In the code below, I left the code that performs this check with alias of thisYear.
I left this line of code but commented it out just to show what we have that works except this time, we just want this check to be between March and May 2024.
The code:
CASE WHEN d.dateCreated BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear
appears to work sometimes but does not work other times. No errors but wrong results.
That code above, temporarily replaces this line of code below as described above.
--CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
Here is the entire code:
SELECT e.Name, e.email, e.emptitle, d.dateCreated,
CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,
--CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear
FROM Employees e
INNER JOIN dateDetails d on e.employeeID = d.employeeID
WHERE e.employeeID = someID
I have also tried this:
CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted < DATEFROMPARTS(2024,5 + 1, 1)
Same inconsistent result.
I guess my question is why does this work perfectly:
CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
but the date range does not work well?
Thanks in advance for your help.
|
|
|
|
|
You didn't say what "inconsistent" means.
Additionally what is the data type of 'dateSubmitted'?
|
|
|
|
|
You could have easily told me if my code was right or wrong.
Anyway, I have resolved it.
I was actually coming here to delete the thread.
|
|
|
|
|
You'd be better share your solution.
|
|
|
|
|
samflex wrote: I was actually coming here to delete the thread.
Please don't. Removing some part and leaving an orphaned thread makes it difficult to know what the original question was. And many of the replies do not make sense. Just update your question with the solution you found and add the word "Solved" to the title.
|
|
|
|
|
I wasn't going to delete the thread once someone had responded to it.
Here is what ultimately worked for me.
SELECT e.Name, e.email, e.emptitle, d.dateSubmitted,
CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,
CASE WHEN d.dateSubmitted >= '20240301' AND d.dateSubmitted < '20240501' THEN 1 ELSE 0 END as thisYear
FROM Employees e
INNER JOIN dateDetails d on e.employeeID = d.employeeID
WHERE e.employeeID = someID
|
|
|
|
|
samflex wrote: me if my code was right or wrong.
I wasn't sure without the information I asked for.
However I can recognize that mixing date/time/stamp can be problematic. As well as not accounting for what time zone means both for programming and display.
|
|
|
|
|
Let's say you have a table with an index on the field "username".
Is it true that eventually you'll have some really time-consuming inserts when the hash table has to resize itself?
Hash table resize is O(n), I think.
Thanks.
|
|
|
|
|
INSERT is costly if there's a lot of indexes. It is not that much about resizing, there's usually a lot of free spare space in a DB and hardly anything needs resizing.
If you can come up with a better strategy than indexes, I'd love to hear it.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
mike7411 wrote: time
... define ...
|
|
|
|
|
I was looking at the documentation for CREATE INDEX in SQL:
https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);
I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes?
For instance, does that statement create two hashtables? Or one hashtable?
Thank you.
|
|
|
|
|
|
One index. First column is most significant, second is then used to reduce it further when doing searches.
I don't think you should consider it as a 'hash table'. At a minimum I doubt any relational database uses a hash, in general, to fully implement an index. I suspect hash tables are not an optimal solution for file based storage. A quick google suggests SQL Server specifically supports that type but for a memory table (so not a normal table.)
I don't want to do the deep dive research to actually figure out what they might use for db indexes. But you can read up on what Clustered Indexes are.
Although not directly related the one example I know without research is the comparison of doing sorting. Binary Sort is for memory. Heap Sort is for file based processing.
|
|
|
|
|
Copy/paste from the answer to the question that was posted before yours;
Quote: That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
Meaning, in a list of users, while random stored in the table, would have an index that orders it as such;
Lastname, Firstname
Doe, Jane
Doe, John
Vluggen, Albert
Vluggen, Eddy
Vluggen, Zack
If you search it, you go "where lastname = bla (col1 from index) AND firstname = bla2 (col2 from index)".
This way, it will first locate the segment for "Vluggen", and only has to scan until it finds "Eddy" within that segment. That's what the DB is optimized to do.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Let's say you have a SQL database with a table called USERS.
Let's say it has 500,000 users in it.
Let's say you do this:
SELECT PASSWORD FROM USERS WHERE USER='bill.gates';
Let's say there's no indices on the table.
Does the database just do a linear search thru each user?
Thank you.
|
|
|
|
|
Let's say there are books available on SQL, and let's say there are search engines on the internet.
|
|
|
|
|
Yes. It will scan segments and it breaks once all predicates are met. That's why having a PK with a clustered index is so very nice to have. Who comes up with the idea of a non-indexed table? 
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
In data base talk it is known as a 'table scan'.
Most databases have a way which allows you to see what it is doing. Oracle, SQL Server and MySQL IDEs all have a way to see this clearly. The nomenclature used in the output is hard to read but practice makes that easier.
|
|
|
|
|
|
See the question above; you have an index with two columns. That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
So, yes, you search those linear. For a table without index, that means scanning the entire table as there is no guaranteed order to the rows.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
"index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2."
(My quote button still not working.)
Re-read that response after looking at post to other response.
That phrasing is a misstatement about how it works.
A table storage in a database is quite complicated. Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
There are some implementation details about how that works technically but 'at the end' is the best technical description. Clustered indexes do this differently but not specifically in terms of sorting anything.
After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Sorting is not something that is related to the table nor even to indexes. In SQL you can specifically ask for a specific sort order. If you do not do that then how the records (rows) are returned is somewhat random. And that becomes even more true when things like joins are factored in.
|
|
|
|
|
jschell wrote: That phrasing is a misstatement about how it works.
Please, explain. Please do? Pretty please?
Do explain how it works?
jschell wrote: A table storage in a database is quite complicated
Nope, and quite well documented.
jschell wrote: Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
Tables are without any inherent order, by design and definition. Indexes aren't in the table, they are entities outside the table that may be updated.
jschell wrote: After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Hence, the "create index" expects some columns to sort on.
jschell wrote: Sorting is not something that is related to the table nor even to indexes
If you need not sort, you need no index. So yes, it might be a tad related. The definition in proper schooling talks more about lookups than sorting, as that is more common.
jschell wrote: In SQL you can specifically ask for a specific sort order
Thanks for explaining that, that's really something new.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
modified 10-Feb-24 19:17pm.
|
|
|
|
|
I have a window based application that creates new database every month programmatically and in that database i have single table which consists of 50 columns which also . But the problem is after crating database programmatically it falls in recovery mode. So my question is Why database falls in recovery mode.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
