Viewing 15 posts - 4,966 through 4,980 (of 7,619 total)
Is the key column really that long?
Why not just use a separate row for each interval, 5, 15 and 60 minutes?:
settlement_key, interval, value
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".
July 20, 2015 at 3:12 pm
I'd prefer a single UPDATE statement:
UPDATE raa
SET tt_TotalPay = aan.TotalPay,
tt_CountOf100s = aan.TotalPay / 100,
tt_CountOf50s = aan.TotalPay % 100 / 50,
...
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".
July 20, 2015 at 8:26 am
SELECT Capacity
FROM (
SELECT '1234' AS Capacity UNION ALL
SELECT '-987' UNION ALL
SELECT NULL UNION ALL
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".
July 17, 2015 at 3:19 pm
Put double quotes around the column in the SELECT list:
SELECT ..., '"' + column_with_leading_zeros + '"' AS column_name, ...
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".
July 16, 2015 at 11:40 am
You're welcome!
Btw, note that for "SELECT TOP (1) *", the * is not a problem, because SQL can determine that it needs to get only the columns that are actually...
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".
July 16, 2015 at 10:37 am
You need to use CROSS APPLYs (CA) rather than an INNER JOIN (IJ). CA only, without the IJ, assumes you need to see only the low and high hematocrit...
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".
July 16, 2015 at 10:23 am
Luis is correct.
But also quit using sysobjects and syscolumn views, as they are very obsolete and slow (and possibly even buggy).
Try this code instead:
SELECT @COLUMNS = @COLUMNS + c.name +...
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".
July 16, 2015 at 10:05 am
This will be more complex than just what you've shown, if this is trying to strip prefixes/suffixes off a name, as it appears to be.
For example, the letters "JR" and...
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".
July 16, 2015 at 9:55 am
Yeah, I can't think of a really clean way to do it now either, other than brute force:
SELECT table_name.*, derived2.row_num
FROM table_name
INNER JOIN (
SELECT colb,ROW_NUMBER() OVER(ORDER BY...
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".
July 15, 2015 at 1:29 pm
If you typically query by datetime, cluster the table by datetime first. No need to use kludges just to keep load_id as the lead key column.
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".
July 15, 2015 at 10:57 am
A User table, a Group table and a GroupUser -- or UserGroup -- table.
User ( User_Id, ...other_user_columns... )
Group ( Group_Id, ...other_group_columns... )
GroupUser ( Group_Id, User_Id, ...other_columns_related_only_to_the_COMBINATION_of_Group_and_User only]...
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".
July 15, 2015 at 10:55 am
Typically the ROW_NUMBER() method will outperform other methods. It also guarantees only a single result row, which means that duplicate values will be ignored (I believe you can use...
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".
July 14, 2015 at 3:24 pm
Or maybe this?:
SELECT
t.ProductID
,t.Order_Day
,t.Order_Quantity
FROM (
SELECT
ProductID
,Order_Day
,Order_Quantity
,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY...
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".
July 14, 2015 at 3:15 pm
Yes, but you could have a start-up proc that recreates the table. Or just test for its existence and create it if it's not there.
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".
July 14, 2015 at 2:36 pm
For a particular table, you can add a trigger to the table to capture the relevant details from any UPDATE statements.
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".
July 14, 2015 at 2:35 pm
Viewing 15 posts - 4,966 through 4,980 (of 7,619 total)