August 27, 2017 at 12:45 am
I am able to query Total number of Devices from below CTE.
with Alarmreport (ID,SiteName,DEVICENAME)
as
(
SELECT inp.[ID] as ID
,substring (inp.[NAME],1,3) as SiteName,
oup.NAME as DEVICENAME
FROM [SRV-RVS].[dbo].alaraminput as inp
inner join [dbo].devicelist as oup
on inp.ID=oup.ID
where oup.NAME like '%sas%'
group by inp.ID,inp.NAME,oup.NAME
)
select SiteName, DEVICENAME, count(*) from Alarmreport
group by DEVICENAME,SiteName
C
The query returns the total number of devices in each site ( sitename)
I am trying to do this with another query without CTE but somehow couldn't do it .
how to return the same result without CTE?
August 27, 2017 at 6:45 am
Why? If the CTE works, why not use it?
You can do the same thing with a subquery, but it might be harder to read.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2017 at 7:40 pm
GilaMonster - Sunday, August 27, 2017 6:45 AMWhy? If the CTE works, why not use it?You can do the same thing with a subquery, but it might be harder to read.
I can also do with the belo query .
SELECT oup.name, inp.[ID] as ID
, min (substring (inp.[NAME],1,3)) as SiteName,count (inp.ID) Total
FROM [SRV-RVS].[dbo].alaraminput as inp
inner join [dbo].devicelist as oup
on inp.ID=oup.ID
where oup.NAME like '%sas%'
group by inp.ID,oup.NAME
order by name asc
Can we do this with a case statement or using partitionby function?
August 27, 2017 at 7:42 pm
GilaMonster - Sunday, August 27, 2017 6:45 AMWhy? If the CTE works, why not use it?You can do the same thing with a subquery, but it might be harder to read.
i am experimenting more with sql statement to see how they are working for a given scenrario.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply