Viewing 15 posts - 2,401 through 2,415 (of 7,619 total)
I'm not 100% sure of what you need, but I think the total from the first table is being overstated. If so, then maybe this:
select
count (H.Customs_Entry_Num)
,max(L.[Child_Customs_Entry_Num_Count])...
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 29, 2020 at 3:40 pm
Thanks both
It looks like it's the ROW_NUMBER() section that's taking the time to work out.
Is there any sort of alternative to that bit?
I need to get the records in...
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 20, 2020 at 2:48 pm
I think you do need at least a couple of indexes. Also, you should capture both pat and ref flags in the same pass of the table and update them...
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 19, 2020 at 3:46 pm
SELECT OrderNumber
FROM #X_ReadytoPick
GROUP BY OrderNumber
HAVING MAX(CASE WHEN OpsStatus = 'Okay' AND SOPstatus IN ('Acknowledgement', 'DocsPrinted') THEN 0 ELSE 1 END) = 0
ORDER BY OrderNumber /*optional, of course*/
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 19, 2020 at 1:57 pm
First try "SELECT COUNT(*) FROM sys.dm_exec_cached_plans", to see how many cached plans you actually have. Then go from 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".
May 18, 2020 at 2:46 pm
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity 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".
May 18, 2020 at 2:44 pm
My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted. It might be that...
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 16, 2020 at 4:58 am
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is...
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 16, 2020 at 4:57 am
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer...
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, 2020 at 1:30 pm
An alternative is like below. Which performs better depends on total data volume and index(es) present:
Edit: I'm at work so I have only a limited time to review all posts,...
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 4, 2020 at 6:19 pm
You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just...
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".
April 29, 2020 at 5:22 pm
The full, unlimited Cartesian join will be huge overhead. At least turn the match of the tables into a standard join:
select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
from SampleTable...
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".
April 29, 2020 at 1:59 pm
D'OH, quite right, a copy/paste mishap I think. I have corrected the original code.
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".
April 27, 2020 at 5:08 pm
Thanks for the queries above. For the PL update, it's not working as expected .
OK, adjust it as you need to. You didn't provide enough details to know which...
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".
April 24, 2020 at 7:47 pm
I completely agree with Celko.
At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for...
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".
April 24, 2020 at 7:46 pm
Viewing 15 posts - 2,401 through 2,415 (of 7,619 total)