Viewing 15 posts - 3,646 through 3,660 (of 7,619 total)
Be sure to create a unique clustered key / primary clustered key on SiteKey in the #Sites tables. With only ~20+ rows it may not matter, but it can't hurt.
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".
December 21, 2017 at 11:13 am
DayOfWeek 7 is ambiguous in SQL Server, since SQL allows the week to start on any day that you specify.
Instead, I'd strongly urge you to use known day...
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".
December 21, 2017 at 9:51 am
If you can, TRUNCATE the table rather than DELETE all the rows, as trunc will be much less overhead (btw, in case you're wondering, a trunc can be rolled back...
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".
December 19, 2017 at 2:29 pm
Sorry about the HOUR(), that's the one function I always forget doesn't exist. Per quarter for the current year, something like this:
select
DATEPART(quarter,...
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".
December 19, 2017 at 12:51 pm
Not 100% sure what you need, but, for example, this gives sales for the latest quarter for >=7AM and <8AM:
select *
from TicketItem
where s_item...
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".
December 19, 2017 at 11:19 am
Below is alternative method that's closer to the original technique (for good or ill). Btw, there's a minor bug in the other code that doesn't reduce the day count 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".
December 19, 2017 at 8:28 am
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".
December 15, 2017 at 8:00 am
Your pattern requires two spaces between the date and the time, and your value only has one space.
Also, you didn't explicitly specify the \ as the escape character: 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".
December 15, 2017 at 6:50 am
Impossible to say. It depends primarily on (1) how much free space is available in each page already, (2) as noted already, how many affected indexes there are on each 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".
December 15, 2017 at 6:46 am
Select
t1.Salesagent,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS Quarter1_Sales_Count,
SUM(CASE WHEN MONTH(t1.DateCompleted) BETWEEN 4 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".
December 14, 2017 at 10:24 am
When joining to do UPDATEs, always update the table alias not the table name itself. Otherwise you could have the same problem (or other problems).
UPDATE ...
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".
December 14, 2017 at 10:20 am
Specifically what row selection conditions do you want? Your sample is not clear. But you almost certainly don't need CASE in any event.
It sounds like you may 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".
December 12, 2017 at 3:01 pm
Could you provide actual test data, as CREATE TABLE and INSERT statements, rather than just a picture of data. We can't write code against a picture .🙂
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".
December 12, 2017 at 2:50 pm
There's no need to join the group row to itself, so this is another possibility:
SELECT c.*
FROM #companies c
LEFT OUTER JOIN #companies g
...
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".
December 8, 2017 at 9:12 am
If the values are integers, I don't see the need to involve date conversions/char stringing at all:
SELECT yyyymm1, yyyymm2, (yyyymm2 / 100 * 12 +...
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".
December 8, 2017 at 9:06 am
Viewing 15 posts - 3,646 through 3,660 (of 7,619 total)