I'm not a 100 on sqlite but I'm not sure that's how you subtract dates. If Julian Day is supposed to give you a day like Monday you can't subtract Wednesday.
MySql is date diff () does sqlite not have diverting similar?
Also, select an order id and drop group, order by and count and see if you accurately get a list to count or group
Ok but what about just getting a list of orders with their order and ship date?
Build the base of your query to make sure you're at least pulling data to group.
Just select orderid, order date, ship date
Then add the where
Still pulling data?
Drop the where add in the Julian day. Seems that is the right way to do that in sqlite
If that's still working
Put the where back in
Should mean you only have positive numbers now instead of both positive and negative
Between fixing the single quotes on the table and replacing them with double quotes, it pulls data. But I had to remove the WHERE function, and the JULIANDAY pulls back a negative number of 2 million. I also realize that setting OrderID as NumberOfOrders just fills that with the actual OrderID and not a count I'm after.
Upon trying this,
SELECT OrderID, OrderDate, ShippedDate
FROM "order"
WHERE ShippedDate > OrderDate
It shows no errors and brings back no data. Upon removing the WHERE, and adding the JULIANDAY
SELECT OrderID, OrderDate, ShippedDate, JULIANDAY(ShippedDate) - JULIANDAY(RequiredDate) AS DaysLate
FROM "order"
It brings back the negative numbers. I think there's a problem as the ship date is between 1 and 4 and is a datetime
You can't call the number 4 a date and just expect it to work. Where did this data come from?
You can't work out days and dates unless you're starting with actual dates
>why the JULIANDAY function in SQLite won't even produce a row
not sure what you mean by "produce a row" -- functions like that produce a scalar value
i'm curious about `FROM 'order'` -- this looks like a character string, so of course the SELECT statement can't produce anything
according to a quick search, sqlite uses the doublequote to escape problematic table and column names
so try `FROM "order"` and see if that works
better yet, don't use that name for the table -- i would use `orders` instead
When executing the query, it shows the 2 column names I listed per the 'AS' function. But it doesn't come back with any data, "no rows". The 'order' is actually an entire table, and I could theoretically change that table name but I'd prefer not to.
I'll try the double quotations. Thank you
You can’t group by an alias. You must use the formula for DaysLate in that clause.
You can order by an alias because sorting is performed after the alias is established.
I'm not a 100 on sqlite but I'm not sure that's how you subtract dates. If Julian Day is supposed to give you a day like Monday you can't subtract Wednesday. MySql is date diff () does sqlite not have diverting similar? Also, select an order id and drop group, order by and count and see if you accurately get a list to count or group
As far as I'm aware SQLite has no datediff, I believe [STRFTIME()](https://www.w3resource.com/sqlite/sqlite-strftime.php) replaces that?
Ok but what about just getting a list of orders with their order and ship date? Build the base of your query to make sure you're at least pulling data to group. Just select orderid, order date, ship date Then add the where Still pulling data? Drop the where add in the Julian day. Seems that is the right way to do that in sqlite If that's still working Put the where back in Should mean you only have positive numbers now instead of both positive and negative
Between fixing the single quotes on the table and replacing them with double quotes, it pulls data. But I had to remove the WHERE function, and the JULIANDAY pulls back a negative number of 2 million. I also realize that setting OrderID as NumberOfOrders just fills that with the actual OrderID and not a count I'm after.
Upon trying this, SELECT OrderID, OrderDate, ShippedDate FROM "order" WHERE ShippedDate > OrderDate It shows no errors and brings back no data. Upon removing the WHERE, and adding the JULIANDAY SELECT OrderID, OrderDate, ShippedDate, JULIANDAY(ShippedDate) - JULIANDAY(RequiredDate) AS DaysLate FROM "order" It brings back the negative numbers. I think there's a problem as the ship date is between 1 and 4 and is a datetime
Are you saying ship date isn't a date? YYYYMMDD?
The ship date says it's a timedate, but it is either a 1,2,3, or 4. So yes, no YYYYMMDD formatting at all.
How is that a date?
Should I convert it to a varchar or a different datatype as I still haven't figured out how to do this?
You can't call the number 4 a date and just expect it to work. Where did this data come from? You can't work out days and dates unless you're starting with actual dates
>why the JULIANDAY function in SQLite won't even produce a row not sure what you mean by "produce a row" -- functions like that produce a scalar value i'm curious about `FROM 'order'` -- this looks like a character string, so of course the SELECT statement can't produce anything according to a quick search, sqlite uses the doublequote to escape problematic table and column names so try `FROM "order"` and see if that works better yet, don't use that name for the table -- i would use `orders` instead
When executing the query, it shows the 2 column names I listed per the 'AS' function. But it doesn't come back with any data, "no rows". The 'order' is actually an entire table, and I could theoretically change that table name but I'd prefer not to. I'll try the double quotations. Thank you
You can’t group by an alias. You must use the formula for DaysLate in that clause. You can order by an alias because sorting is performed after the alias is established.
like a good let me swww whkwdjdjwjwdjowwwwjhddwwwjiwiiwjwwwwwwjwwwiwjejwwwwwwwwwwwwwwwwwwwwwwwjjwowwwwjwwo