T Sql - just current Version wanted.

Question

Post reply

T Sql - just current Version wanted.

tmmutsetse

Hall of Fame

Points: 3669
More actions
May 16, 2016 at 8:47 am

#318887

Good day experts,
I am new to Sql and i want to modify the query below to return current VERSION.Please note not Agent_Version.
I am trying to use Distinct but it doesn't bring desired results.Please help.
Kindly find the query i am getting.
Declare @datevar datetime =getdate()
select distinct
a.SiteName,
a.ComputerName,
--a.IP,
a .Agent_Version,
a.[Version],
a.[Operation_System],
a.[CreatedOn],
a.[Description],
case when b.ID IS null then 'BLACK' else 'RED' end as Color
from
(
select * from dnaDBA.dbo.PNS_Sect_6_SiteSEPDropLinkSnapshot as a
where
YEAR(CaptureDate) = year(@DateVar) and MONTH(CaptureDate) = MONTH(@DateVar)
) as a
left join
(
select * from dnaDBA.dbo.PNS_Sect_6_SiteSEPDropLinkSnapshot as a
where
YEAR(CaptureDate) = year(dateadd(month,-1,@DateVar)) and MONTH(CaptureDate) = MONTH(dateadd(month,-1,@DateVar))
) as b on b.ComputerName = a.ComputerName

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sestell1 SSChampion Points: 10230 More actions · Answer 1

Hello,

I'm afraid I'm not quite sure what you are asking.

You said that you want to return VERSION. I see that you have it included in your query, so it should be returned.

You then said that you tried using DISTINCT. Unfortunately, without knowing what your expected output should be, I can't really tell why you are trying to use DISTINCT.

Could you provide sample table structures, sample data, and expected output based on the sample data?

drew.allen SSC Guru Points: 76944 More actions · Answer 2

This is usually done with a CTE with ROW_NUMBER(). I'm showing the equivalent derived table version, since your original query used derived tables rather than CTEs.

I've also assigned your parameter the beginning of the month, instead of the current date, so that I could more easily change your WHERE clauses to be SARGable.

DECLARE @datevar DATETIME =DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

SELECT DISTINCT

a.SiteName,

a.ComputerName,

--a.IP,

a .Agent_Version,

a.[Version],

a.[Operation_System],

a.[CreatedOn],

a.[Description],

CASE WHEN b.ID IS NULL THEN 'BLACK' ELSE 'RED' END AS Color

FROM (

SELECT *, ROW_NUMBER() OVER(PARTITION BY a.ComputerName ORDER BY a.[Version] DESC) AS rn

FROM dnaDBA.dbo.PNS_Sect_6_SiteSEPDropLinkSnapshot AS a

WHERE CaptureDate >= @datevar

) AS a

LEFT JOIN (

SELECT *, ROW_NUMBER() OVER(PARTITION BY a.ComputerName ORDER BY a.[Version] DESC) AS rn

FROM dnaDBA.dbo.PNS_Sect_6_SiteSEPDropLinkSnapshot AS a

WHERE CaptureDate >= DATEADD(MONTH, -1, @datevar)

AND CaptureDate < @datevar

) AS b

ON b.ComputerName = a.ComputerName

AND b.rn = 1

WHERE a.rn = 1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 3

Thanks,

The query is working perfect but the problem now is that query must run in SSRS Dataset and when i try to upload the file to report Manager it returns errors.

The Value expression for the text box ‘ComputerName2’ refers to the field ‘Computer_name’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsFieldReference) Get Online HelpThe Value expression for the text box ‘Description2’ refers to the field ‘COMPUTER_DESCRIPTION’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsFieldReference) Get Online Help

The Value expression for the text box ‘IP2’ refers to the field ‘IP_ADDR1_TEXT’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsFieldReference) Get Online Help

The Value expression for the text box ‘Description3’ refers to the field ‘COMPUTER_NAME’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsFieldReference) Get Online Help

The Value expression for the text box ‘IP3’ refers to the field ‘IP_ADDR1_TEXT’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsFieldReference) Get Online Help

Thanks for your help

drew.allen SSC Guru Points: 76944 More actions · Answer 4

The report is looking for COMPUTER_NAME, but your original query has ComputerName. This is the main argument for using a CONSISTENT naming convention. I prefer separating pieces with an underscore rather than using camel case.

Your report is also looking for IP_ADDR1_TEXT, but your original query had IP commented out. Again, your naming convention is inconsistent, and you probably don't want to comment out IP.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA