Solved

SQL Query issue

Posted on 2011-09-21
3
425 Views
Last Modified: 2012-05-12
For some reason I can't get this query to production any results except one at time.
Want do I have wrong?


--DECLARE @CID AS
DECLARE @ProjectStatus AS CHAR(22)
DECLARE @tech AS CHAR(22)
DECLARE @SDLCPhase AS CHAR(22)

SET @ProjectStatus = 'In Process'
SET @tech = 'Dawn'
SET @tech = 'Harlan'
SET @tech = 'Jason'
SET @tech = 'Robert'
SET @tech = 'Tim'
SET @tech = 'Ron'
--SET @tech = 'Harlan'
--SET @tech = '%' + @tech
--SET @tech = @tech + '%'
--SET @tech = ''
--SET @SDLCPhase = '*'

SELECT    
 COALESCE(tu.FIRST_NAME,'') AS [Assigned Technician]
 --tu.FIRST_NAME AS [Assigned Technician]
,  cd.CHANGEID
--, cf.UDF_CHAR5 AS 'EMT Sponsor'
--, cf.UDF_CHAR6 AS 'justification'
, cr.ROLLOUTPLAN AS 'descr'
,DATEDIFF(day, CASE WHEN dateadd(ss, cd.createdtime / 1000, '1970-01-01') = '1970-01-01' THEN NULL ELSE dateadd(ss, cd.createdtime / 1000, '1970-01-01') END, GETDATE()) AS 'Age'
, cf.UDF_LONG3 AS 'CPSA Priority'
, cf.UDF_LONG1 AS 'Tech Priority'
, sd.NAME AS 'Project Status'
, cf.UDF_CHAR1 AS 'SDLC Phase'
, cd.TITLE AS 'Project Title'
, iu.FIRST_NAME AS Requestor
, cr.IMPACTDESC AS 'Impact'
, cf.UDF_CHAR2 AS 'Requesting Department'
, CASE WHEN dateadd(ss, cf.UDF_DATE1 / 1000, '1970-01-01') = '1970-01-01'
      THEN NULL
    ELSE CONVERT(char(12), dateadd(ss, cf.UDF_DATE1 / 1000, '1970-01-01'), 101)
    END AS 'Phase Plan End Date'
, cf.UDF_CHAR4 AS 'PercentComplete'
, cf.UDF_CHAR3 AS 'ROMHrs'
, cf.UDF_LONG2 AS 'Requester Priority'
, cdf.CATEGORYNAME AS 'Category'
, scdf.NAME AS 'Sub-Category'
, id.NAME AS Item
, CASE WHEN dateadd(ss, cd.createdtime / 1000, '1970-01-01') = '1970-01-01'
            THEN NULL
            ELSE CONVERT(char(12), dateadd(ss, cd.createdtime / 1000, '1970-01-01'), 101)
            END AS 'Created Date'
, CASE WHEN dateadd(ss, cd.scheduledstarttime / 1000, '1970-01-01') = '1970-01-01'
            THEN NULL
            ELSE CONVERT(char(12), dateadd(ss, cd.scheduledstarttime / 1000, '1970-01-01'), 101)
            END AS 'Scheduled Start'
, CASE WHEN dateadd(ss, cd.scheduledendtime / 1000, '1970-01-01') = '1970-01-01'
            THEN NULL
            ELSE CONVERT(char(12), dateadd(ss, cd.scheduledendtime / 1000, '1970-01-01'), 101)
            END AS 'Scheduled End'
, CASE WHEN dateadd(ss, cd.completedtime / 1000, '1970-01-01') = '1970-01-01'
            THEN NULL
            ELSE CONVERT(char(12), dateadd(ss, cd.completedtime / 1000, '1970-01-01'), 101)
            END AS 'Completed', DATEDIFF(DAY
, CASE WHEN dateadd(ss, cd.scheduledstarttime / 1000, '1970-01-01') = '1970-01-01'
            THEN NULL
            ELSE dateadd(ss, cd.scheduledstarttime / 1000, '1970-01-01')
            END, GETDATE()) AS 'ActiveAge'
