?
Solved

SQL Query issue

Posted on 2011-09-21
3
Medium Priority
?
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

765 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