Oops... Change the cte query to this:

;WITH tagTypes (tagType)

AS

(Select '%-FRN%'

UNION

Select '%-BCK%'

UNION

Select '%-PPP%'

UNION

Select '%-MES%'

UNION

Select '%-MMT%')

CTEs cannot have the data type declared, it is implicit.

Oh, got it and tested...

WHERE Agent = CASE WHEN @test <> '' THEN @test ELSE Agent END

SQLKnowItAll (7/17/2012)

---

guerillaunit (7/17/2012)

---

I am trying to do what the below pseuedo code infers:

WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE --no where clause END

What is the correct...

guerillaunit (7/17/2012)

---

I am trying to do what the below pseuedo code infers:

WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE --no where clause END

What is the correct structure...

OH! You want all of those types no matter what. That is why you are doing that first... Ok. Do this:

;WITH tagTypes (tagType VARCHAR(10))

AS

(Select '%-FRN%' AS tagType

UNION

Select '%-BCK%'...

neb2886 (7/17/2012)

---

This is an example of how the virtual table was working:

http://sqlfiddle.com/#!3/f2bb4/1

The whole larger query is checking an anti-virus database for hosts within 5 different groups that have out of...

Try changing your INNER JOIN IDENTITY_MAP IM to a LEFT JOIN IDENTITY_MAP IM

neb2886 (7/17/2012)

---

Thanks, this query runs but its not giving me the result I was looking for.

For example, this query returned one row with MMT. However, I would like to receive...

Ok, so just insert another CASE after our groupName:

SELECT DISTINCT SA.DELETED

,PAT.VERSION

,PAT.PATTERNDATE

,SA.AGENT_VERSION

,SCL.COMPUTER_NAME AS Computer_Name

,SCO.OPERATION_SYSTEM AS Operation_System

,dateadd(s, convert(BIGINT, SA.CREATION_TIME) / 1000, '01-01-1970 00:00:00') CREATION_DTTM

,dateadd(s, convert(BIGINT, SA.LAST_UPDATE_TIME) / 1000, '01-01-1970 00:00:00') Lastupdatetime

,DATEADD(s,...

Ok, now I am lost... What is the point of your 2nd query? It does nothing for you.

Ugh... Hold on, this is messed up... Give me a minute to fix.

Try this:

SELECT DISTINCT

SA.DELETED,

PAT.VERSION,

PAT.PATTERNDATE,

SA.AGENT_VERSION,

SCL.COMPUTER_NAME AS Computer_Name,

SCO.OPERATION_SYSTEM AS Operation_System,

dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,

'01-01-1970 00:00:00') CREATION_DTTM,

dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,

'01-01-1970 00:00:00') Lastupdatetime,

DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,

'01-01-1970 00:00:00')LAST_SCAN_TIME,

PAT.PATTERNDATE AS Pattern_Date,

SCL.USER_NAME AS User_Name,

VSC.IP_ADDR1_TEXT AS IP_Add,

CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME

WHEN...

That error is saying that there is no column named tagType in the tagTypes table. In the second query, you have:

From

(

Select '%-FRN%' AS tagType

UNION

Select '%-BCK%' AS tagType

UNION

Select '%-PPP%' AS tagType

UNION

Select...

gerard-593414 (7/17/2012)

---

The AccountNum field will identify the account. This will have come from an Customer master table and the table I am updating is a Balances Table (Just to hold...

Google "sliding window SQL Server partitioning" That should get you started. Then come back with any specific questions.