Hi Don

What are you grouping by, in order to use the aggregate operator AVG() in the original query containing this subquery?

Of course, unless there's something in Notifications to identify which delivery/item...

Very good it is too.

SELECT

DateFromString,

DateBackToString...

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 =...

You've seen the benefit that a unique index can bring, but be careful with...

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

-...

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...

EXEC(@aa)

There are a number of errors in this stored procedure - would you be interested in knowing what they are?

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) =...

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...

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 > ''

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;

You're welcome - thanks for the nice feedback 🙂

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...

call.copse (8/14/2012)

---

Jeff Moden (8/14/2012)

---

That's what I like ORMs for is basic CRUD....