Solved

SQL Query issue

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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