FROM        
                              servicedesk.dbo.ChangeDetails AS cd
INNER JOIN            servicedesk.dbo.AaaUser AS iu ON cd.INITIATORID = iu.USER_ID
INNER JOIN            servicedesk.dbo.ChangeResolution AS cr ON cd.CHANGEID = cr.CHANGEID
INNER JOIN            servicedesk.dbo.AaaUser AS tu ON cd.TECHNICIANID = tu.USER_ID
INNER JOIN            servicedesk.dbo.Change_Fields AS cf ON cd.CHANGEID = cf.CHANGEID
INNER JOIN            servicedesk.dbo.StageDefinition AS sd ON cd.STAGEID = sd.STAGEID
INNER JOIN            servicedesk.dbo.CategoryDefinition AS cdf ON cd.CATEGORYID = cdf.CATEGORYID
INNER JOIN            servicedesk.dbo.SubCategoryDefinition AS scdf ON cd.CATEGORYID = scdf.CATEGORYID AND cd.SUBCATEGORYID = scdf.SUBCATEGORYID
INNER JOIN            servicedesk.dbo.ItemDefinition AS id ON cd.ITEMID = id.ITEMID AND cd.SUBCATEGORYID = id.SUBCATEGORYID
WHERE    
      (sd.NAME IN (@ProjectStatus))
      AND (tu.FIRST_NAME IN (@tech) )
     
      --(tu.FIRST_NAME IN (@tech))
      --AND (tu.FIRST_NAME LIKE '%harlan%')
      --AND (tu.FIRST_NAME LIKE '%' + @tech + '%')
      --AND (tu.FIRST_NAME LIKE @tech)
           
      --AND (cf.UDF_CHAR1 IN (@SDLCPhase))
      --AND (id.NAME = @projecthierarchy AND
 --                     id.NAME = 'Child Project' OR
 --                     id.NAME <> 'Child Project')
--ORDER BY cf.UDF_LONG1</CommandText>
0
Comment
Question by:TimSweet220
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36577747
You can resort to using a lousy dynamic query or you can do it this way:
DECLARE @ProjectStatus AS CHAR(22)
DECLARE @SDLCPhase AS CHAR(22)

DECLARE @Techs TABLE (FIRST_NAME char(22) NOT NULL PRIMARY KEY)

SET @ProjectStatus = 'In Process'

INSERT	@Techs (FIRST_NAME)
SELECT	'Dawn'
UNION ALL
SELECT	'Harlan'
UNION ALL
SELECT	'Jason'
UNION ALL
SELECT	'Robert'
UNION ALL
SELECT	'Tim'
UNION ALL
SELECT	'Ron'

