|
That is the wrong way to hold date and time information in a database. You should always use DateTime types, and store UTC values.
|
|
|
|
|
Assuming you're using Microsoft SQL Server, something like this should work:
WITH cte As
(
SELECT
ID,
[Device id],
TimeZone,
[Effective Date],
ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
[Effective Date] DESC
) As RN
FROM
Timetable
)
SELECT
ID,
[Device id],
TimeZone,
[Effective Date]
FROM
cte
WHERE
RN = 1
;
ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]
NB: You should avoid using spaces or special characters in table and column names. It makes it harder to query the data correctly. Instead, change the display names in your application's UI.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you .it has solved partially but still have one problem
If we have rows which are less than or equal for the supplied parameter(Date) we are pulling highest date row here this is working correct .
if we don't have any rows which are less than or equal for the supplied parameter(Date) and if we have rows which are greater than supplied parameter we need to pull lowest date.can you help in this regard
|
|
|
|
|
Try changing the ROW_NUMBER ordering:
ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
CASE WHEN [Effective Date] <= @YourDateParameter THEN [Effective Date] ELSE Null END DESC,
CASE WHEN [Effective Date] <= @YourDateParameter THEN Null ELSE [Effective Date] END
) As RN
- Dates on or before the date parameter come first;
- Dates on or before the parameter are sorted in descending order, so the latest comes first;
- Dates after the parameter are sorted in ascending order, so the earliest comes first;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Interesting, why this...
SELECT
COUNT(*)
FROM TBL1
LEFT JOIN TBL2 ON TBL2.ID = TBL1.TBL2_ID
WHERE (TBL1.INTFIELD IS NULL) <> (TBL2.INTFIELD IS NULL)
... Ends in an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.
Where I'm wrong? Is it not allowed to compare two booleans?
At least in (MS)SQL not 
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I think boolean expressions require boolean operators: AND, OR etc.
|
|
|
|
|
Yes seems so in SQL. But e.g. in c++ (and I think also in c#) you can easy have something like:
bool b1= true;
bool b2= false;
bool res= b1 == b2;
modified 19-Jan-21 21:04pm.
|
|
|
|
|
(SQL != C++) && (SQL != C#)
|
|
|
|
|
No ...
(SQL != C++) && (SQL != C#)
... does also not work 
modified 19-Jan-21 21:04pm.
|
|
|
|
|
And SQL := SQL. Only for Information, MySQL 5.6 does evaluate it like I would expect.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I think you mean SQL != MySQL. Different SQL implementations have different features, that is well known.
|
|
|
|
|
NULLs cannot be compared in SQL
|
|
|
|
|
Try using a case statement in the where clause to turn the bool in 1 or 0
where (case when intfield is null then 0 else 1 end) != (case when intfield is null then 0 else 1 end)
(why do you have int field that are null?)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why int field available NULL: To know wheter there have assigned a value. BTW the same "Problem" will be for any other datatype I assume.
case: Would be nice to have it. At the Moment I have to fight with Interbase SQL 
modified 19-Jan-21 21:04pm.
|
|
|
|
|
You need a logical operator to combine two boolean conditions.
In this example, you'd want the Xor operator. But unfortunately, MS SQL Server doesn't support it; it only provides a bitwise Xor operator, which won't work here.
You can either use CASE, as Mycroft suggested, or the longhand version of Xor:
a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)
... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you for your answer.
"The longhead version of Xor": That is the Thing a tried to write shorter when I one time again was struggling with the same described here:
The Weird and The Wonderful[^]
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Only for Information: MySQL 5.6 does evaluate it, like I expect it has to be evaluated. Tested on SQL Fidle.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Strange, Could of swore when I first wrote this, I was just getting 1 record.
Then I changed the return value to List from IEnumerable and starting getting 3 records back for each record.
But I doubt that had anything to do with it. I did raise the .Net level to 4.5.2.
Must be my And Statement.
I tried Distinct but It doesn't seen to be working, most likely because I have no clue how use it here.
I started writing in c# and MVC and don't write like this anymore.
Public Shared Function load_COH_ListBox_Info(
ByRef pResults As List(Of cohIndex)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
pResults =
(
From oh In context.Order_History
Where oh.OrderDate >= DbFunctions.AddDays(Today, -40) _
And
(
oh.OrderStatus = "COMPLETED" _
Or oh.OrderStatus = "ORDER_CANCELED" _
Or oh.OrderStatus = "ORDER_CANCELED_REFUND_COMPLETE"
)
Order By oh.OrderNumber Descending
Select New cohIndex With
{
.orderID = oh.CompletedOrderID,
.orderNumber = oh.OrderNumber,
.orderDate = oh.OrderDate,
.orderStatus = oh.OrderStatus,
.accountName = oh.LoginID,
.total_NetCharge = oh.GrandTotal
}
).Distinct().OrderByDescending(Function(ob) ob.orderNumber).ToList()
pValue = pResults.Count()
Return pValue
End Function
If it ain't broke don't fix it
|
|
|
|
|
The 3 records each are in my database, let me fix that first
If it ain't broke don't fix it
|
|
|
|
|
Instead of removing them, you can just group them by their ID.
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
I am surprised as to why you didn't consider using GroupBy function? That would be useful in grouping the objects by a property, so that only distinct ones are returned, since they will be grouped by their property in a similar manner, to what you want to expect from Distinct function call here. Something like this,
from oh in context.Order_History
where oh.OrderDate ...
orderby oh.OrderNumber desc
group oh by oh.RequiredProperty in ohGrouped
...
select ...
Something like this will be useful, as it will group the records by the properties which you specify. Please have a look here, [c# - Group by in LINQ - Stack Overflow](https://stackoverflow.com/questions/7325278/group-by-in-linq)
There is a library, which contains a lot of added features to LINQ, you should also look into it as it might also help you out with a lot of good function calls, such as this DistinctBy (DistinctBy takes the properties by which you want to select the records), get it here, GitHub - morelinq/MoreLINQ: Extensions to LINQ to Objects
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
How do I set this in a Sql query?
If code >= 1 or <= 9 then place two zeros if front...001, 002....009
if code >= 10 or <= 99 then place one zero if front....010, 050...,099
Thanks
**** NEVER MIND, I GOT IT:*********
when [EMPLOYEE].JOB_CODE >=1 AND [EMPLOYEE].JOB_CODE <= 9 then '00' + [EMPLOYEE].JOB_CODE
when [EMPLOYEE].JOB_CODE >=10 AND [EMPLOYEE].JOB_CODE <= 99 then '0' + [EMPLOYEE].JOB_CODE
modified 20-Jul-17 8:43am.
|
|
|
|
|
CASE
If current SQL Server, look at FORMAT.
modified 19-Jul-17 20:53pm.
|
|
|
|
|
Don't you mean ...
If code >= 1 AND <= 9 then place two zeros if front...001, 002....009
if code >= 10 AND <= 99 then place one zero if front....010, 050...,099
... ?
|
|
|
|
|
Correct - was in a rush to leave the office when I typed it.
I want to concatenate 00 in front of the value.
modified 20-Jul-17 8:31am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
