DECLARE@MeterContract TABLE

(

MeterContract INT IDENTITY(1, 1),

MeterID INT NOT NULL,

ContractID INT NOT NULL,

StartDate DATE NOT NULL,

EndDate DATE NOT NULL

)

INSERT@MeterContract

VALUES(1, 1, '01-JAN-2011', '01-AUG-2011'),

(1, 1, '01-MAR-2012', '31-DEC-2012'),

(1, 2, '01-JAN-2011', '01-JAN-2012'),

(1, 2, '01-AUG-2012', '31-DEC-2012'),

(1, 3,...

SELECT ASCII(SUBSTRING(Col1, n, 1))

Here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 is examples of both moving average, weighted average and weighted moving average.

Jeff Moden (5/3/2011)

---

What does the following snippet from your code do?

,1 S SeqID FROM ...

Jeff Moden (5/3/2011)

---

BWAAA-HAAAA!!! With only 55 rows of test data on one hand and only 8788 from the "Adventureworks" hand, I'd have to say no one hammered anything to...

See this topic. We hammered the subjet to death...

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c4d4f72c-35a7-43f9-8dbc-5517a14de5f2

Using Adventureworks

SELECTx.PurchaseOrderID AS LowID,

y.PurchaseOrderID AS HighID,

MIN(x.ProductItems) AS ProductItems

INTO#Stage

FROM(

SELECTPurchaseOrderID,

ProductID,

COUNT(*) OVER (PARTITION BY PurchaseOrderID) AS ProductItems

FROMPurchasing.PurchaseOrderDetail

) AS x

INNER JOIN(

SELECTPurchaseOrderID,

ProductID,

COUNT(*) OVER (PARTITION BY PurchaseOrderID) AS...

SELECTe.CarrierMemID,

CASE

WHEN COUNT(*) >= 11 THEN 'Pass'

ELSE 'Fail'

END AS Result

FROM(

SELECTDATEADD(MONTH, Number, @StartDate) AS theMonth

FROMmaster..spt_values

WHEREType = 'P'

AND Number BETWEEN 0 AND 11

) AS v

INNER JOIN#Enrollment AS e ON e.EffDate <= v.theMonth

AND e.TermDate...

I did a series of tests here

http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx

http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-records-in-one-table-not-present-in-another-tableAgain.aspx

Someone called? 😉

DECLARE@Sample TABLE

(

theDate DATE

)

INSERT@Sample

(

theDate

)

VALUES('20110314'),

('20110315'),

('20110316'),

('20110414'),

('20110415'),

('20110416')

SELECT theDate,

DATEADD(DAY, DATEDIFF(DAY, 4, DATEADD(MONTH, DATEDIFF(MONTH, -1, DATEADD(DAY, -14, theDate)), -1)) / 7...

DECLARE@Sample TABLE

(

hic CHAR(6) NOT NULL,

qry VARCHAR(7) NOT NULL

)

INSERT@Sample

SELECT'00005A', '9130813' UNION ALL

SELECT'00005A', '9130850' UNION ALL

SELECT'00010A', '9130813' UNION ALL

SELECT'00010A', '9130850' UNION ALL

SELECT'00010A', '915216' UNION ALL

SELECT'00010A', '915416' UNION ALL

SELECT'00021A', '9130813' UNION ALL

SELECT'00021A', '9130850'

SELECTs.hic,

s.qry,

f.qry

FROM@Sample...

SELECT MIN(qry_id), MAX(qry_id), hic_number

FROM Table1

GROUP BY hic_number

HAVING COUNT(*) = 2

SELECTq1.qry_id,

q2.qry_id,

h.hic_number

FROM(

SELECTDISTINCT

qry_id

FROMdbo.DEV_QRY_QC_HCC_131

) AS q1

INNER JOIN(

SELECTDISTINCT

qry_id

FROMdbo.DEV_QRY_QC_HCC_131

) AS q2 ON q2.qry_id > q1.qry_id

CROSS JOIN(

SELECTDISTINCT

hic_number

FROMdbo.DEV_QRY_QC_HCC_131

) AS h

WHERE NOT ((er.Art_Tons - er.Voy_Tons) > -0.5 AND (er.Art_Tons - er.Voy_Tons) < 0.5 )

WHERE (er.Art_Tons - er.Voy_Tons) <= -0.5 OR (er.Art_Tons - er.Voy_Tons) >= 0.5

My solution runs in about 400 milliseconds using 164k reads.

It produces the 163 ID's from the 64,178 record sample set.

SET NOCOUNT ON

-- Solution here

CREATE TABLE #Values

...

A generic solution is found here

http://weblogs.sqlteam.com/peterl/archive/2008/10/27/Ordinal-your-numbers.aspx