ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,896 through 5,910 (of 10,143 total)

RE: How do you use a sub query in a join?

dwilliscp (8/15/2012)
... we are guessing that all notifications fall under the highest delivery+item. Still this might be our best shot...
Of course, unless there's something in Notifications to identify which delivery/item...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 16, 2012 at 2:13 am

#1526382
RE: datetime conversion challenge

BrainDonor (8/16/2012)
Although a bit dated (no pun intended) there is an interesting article by Robyn Page for various date and time conversions - http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/.
Very good it is too.
SELECT
DateFromString,
DateBackToString...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 16, 2012 at 1:55 am

#1526361
RE: How do you use a sub query in a join?

Looks like you need to roll up ztb_Carrier_Delivery_Metrics too. Try this:
SELECT
d.MAX_Delivery,
d.MAX_Delivery_Item,
n.Sales_Order_Number,
d.REF_DOC,
n.Notification_cnt
FROM (
SELECT
REF_DOC,
MAX_Delivery = MAX(Delivery),
MAX_Delivery_Item = MAX(Delivery_Item)
FROM ztb_Carrier_Delivery_Metrics
GROUP BY REF_DOC) d
INNER JOIN (
SELECT
sales_order_number,
Notification_cnt =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 15, 2012 at 8:16 am

#1526013
RE: Index Range_scans

SQLSACT (8/15/2012)
...
Given all of this, is it safe to say that, where possible, make you indexes unique?
You've seen the benefit that a unique index can bring, but be careful with...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 15, 2012 at 5:06 am

#1525947
RE: Insert records into one table form other table

This should replace both original queries:
INSERT INTO tableA (Aid, ACode, ATimestamp)
SELECT
Aid
,ACode
,MIN(ATimestamp) ATimestamp
FROM dbo.ALog al
WHERE AId > 0
AND ACode IN ('RunQuote', 'Generatedocument')
GROUP BY ACode, Aid
ORDER BY ACode, Aid
-...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 15, 2012 at 3:50 am

#1525927
RE: How was the record deleted!!

SJanki (8/14/2012)
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000
There is another functionality where another...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 15, 2012 at 3:40 am

#1525923
RE: save varible in procedure

EXEC(@aa)
There are a number of errors in this stored procedure - would you be interested in knowing what they are?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 15, 2012 at 2:01 am

#1525894
RE: Querying Data From One Table Against Another Table For All Results

Lynn Pettis (8/14/2012)
ChrisM@Work (8/14/2012)
Here's an alternative using PARSENAME()
SELECT
bi.Building_Name,
COUNT(di.DeviceNum) NumberOfDevices
FROM #Building_Information bi
INNER JOIN #Device_Information di
ON PARSENAME(bi.Subnet,4) = PARSENAME(di.IP_Address,4)
AND PARSENAME(bi.Subnet,3) = PARSENAME(di.IP_Address,3)
AND PARSENAME(bi.Subnet,2) =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 8:17 am

#1525546
RE: How do you use a sub query in a join?

dwilliscp (8/14/2012)
dwilliscp (8/14/2012)
Thanks for your help... I just could not figure out how to word the statement ... looking at some samples I found on the internet.
Thanks for the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 7:39 am

#1525515
RE: How do you use a sub query in a join?

SELECT
d.Delivery,
d.Delivery_Item,
n.Sales_Order_Number,
d.REF_DOC,
n.Notification_cnt
FROM ztb_Carrier_Delivery_Metrics d
INNER JOIN (
SELECT
sales_order_number,
Notification_cnt = COUNT(sales_order_number)
FROM ztb_IMP_Notifications
GROUP BY sales_order_number
) n
ON n.Sales_Order_Number = d.REF_DOC
WHERE d.Ref_Doc > ''
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 7:31 am

#1525501
RE: Querying Data From One Table Against Another Table For All Results

Here's an alternative using PARSENAME()
SELECT
bi.Building_Name,
COUNT(di.DeviceNum) NumberOfDevices
FROM #Building_Information bi
INNER JOIN #Device_Information di
ON PARSENAME(bi.Subnet,4) = PARSENAME(di.IP_Address,4)
AND PARSENAME(bi.Subnet,3) = PARSENAME(di.IP_Address,3)
AND PARSENAME(bi.Subnet,2) = PARSENAME(di.IP_Address,2)
GROUP BY Building_Name;
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 6:57 am

#1525484
RE: Calculating average for 3 consecutive years

WannaBee (8/14/2012)
Thank you so much Chris 🙂
That works just perfect!
You're welcome - thanks for the nice feedback 🙂
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 6:47 am

#1525472
RE: The multi-part identifier could not be bound.

kgillispie (8/14/2012)
Thanks for the help!
You're welcome, though I get the feeling that we could have given a lot more. Can you post your modified code? It's likely that folks can...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 6:02 am

#1525444
RE: Avoiding Stored Procedures

call.copse (8/14/2012)
Jeff Moden (8/14/2012)
call.copse (8/14/2012)
Point 3 Managing and using auto generated CRUD procs is definitely slower than using an ORM. I promise!
That's what I like ORMs for is basic CRUD....
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 5:47 am

#1525436
RE: Help to join two tables become one table

tantcu (8/13/2012)
Hi guys,
I'm creating two temporary table from a query:
SELECT
branch_description
,qty_order
,current_open_orders
.....
These 2 tables has the same column as branch description. Is there a way that I...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

August 14, 2012 at 3:49 am

#1525411

Viewing 15 posts - 5,896 through 5,910 (of 10,143 total)