craig.bobchin (8/28/2015)

---

Jason,

I modified your code for our table/fields and it doesn't quite work. I set the threshold to >=4 and of the 30k records in the table 8818 came back...

The code I posted earlier shows how to handle the "how to assign a common value to a new field based on the matches" problem.

The idea is to find the...

Sean Lange (8/27/2015)

---

I agree but without an explanation it doesn't make any sense.

Here is an article that discusses this hint. There...

From what I can see, it looks like the following will work for you...

AND ((@JobStatus = 'O' AND JCCM.JobStatus = 1) OR (@JobStatus = 'C' AND JCCM.JobStatus > 1)

Also, get...

I've done something very similar but it far from fast, especially on larger sets.

The following is a simplified version of what I did in the past but it does illustrate...

No worries. 🙂

In any case Jacob's solution is what you're looking for. It should work in pretty much any RDBMS.

Jacob Wilkins (8/19/2015)

---

Hmmm...I'm not sure I follow. The results of my original query exactly match the contents of test2, his desired results, with his sample data.

It looks like your...

At 1st glance, it looks like something along these lines should work...

DECLARE

@StartLastYear DATE,

@StartThisYear DATE

SELECT

@StartLastYear = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP) -1, 1, 1),

@StartThisYear = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)

SELECT DISTINCT

C.OldDealerID,

C.NameLong,

C.NameShort,

C.MailingState,

CC.NameFirst,

CC.NameLast,

CC.Phone,

CC.FaxPhone,

MR.Name,

CC.Address,

CC.City,

CC.Zip,

C.MailingStreet,

C.MailingCity,

CC.EMailAddress,

C.DateLastReview,

HC.HoldingCoDescription,

---Funded, Approved,Conditioned, Declined

facd.FundedLastYear,

facd.ApprovedLastYear,

facd.ConditionedLastYear,

facd.DeclinedLastYear

--- and so on

FROM

Channels...

Jacob Wilkins (8/19/2015)

---

For a 2008-friendly version (as Jason indicated, his won't work prior to 2012, because ORDER BY was not supported for aggregate window functions yet; it only worked for...

The following works in SQL 2012 & 2014, but I'm not certain about 2008 (or R2). Unfortunately, I don't have 2008 to test with.

(So... Sorry in advance if it...

This is just a different variation of Lynn's solution...

IF OBJECT_ID('tempdb..#tabA') IS NOT NULL

DROP TABLE #tabA;

create table #tabA

(

memid varchar(10),

monthStartDate datetime

);

--drop table #tabA

Insert into #tabA (memid,monthStartDate)

values(123,'2014-01-01'),

(123,'2014-03-01'),

(123,'2014-04-01'),

(123,'2014-05-01'),

(123,'2014-06-01'),

(123,'2014-07-01'),

(123,'2014-08-01'),

(123,'2014-09-01'),

(123,'2014-10-01'),

(123,'2014-11-01'),

(123,'2014-12-01'),

(222, '2014-01-01'),

(222 , '2014-02-01'),

(222, '2014-03-01'),

(222...

I don't see where you mentioned a "sort issue" before... What exactly is the sort issue?

Try this...

DECLARE

@String1 VARCHAR(200) = 'Panadol',

@String2 VARCHAR(200) = 'XYZadol';

WITH n (n) AS (

SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)

), Tally (n) AS (

SELECT TOP (SELECT MAX(LEN(x.String)) FROM (VALUES (@String1), (@String2))...

The following should do what you're trying to do... (you just need to wrap it in a function)

DECLARE

@String1 VARCHAR(200) = 'Panadol',

@String2 VARCHAR(200) = 'XYZadol';

WITH n (n) AS (

SELECT 1...

The following should give you better performance than what you currently have...

WITH cte AS (

SELECT

MIN(i.OrderID) AS OrderID,

i.AccountID,

i.StatusID

FROM

#Input i

WHERE

(i.StatusID = 1 AND i.Value = 20) OR (i.StatusID =...