Sean Lange (1/7/2016)

---

Zohaib Anwar (1/7/2016)

---

Hi,

I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in...

Here it is..

Get_NameDetails

1.Set Set lcLast_Name = get (character position of lcSeperator in 1301_Prestage_PID.cname) - 1 from 1301_Prestage_PID.cname[48 Charactrers]

2.Set lcFirst_Name = get (character position of lcSeperator in 1301_Prestage_PV1.cname) + 1...

ChrisM@Work (6/10/2015)

---

You should be expecting a final query something like this:

;

WITH PreAgg AS (

SELECT

p.id,

HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,

paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount - tdPaid.CreditAmount ELSE...

If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:

...

GilaMonster (6/10/2015)

---

You prefer to add a hint that causes incorrect results rather than address...

Joie Andrew (6/10/2015)

---

How big are the Vendors and Payables tables? It seems like most of the query is consumed with this join:

left join Payables p with (nolock) on p.SalespersonID =...

ChrisM@Work (6/10/2015)

---

Zohaib Anwar (6/10/2015)

---

I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then...

I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots...

Dohsan (5/28/2015)

---

Collation will determine how data is sorted and compared, for example should case sensitivity be taken into account when comparing strings etc.

If in this case each column has a...

Sean Lange (4/9/2015)

---

Zohaib Anwar (4/9/2015)

---

Thanks for the reply Sean

Can I do it following way ??

WITH ACOALERTCTE

AS (

SELECT a.lastprdate

,a.lastprtime

FROM acoalert a

WHERE a.coalertid = 3000

)

SELECT TOP 1000 f.foordno AS "Order #"

,f.foprogno AS...

Thanks for the reply Sean

Can I do it following way ??

WITH ACOALERTCTE

AS (

SELECT a.lastprdate

,a.lastprtime

FROM acoalert a

WHERE a.coalertid = 3000

)

SELECT TOP 1000 f.foordno AS "Order #"

,f.foprogno AS "Program #"

,f.alertmssg AS "Pick...

Luis Cazares (3/19/2015)

---

As previously stated, you're getting a join on matched rows only. The problem is that your first row on table A matches both rows on table B and...

Ok, Thanks for the explanation.

select * from A INNER JOIN B ON A.ID=B.ID

Please let me know how can we modify above query so that it will gives us only...

No, I just did it to explain the situation. I...

GilaMonster (3/19/2015)

---

No, SQL's giving you an inner join, it's just that your data has every row in one table matching every row of the other table because they're all the...