Viewing 15 posts - 61 through 75 (of 2,171 total)
Did you wrap the code as an inline table-valued function?
CREATE FUNCTION dbo.OverlapHours
(
@s1 DATETIME,
@f1 DATETIME,
@s2 DATETIME,
@f2 DATETIME
)
RETURNS TABLE
AS
RETURN(
SELECTCASE
WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))
ELSE 0
END AS [Hours]
FROM(
VALUES(@s1, @f1),
(@s2, @f2)
) AS...
N 56°04'39.16"
E 12°55'05.25"
August 13, 2014 at 5:09 am
What's wrong with a set-based solution?SELECTCASE
WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))
ELSE 0
END
FROM(
VALUES(@s1, @f1),
(@s2, @f2)
) AS d(FromTime, ToTime);You can wrap this as an ITVF.
N 56°04'39.16"
E 12°55'05.25"
August 13, 2014 at 4:14 am
SELECTMarker
FROMdbo.TEMP_A
GROUP BYMarker
HAVINGSUM(CASE WHEN Val = 'Y' THEN 0 ELSE 1 END) = 0
N 56°04'39.16"
E 12°55'05.25"
July 4, 2014 at 3:22 am
No. You are right.
I am the one needing more coffee. Just a mind lapse and disregarding the DISTINCT for some reason.
N 56°04'39.16"
E 12°55'05.25"
June 13, 2014 at 7:17 am
So, depending on the distribution of the sample data, the two different queries will return different results.
The COUNT(DISTINCT ... ) will return a fewer number of rows, than the SUM(CASE...
N 56°04'39.16"
E 12°55'05.25"
June 13, 2014 at 6:30 am
You don't need to.
The COUNT(DISTINCT ...) approach will only return the groups that has exactly one A and one B.
The SUM(CASE ...) approach will return all groups having at least...
N 56°04'39.16"
E 12°55'05.25"
June 13, 2014 at 6:16 am
The Wizard Of Oz (6/13/2014)
I tested Jeff's original solution and 2 other solutions on a 10-million-row random table (using the code attached in Jeff's article):
They are not equivalent and return...
N 56°04'39.16"
E 12°55'05.25"
June 13, 2014 at 5:20 am
It boils down to the number of IO used for the solution.
Also the CPU usage matters.
Obviously scanning the table twice (using INTERCEPT/EXCEPT for example) uses twice as much IO than...
N 56°04'39.16"
E 12°55'05.25"
June 12, 2014 at 8:24 am
robinwilson (6/7/2014)
Hello AllI'm just wondering, is there anything wrong with doing it this way:
There is no guarantee that customer with both product A and B are returned.
N 56°04'39.16"
E 12°55'05.25"
June 7, 2014 at 8:00 am
SimonC, I have to disagree with you here.
Even if the PIVOT solution seem to work well, it doesn't scale well. And that is one of the cardinal sins junior developers...
N 56°04'39.16"
E 12°55'05.25"
June 6, 2014 at 11:59 pm
Naomi N (6/6/2014)
Peter,I think we wanted customers who bought A and B but not C, so your query will be even simpler,
MIN(productID) = 'A' and MAX(productID) = 'B'
See page 10.
N 56°04'39.16"
E 12°55'05.25"
June 6, 2014 at 12:40 pm
SELECT CustomerID
FROM #Purchase
WHERE ProductCode IN ('A', 'B', 'C')
GROUP BY CustomerID
HAVING MIN(ProductCode) = 'A'
AND MAX(ProductCode) = 'C'
AND SUM(CASE WHEN ProductCode = 'B' THEN 1 ELSE...
N 56°04'39.16"
E 12°55'05.25"
June 6, 2014 at 9:43 am
You know me Jeff 🙂 Can't help myself since this is a Relational Division Problem.
SELECTCustomerID
FROM#Purchase
WHEREProductCode IN ('A', 'B', 'C')
GROUP BYCustomerID
HAVINGMIN(ProductCode) = 'A'
AND MAX(ProductCode) = 'B'
--AND COUNT(*) = 2;
N 56°04'39.16"
E 12°55'05.25"
June 6, 2014 at 4:00 am
Here is a link to download the schema of my hybrid solution.
It can also be found on my web page http://www.sqltopia.com
N 56°04'39.16"
E 12°55'05.25"
December 9, 2013 at 5:37 am
Thank you Jeff!
The client I was working with have 35,000 employees of which about 15,000 are using the database at any given time. And the system is, by nature, a...
N 56°04'39.16"
E 12°55'05.25"
December 9, 2013 at 5:23 am
Viewing 15 posts - 61 through 75 (of 2,171 total)