Solved

SQL Query issue

Posted on 2011-09-21
3
429 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 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
point in time restore in SQL server 26 46
IF SQL Query 12 29
getting error while running below query  in sql 2 16
SQL Server remove line breaks and tabbed 2 9
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

730 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