Solved

SQL Query issue

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now