Viewing 15 posts - 3,376 through 3,390 (of 7,613 total)
I don't think "AVG" really has any meaning for the diff between only 2 dates?!
SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
FROM (
SELECT cust_id, visit_date
, ROW_NUMBER()...
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".
May 23, 2018 at 10:11 am
Try forcing a MERGE join, see if that helps significantly. If it does, then go ahead and cluster the SOH table on ( IT_ID, ST_ID, DateID ). Yes, that means the rows...
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".
May 23, 2018 at 10:04 am
Partition the clus columnstore index on that date. Then SQL can eliminate partitions with non-matching dates. I partition by month on many of our very large tables, but you might...
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".
May 22, 2018 at 7:48 am
It's definitely possible to do. You'll most likely need an index on the table to support the lookup. You might be able to use a filtered index, but I can't...
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".
May 21, 2018 at 2:18 pm
SELECT ut.IdUser,
MIN(ut.IdShop) AS IdShop_Min, MAX(ut.IdShop) AS Id_Shop_Max /*MIN & MAX are optional, of course*/
FROM @UserTransaction ut
GROUP BY ut.IdUser
HAVING COUNT(DISTINCT ut.IdShop) >...
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".
May 18, 2018 at 2:54 pm
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table...
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".
May 18, 2018 at 11:09 am
SELECT GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans 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".
May 18, 2018 at 11:04 am
Your clustered index is fine.
Actually that's a significant understatement by me. Your clus index is generally superb (although you could probably remove the Attribute* columns with no real...
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".
May 17, 2018 at 10:25 am
Your clustered index is fine. 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".
You really don't need the temp tables for this specific query, but if building them is not taking much time, then that's OK too.
May 17, 2018 at 8:24 am
For testing a datetime/datetime2 column for the current day, you should do this:
--good way
cr.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
cr.CreatedDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) +...
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".
May 15, 2018 at 10:52 am
Data warehouses are often denormalized. However, this data is so unlikely to be needed that all columns other than, say, FullName, should be put into a separate table, which can be joined...
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".
May 14, 2018 at 11:29 am
Back in the day, I would do a pattern search to determine eligible data types:
SUM(CASE WHEN column NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END) AS column_has_only_digits, 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".
etc.
May 11, 2018 at 1:31 pm
For a first pass, you could try just ISDATE, IS NULL, ISNUMERIC and a few other key attributes:
CREATE TABLE dbo.column_profile ( ... );
INSERT...
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".
May 11, 2018 at 12:39 pm
Put the metrics in a table. Then we can join to each metric as needed.
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".
May 10, 2018 at 1:16 pm
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".
May 10, 2018 at 10:24 am
Viewing 15 posts - 3,376 through 3,390 (of 7,613 total)