|
SELECT CASE WHEN MCTR_DESCR IN ('Large', 'Midsize') THEN 'LG' ELSE '' END ...
etc. Just build it out. It's all pretty straightforward.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Hi All,
Ok so I am hoping I can get some help here please?
My situation is that we have three environments 2 are using sql server and 1 is using sql server clustered.
We have a SSIS package that is used to create xml files which works perfectly in environment 1 and 2. However in environment 3 the using clustered sql the xml file generated is out of sequence. It's lists all odd numbered records first then all the even numbered ones.
Everything between the environment is like for like so I cannot see why this is happening.
Are there any steps or an approach I can use to see why this could be happening?
Thanks
KP
|
|
|
|
|
Member 13357915 wrote: It's lists all odd numbered records first then all the even numbered ones.
Why is that a problem?
Member 13357915 wrote: so I cannot see why this is happening.
Assuming ordering perhaps. If you are not explicitly ordering the records then you are assuming the ordering. There is no assurance of ordering. It might normally be ordering (default ordering) by the primary index and that might change with a cluster.
|
|
|
|
|
Hi ,
ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
1 123 PST 2017-06-24 00:00:00.000
1 123 MST 2017-08-02 00:00:00.000
2 345 CST 2017-07-01 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000
The above table is Name Timetable .I looking for below result if i pass date parameter
as ‘2017-08-01 00:00:00.000’
ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000
|
|
|
|
|
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 !~
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
