Viewing 15 posts - 2,251 through 2,265 (of 7,619 total)
The good-ole standard tally table should handle this nicely. I default to end of calendar year for end date; naturally change that if/as you need to.
DECLARE @end_date...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2020 at 3:09 pm
Assuming you consider only whole months, truncating any remaining days, then:
declare @age varchar(6)
declare @dob datetime
set @dob = '2015-07-31 00:00:00.000'
select @age = cast(months_old / 12 as varchar(3)) +...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2020 at 9:02 am
It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300 baud...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2020 at 8:46 am
GUIDs are horrendous for clustered indexes. They fragment like crazy. And a guid is always a single key lookup, not a range (at least in my experience), thus a non-clus...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 10:07 pm
CREATE TABLE Employee_Leave
also state that your CHECK for the start date being less than or equal to the end data will fail if the end date is not...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 10:00 pm
I believe all you need to do is specify the proper PARTITION BY clause in the code (new code is below). The code originally comes from Itzik Ben-Gan (I believe),...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 7:13 am
Recompile forces SQL to recreate the query plan, even if one is already available in memory.
That process may require updating stats if one of the tables in the query has...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 1:08 am
... The other thing is, all the other tables have clustered indexes on the GUID PKs ...
That alone might have made me skip buying the product!
Fillfactor also depends on...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 1:04 am
CREATE UNIQUE INDEX UK_IDX_TLOG ON TLOG_TB ( TXN_AUTH_ID ) WHERE MSG_TYPE = '200';
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2020 at 7:27 am
It can not use the run length as a parameter, because the window specification requires a literal value.
But for every given run length a solution exists using only six...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2020 at 2:44 am
>> I want to find runs of at least three 1s, and return the result below. The 0s would be any test_values that are not 1. <<
Since SQL does...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2020 at 7:22 pm
As Phil noted, I can't actually test the code, since you didn't provide any directly usable data:
SELECT up.class, up.model, ca1.type
FROM dbo.u_parts up
CROSS APPLY ( VALUES([W20-13]),([W20-14]),([WorkLog]) ) AS...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2020 at 7:08 pm
Glad it helped, thanks for the feedback!
If you need to split out the final string, you can use a tally table for that:
;WITH
cte_tally10 AS (
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2020 at 2:57 pm
Yet another version.
Non-serious note to Jeff Moden: See, I actually am willing to use an identity as the clustering key.
DECLARE @min_vals_in_sequence int
SET @min_vals_in_sequence = 3
DROP TABLE IF...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2020 at 8:28 am
If you need both extracted values in the same row:
SELECT s.StuffId, ds2.email_name_1, ds2.email_name_2
FROM #Stuff s
CROSS APPLY (
SELECT
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2020 at 1:51 am
Viewing 15 posts - 2,251 through 2,265 (of 7,619 total)