In the combined cte just add a where clause to both tables to filter out the null records.

Old school query without CTE and windowing functions:

SELECT MachineID, InstanceKey, TimeKey, RevisionID, rowversion, Account, SchemaClass FROM TABLE_A

where not exists(

SELECT TABLE_B.MachineID FROM TABLE_B

where TABLE_B.MachineID = TABLE_A.MachineID

and TABLE_B.InstanceKey = TABLE_A.InstanceKey

and TABLE_B.TimeKey >...

This appears to be data encoded in EBCDIC format. Some type of translation into the ASCII char set would be required. You may not be able to connect...

8 years ago I also was asked to work on "EAV Magic" system. "EVA Magic" was not quite as extreme as "vision". It stored the meta data in...

If do try and use a *.csv (Comma Separated Values) driver it will probably work better on data like this:

IntColumn1 CharColumn2 CharColumn3

1,"Row1Col2","Row1Col3"

2,"Row2Col2","Row2Col3"

In the olden days we did this kind of thing with BCP. This still works ok for ad hoc imports, but it is not the easiest tool in the...

As a general rule I don't like putting business logic in triggers if there is any other reasonable way to accomplish the result. If the logic in your function...

whoops I did not move the order by.

SELECT

Prod.*,

ProdInfo.*,

dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,

dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice

FROM NetPortal_Ecommerce_Categories Cat

INNER JOIN NetPortal_Ecommerce_ProductCategory_Link CatLink ON

CatLink.CategoryId = Cat.ID

INNER JOIN NetPortal_Ecommerce_Products Prod ON

Prod.ID =...

try this:

SELECT

Prod.*,

ProdInfo.*,

dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,

dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice

FROM NetPortal_Ecommerce_Categories Cat

INNER JOIN NetPortal_Ecommerce_ProductCategory_Link CatLink ON

CatLink.CategoryId = Cat.ID

INNER JOIN NetPortal_Ecommerce_Products Prod ON

Prod.ID = CatLink.ProductId

AND (Prod.AvailableDate IS NULL OR...

No problem

I would agree that the clustered index should be chosen carefully. Too many people just make it the primary key identity column. On an OLTP system this is...

Again it is hard to assist here because you don't list a table structure. Your solution is using columns that don't exist is your sample data. You also...

It helps if you put a table def in your question.:Whistling: Assuming Yearmm is an int this will work.

declare @endDate datetime=getdate()

declare @startDate datetime

set @startDate = dateadd(mm, -12, @endDate)

-- validate...

If you want and even more difficult task, try explaining to management types why this is not simple and easy thing to do.

Adding a fixed amount to identity keys can...

Rather than using views I have often used Table Value Functions to achieve modularization. I first write a lower level detail function that does the filtering and returns detailed...