• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

SQL Query Question

Hi,

I'm struggling with a simple issue with a query.  Your knowledgable assistance is greatly appreciated:

Problem
target field in the query below begins with a C, hence I want to add d target like 'C%'

Where do I place this in the query below:

USE NimbusSLM
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
declare @sqlcmd nvarchar(1000)
set @sqlcmd = N'Select
CONVERT(datetime,CAST(MONTH(sampletime) AS varchar(2)) + ''-09-'' +
CAST(YEAR(sampletime) AS char(4))) AS REPORTPERIOD,
CONVERT(char(10),sampletime,120) As
sampleDate,(Sum(samplevalue)/Count(samplevalue)) As Avgsamplevalue,
min(samplevalue) As MINIMUM ,max(samplevalue) As MAXIMUM, d.origin,d.source,d.hubname,d.target INTO T_Daily_Disk_Percent  
From [dbo].[V_QOS_Disk_Percent_' +
left(cast(datename(month,dateadd(mm,-1,getdate())) as nvarchar(10)),3) + N'_' +
cast(datepart(yyyy,dateadd(mm,-1,getdate())) as nvarchar(4)) + N'] d Group By day(CONVERT(char(10),sampletime,120)),
d.origin,d.source,d.target,d.hubname,
CONVERT(datetime,CAST(MONTH(sampletime) AS varchar(2)) + ''-09-'' +
CAST(YEAR(sampletime) AS char(4))),
CONVERT(char(10),sampletime,120)
order by day(CONVERT(char(10),sampletime,120)) '

exec sp_executesql @sqlcmd
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
0
penny_behrensmeyer
Asked:
penny_behrensmeyer
1 Solution
 
JestersGrindCommented:
Try this.

declare @sqlcmd nvarchar(1000)
set @sqlcmd = N'Select
CONVERT(datetime,CAST(MONTH(sampletime) AS varchar(2)) + ''-09-'' +
CAST(YEAR(sampletime) AS char(4))) AS REPORTPERIOD,
CONVERT(char(10),sampletime,120) As
sampleDate,(Sum(samplevalue)/Count(samplevalue)) As Avgsamplevalue,
min(samplevalue) As MINIMUM ,max(samplevalue) As MAXIMUM, d.origin,d.source,d.hubname,d.target INTO T_Daily_Disk_Percent  
From [dbo].[V_QOS_Disk_Percent_' +
left(cast(datename(month,dateadd(mm,-1,getdate())) as nvarchar(10)),3) + N'_' +
cast(datepart(yyyy,dateadd(mm,-1,getdate())) as nvarchar(4)) + N'] d
WHERE d.target LIKE ''C%''
Group By day(CONVERT(char(10),sampletime,120)),
d.origin,d.source,d.target,d.hubname,
CONVERT(datetime,CAST(MONTH(sampletime) AS varchar(2)) + ''-09-'' +
CAST(YEAR(sampletime) AS char(4))),
CONVERT(char(10),sampletime,120)
order by day(CONVERT(char(10),sampletime,120)) '

Greg

0
 
awking00Commented:
You could also use -
WHERE CHARINDEX('C',d.target) = 1
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now