|
|
|
Why?
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.
|
|
|
|
|
Pick one data models[^]
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm wondering if I went too far in converting all these dates. While I was testing, I think the database server crashed and the connection broke. I know I asked this before about converting date times, and I thought I fixed this in Feb because it was working fine. But I'm back to it again.
SELECT
a.project_no,
a.status,
a.Sales_no,
a.swan_job,
b.tc,
b.EC,
convert(VARCHAR(10), b.startup_check_date, 120),
convert(VARCHAR(10), b.finished_check_date, 120),
e.Employee_ID,
e.fname,
e.lname,
c.customer_no,
c.lname,
c.fname
FROM project AS a, commission_summary AS b, employee AS e, customer AS c
WHERE a.project_no = b.project_no
AND a.customer_no = c.customer_no AND a.sales_no = e.Employee_ID
AND (a.status = 'construction' OR a.status = 'finished')
AND (convert(VARCHAR(10), b.startup_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31')
OR (convert(VARCHAR(10), b.finished_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31')
OR (convert(VARCHAR(10), b.startup_check_date, 120) IS NULL
OR convert(VARCHAR(10), b.startup_check_date, 120) = '1900-01-01')
OR (convert(VARCHAR(10), b.startup_check_date, 120) < '2021-4-01')
AND (convert(VARCHAR(10), b.finished_check_date, 120) IS NULL OR convert(VARCHAR(10), b.finished_check_date, 120) = '1900-01-01')
ORDER BY a.status, b.finished_check_date, b.startup_check_date
Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01
SELECT
a.project_no,
a.status,
a.Sales_no,
a.swan_job,
b.tc,
b.EC,
convert(CHAR(10),
b.startup_check_date,120),
convert(CHAR(10),
b.finished_check_date,120),
e.Employee_ID,
e.fname,
e.lname,
c.customer_no,
c.lname,
c.fname
FROM project as a, commission_summary as b, employee as e , customer as c
WHERE a.project_no=b.project_no
AND a.customer_no=c.customer_no
AND a.sales_no = e.Employee_ID
AND (a.status ='construction' or a.status ='finished')
AND ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (CAST(FLOOR(CAST(b.finished_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (b.startup_check_date is NULL or b.startup_check_date = '1900-01-01')
OR ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) < '$firstDate' )
AND (b.finished_check_date is NULL or b.finished_check_date = '1900-01-01')))";
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
jkirkerx wrote:
Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01
From MSDN FLOOR (Transact-SQL) - SQL Server | Microsoft Docs :
Quote: Returns the largest integer less than or equal to the specified numeric expression.
I'd expect "Quote: the date at midnight
"
|
|
|
|
|
Don't convert dates to strings in order to compare them. If you just want to ignore the time part, cast them to date instead:
CAST(b.startup_check_date As date) Between '20210401' And '20210431' However, this will be non-SARGable. A better option would be to use:
(b.startup_check_date >= '20210401' And b.startup_check_date < '20210501')
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This makes more sense to me now.
so I need to make sure that I craft the dates, like 04 for April by adding the 0.
And on the Select statement, I can convert to string to get the output in the format that I want,
But on the condition statement, convert to the format that SQL server works the best with.
No wonder why I crashed the database server.
I'll give it a try today.
Thanks Richard.
And Thanks to the other guy about the Floor
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
if you must craft a date then use the short name of the month it removes all ambiguity from the expression and SQL Server likes it.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
You mean like Apr for 04?
DECLARE @date datetime2 = '2000-01-01';
SELECT FORMAT(@date, 'MMM') AS 'FORMAT';
Hmm...
I would of never considered that. Will give it a test and see how it goes.
Haven't done anything today with that SQL statement yet.
Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Using yyyyMMdd, with no separators, is unambiguous to SQL Server. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I ended up with this. I didn't want to do too much change at one time, and focused on preserving the date formats that feed into the SQL statements via PHP. eg. '2021-04-10'. I made sure that the month and day were all like 04 instead of 4.
And my head scratchier was a pretty stupid mistake; I translated the code late in the month, so the day was 21. When the month rolled over the day 08 was 8. I went back through my PHPV7.4 and fixed how I crafted the dates.
This was just one of probably 30 SQL statements I fixed. But now my results are matching the original code written in 2003.
SELECT
a.project_no,
a.status,
a.Sales_no,
a.swan_job,
b.tc,
b.EC,
convert(char(10), b.startup_check_date, 120),
convert(char(10), b.finished_check_date, 120),
e.Employee_ID,
e.fname,
e.lname,
c.customer_no,
c.lname,
c.fname
FROM project AS a, commission_summary AS b, employee AS e, customer AS c
WHERE a.project_no = b.project_no AND a.customer_no = c.customer_no
AND a.sales_no = e.Employee_ID
AND (a.status ='construction'
OR a.status ='finished')
AND ((CONVERT(char(10), b.startup_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.startup_check_date, 120) <= '$lastDate')
OR (CONVERT(char(10), b.finished_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.finished_check_date, 120) <= '$lastDate')
OR (b.startup_check_date IS NULL OR CONVERT(char(10), b.startup_check_date, 120) = '1900-01-01')
OR ((CONVERT(char(10), b.startup_check_date, 120) <= '$firstDate')
AND (b.finished_check_date IS NULL OR CONVERT(char(10), b.finished_check_date, 120) = '1900-01-01')))
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Hi All
I am working to move an unwieldy spreadsheet into a streamlined database to improve the data handling, speed of use and data integrity. I kind of understand what it is I want to do (to a degree) but am new to data modelling/database design so need to get assistance on a particular situation that currently has me stumped as to how to best overcome/design around.
I currently have the data split into 4 tables based on the unique and the repeated/copied data in the spreadsheet. The data is around delivery of goods and split as follows:
Consignment which can be made up of 1 or multiple warehouse deliveries
Warehouse deliveries which can be made up of 1 or multiple customer deliveries
So far there are 3 of the 4 tables, there MUST be at least 1 warehouse delivery for a consignment and there MUST be at least 1 customer delivery for a warehouse delivery
The 4th table is Product. Currently a customer delivery can be 1 or multiple products so I believe that the Product should be related to the Customer Delivery table, but the wrinkle is that at the start of the process we will have a Consignment with Product(s) but not necessarily the Customer information until a later date. Currently in the spreadsheet what happens is the data is entered on one line with no Customer/Warehouse information and just a total for the product and then at a later date it is split into multiple lines of data with alot of repeated information but the specific Warehouse/Customer/Product Split information updated. So previously I stated there MUST be warehouse deliveries and customer deliveries for every consignment and whilst this is true the nature of the business means that initially that information may not be available until some time after the consignment is en route to the warehouse
Hope this is all making sense so far.
So here's my question. Should the Product table be related to Consignment or Customer Delivery, and if customer delivery is it a matter of Warehouse and Customer delivery details being entered in a dummy format to allow the relationships to be setup and manipulated at a later date
I really hope this all makes sense as i'm trying to do this in the right way but my low knowledge on database design means i'm kind of making it up as i go by reading up on the right way to do things from multiple sources and pulling together examples of potentially similar standard designs and copy/pasting sections from them to cobble together the data model
thanks in advance
Glen
|
|
|
|
|
Couple of things that might help while you are waiting for a more definitive answer ...
Here is a list of tutorials on Database Design DatabaseAnswers Tutorials[^]
The same site provides pre-designed schemas for multiple scenarios - see http://www.databaseanswers.org/data_models/index_all_models.htm[^]
My gut feelings for your problem:
In my head Products need to be associated to Customer Orders (deliveries). I'm not sure what sort of business model you are using if you know what products are going to be in a consignment without any customer orders to back them up. It sounds as if you are going to need a "release" process for a consignment that will do the validation on missing components / relationships
|
|
|
|
|
Forget the idea of "Delivery" you need to track the product through your workplace, relating it to a customer at some point.
I would split out the Product and customer data into separate tables not related to anything else. This data should only ever be entered once with primary keys used to relate them to the other tables.
Consignment table would be a collection of products (productID). Presumably this is a collection of products coming into your environment. I would probably not relate this to any other piece of data.
Warehouse is just another collection of product (why do you need both a consignment and warehouse collection)
A Delivery is the last process you should be implementing made up of product and customer id's. You may want to pick your product from a consignment/warehouse collection to be able to track the product.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
These two queries:
String sQuery1 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id=?";
Cursor cursor1 = db.rawQuery(sQuery1, new String[]{ String.valueOf(id) });
String sQuery2 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id="+id;
Cursor cursor2 = db.rawQuery(sQuery2, null);only differ in how they handle the parameter at the end. For the second query, it compiles and executes fine. For the first query, the comma in the WHERE clause shows this error (in Android Studio):
'(', ')', '.', BETWEEN or IN expected, got ','
Even if I remove the AND condition altogether:
String sQuery3 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed)";
Cursor cursor3 = db.rawQuery(sQuery3, null); the error persists. Since there are two columns in the WHERE clause and two columns in the subquery, I don't understand the issue.
I also tried using aliases.
[edit]
I was able to use the third query in DB Browser for SQLite and it worked fine. That tells me that the syntax is correct and the problem is with how AS handles strings containing SQL queries.
[/edit]
Any ideas?
Thanks.
DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
modified 23-Mar-21 16:27pm.
|
|
|
|
|
David Crow wrote: String sQuery2 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id="+id;
This appears to be missing a + ")". Also, what's the value of id. Just doing a string concatenation could result in SQL injection, so that seems like a bad idea.
Keep Calm and Carry On
|
|
|
|
|
k5054 wrote:
This appears to be missing a + ")".
All parenthesis are properly paired up.
k5054 wrote: Also, what's the value of id.
It could be any number.
k5054 wrote: Just doing a string concatenation could result in SQL injection, so that seems like a bad idea.
True, but that's not what the post is about.
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
|
|
|
|
|
I can't find any documentation to suggest that Sqlite supports multi-value IN queries; that syntax only seems to apply to MySQL.
Would an Exists query work instead?
SELECT * FROM service_schedules As s WHERE Exists(SELECT 1 FROM services_performed As p WHERE p.vehicle_id = s.vehicle_id And p.service_id = s.service_id) And s.vehicle_id = ?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: I can't find any documentation to suggest that Sqlite supports multi-value IN queries; that syntax only seems to apply to MySQL.
Given that the query works in both Android Studio and in DB Browser for SQLite, I'd say the syntax is supported.
Richard Deeming wrote: Would an Exists query work instead?
I'll try it and see.
[edit]
The EXISTS query produces the same results as the second query in my initial post. Thanks. I'm still wondering what AS has against that first query string, though.
[/edit]
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
modified 24-Mar-21 14:55pm.
|
|
|
|
|
I took a PHP job, upgrading an old PHP 4.7 project to PHP 7.14. I'm doing pretty good rewriting it as an object oriented app, and re imagining the design. But I'm terrible at SQL, and glad Linq came along. I get the error message below, and played around with the statement, ruling out that the cast is the issue. I think the date I put in bold is the issue, because I removed the line and it ran fine. I get the convert part, but should I convert to a float as well? Why a float if so? Is the date really stored as numbers?
Error Message:
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 260 [code] => 260 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Disallowed implicit conversion from data type smalldatetime to data type float, table 'commission_summary', column 'startup_check_date'. Use the CONVERT function to run this query. ) )
getSalesBonusByDate 362: SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79' 1
Query: I think the date in bold is the error, but I'm not sure what to convert the value to. The field is a smalldatetime.
SELECT count (a.project_no) FROM project as a, commission_summary as b WHERE a.project_no = b.project_no AND (a.sold_date <= 3-1-2021) AND (a.status = 'construction' or a.status = 'finished') AND ((CAST(FLOOR(b.startup_check_date)AS DATETIME) BETWEEN 1-1-2021 AND 3-1-2021)) AND a.sales_no = '79'
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
try '3-1-2021' rather than 3-1-2021
|
|
|
|
|
Good idea and tried it, but I think I need to study convert and learn it really fast.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
SQL Server uses ISO dates, so it should be '2021-03-01' (if you are wanting 1st March) or '2021-01-03' (for 3rd January) - the quotes are needed
|
|
|
|
|
Ok ...
The dates stored in the database are like 3/1/2021 as smalldatetime
But I'm asking to compare against 2021-03-01, an ISO date, so wrap what I'm asking to compare to.
I came up with this ...
$query = "
SELECT
count (a.project_no)
FROM project as a, commission_summary as b
WHERE a.project_no = b.project_no
AND (a.sold_date <= '$lastDate')
AND (a.status = 'construction' or a.status = 'finished')
AND (convert(CHAR(10), b.startup_check_date, 120) BETWEEN '$firstDate' AND '$lastDate')
AND a.sales_no = '$salesId'";
I fiddled with the conversion, and was sure I got it right but it failed. Then I used the suggestion to wrap the dates in single quotes and it produced a clean result of 9.
I get the convert part, declare a CHAR no more than 10, input value, ?
Not sure what the 120 stands for.
However your explanation of the date formats has schooled me in how to fiddle with them and get it right.
Just FYI, I wrote some other fixes earlier that are similar, and just had a complete blackout of my previous experiences. But this lesson should solidify it. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