SELECT  COALESCE(tu.FIRST_NAME, '') AS [Assigned Technician]
		--tu.FIRST_NAME AS [Assigned Technician]
        ,
        cd.CHANGEID
		--, cf.UDF_CHAR5 AS 'EMT Sponsor'
		--, cf.UDF_CHAR6 AS 'justification'
        ,
        cr.ROLLOUTPLAN AS 'descr',
        DATEDIFF(day, CASE WHEN DATEADD(ss, cd.createdtime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
                           ELSE DATEADD(ss, cd.createdtime / 1000, '1970-01-01')
                      END, GETDATE()) AS 'Age',
        cf.UDF_LONG3 AS 'CPSA Priority',
        cf.UDF_LONG1 AS 'Tech Priority',
        sd.NAME AS 'Project Status',
        cf.UDF_CHAR1 AS 'SDLC Phase',
        cd.TITLE AS 'Project Title',
        iu.FIRST_NAME AS Requestor,
        cr.IMPACTDESC AS 'Impact',
        cf.UDF_CHAR2 AS 'Requesting Department',
        CASE WHEN DATEADD(ss, cf.UDF_DATE1 / 1000, '1970-01-01') = '1970-01-01' THEN NULL
             ELSE CONVERT(char(12), DATEADD(ss, cf.UDF_DATE1 / 1000, '1970-01-01'), 101)
        END AS 'Phase Plan End Date',
        cf.UDF_CHAR4 AS 'PercentComplete',
        cf.UDF_CHAR3 AS 'ROMHrs',
        cf.UDF_LONG2 AS 'Requester Priority',
        cdf.CATEGORYNAME AS 'Category',
        scdf.NAME AS 'Sub-Category',
        id.NAME AS Item,
        CASE WHEN DATEADD(ss, cd.createdtime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
             ELSE CONVERT(char(12), DATEADD(ss, cd.createdtime / 1000, '1970-01-01'), 101)
        END AS 'Created Date',
        CASE WHEN DATEADD(ss, cd.scheduledstarttime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
             ELSE CONVERT(char(12), DATEADD(ss, cd.scheduledstarttime / 1000, '1970-01-01'), 101)
        END AS 'Scheduled Start',
        CASE WHEN DATEADD(ss, cd.scheduledendtime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
             ELSE CONVERT(char(12), DATEADD(ss, cd.scheduledendtime / 1000, '1970-01-01'), 101)
        END AS 'Scheduled End',
        CASE WHEN DATEADD(ss, cd.completedtime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
             ELSE CONVERT(char(12), DATEADD(ss, cd.completedtime / 1000, '1970-01-01'), 101)
        END AS 'Completed',
        DATEDIFF(DAY, CASE WHEN DATEADD(ss, cd.scheduledstarttime / 1000, '1970-01-01') = '1970-01-01' THEN NULL
                           ELSE DATEADD(ss, cd.scheduledstarttime / 1000, '1970-01-01')
                      END, GETDATE()) 'ActiveAge'
FROM    servicedesk.dbo.ChangeDetails cd
        INNER JOIN servicedesk.dbo.AaaUser iu ON cd.INITIATORID = iu.USER_ID
        INNER JOIN servicedesk.dbo.ChangeResolution cr ON cd.CHANGEID = cr.CHANGEID
        INNER JOIN servicedesk.dbo.AaaUser tu ON cd.TECHNICIANID = tu.USER_ID
        INNER JOIN servicedesk.dbo.Change_Fields cf ON cd.CHANGEID = cf.CHANGEID
        INNER JOIN servicedesk.dbo.StageDefinition sd ON cd.STAGEID = sd.STAGEID
        INNER JOIN servicedesk.dbo.CategoryDefinition cdf ON cd.CATEGORYID = cdf.CATEGORYID
        INNER JOIN servicedesk.dbo.SubCategoryDefinition scdf ON cd.CATEGORYID = scdf.CATEGORYID
                                                                    AND cd.SUBCATEGORYID = scdf.SUBCATEGORYID
        INNER JOIN servicedesk.dbo.ItemDefinition id ON cd.ITEMID = id.ITEMID
                                                           AND cd.SUBCATEGORYID = id.SUBCATEGORYID
		INNER JOIN @Techs t ON tu.FIRST_NAME = t.FIRST_NAME 
WHERE   sd.NAME IN (@ProjectStatus)
        -- AND (tu.FIRST_NAME IN (@tech))
      
      --(tu.FIRST_NAME IN (@tech)) 
      --AND (tu.FIRST_NAME LIKE '%harlan%') 
      --AND (tu.FIRST_NAME LIKE '%' + @tech + '%') 
      --AND (tu.FIRST_NAME LIKE @tech) 
            
      --AND (cf.UDF_CHAR1 IN (@SDLCPhase)) 
      --AND (id.NAME = @projecthierarchy AND 
 --                     id.NAME = 'Child Project' OR
 --                     id.NAME &lt;&gt; 'Child Project')
--ORDER BY cf.UDF_LONG1</CommandText>

Open in new window

0
 

Author Comment

by:TimSweet220
ID: 36580896
That worked great!  Thanks.  What if I were required to do a dynamic query?  What do I have wrong in my original code?

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36581831
You would make @tech a delimited string and then insert that value to you SQL query before executing it using EXEC or sp_executesql.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question