I'm frustrated with a fairly complex problem.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I'm frustrated with a fairly complex problem.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-05-01 : 10:45:26

I have the following query:


IF EXISTS (SELECT TABLE_NAME
             FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = '#Employees')
      DROP TABLE #Employees


CREATE TABLE #Employees (
   fempno CHAR (9),
   Name CHAR (30),
   LastName CHAR (20),
   Dept CHAR (2),
   LaborType CHAR (12)
)

INSERT INTO #employees
      (
          fempno,
          name,
          LastName,
          Dept,
          labortype
      )
   SELECT prempl.fempno,
          rtrim (ltrim (dbo.prempl.FFNAME)) + ' ' + dbo.prempl.FNAME AS [Name],
          prempl.fname,
          prempl.FDEPT,
          labortype
     FROM    dbo.prempl
          CROSS JOIN
             COMANTCUSTOMIZATIONS.DBO.LABORTYPES
    WHERE year (prempl.FENDATE) = 1900
   ORDER BY prempl.fempno, labortype

SELECT emp.*,
       coalesce (L.Saturday, 0) AS [Sat],
       coalesce (L.Sunday, 0) AS [Sun],
       coalesce (L.Monday, 0) AS [Mon],
       coalesce (L.Tuesday, 0) AS [Tues],
       coalesce (L.Wednesday, 0) AS [Wed],
       coalesce (L.Thursday, 0) AS [Thu],
       coalesce (L.Friday, 0) AS [Fri]
  FROM    #employees emp
       LEFT JOIN
          (SELECT prempl.FEMPNO,
                  --(rtrim (prempl.ffname) + ' ' + prempl.fname) AS [Full Name],
                  (CASE rtrim (ltrim (jomast.fcusrchr1))
                      WHEN 'I' THEN 'Indirect'
                      WHEN 'E' THEN 'Engineering'
                      ELSE 'Direct'
                   END)
                     AS [Type],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 7
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Saturday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 1
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Sunday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 2
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Monday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 3
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Tuesday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 4
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Wednesday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 5
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Thursday],
                  coalesce (sum(CASE DATEPART (weekday, ladetail.fedatetime)
                                   WHEN 6
                                   THEN
                                      convert (NUMERIC (6, 2),
                                               (DATEDIFF ("s",
                                                          ladetail.fsdatetime,
                                                          ladetail.fedatetime
                                                ))
                                               / 3600.0
                                      )
                                   ELSE
                                      0
                                END),
                            0
                  )
                     AS [Friday]
             FROM       dbo.prempl prempl
                     LEFT OUTER JOIN
                        dbo.ladetail ladetail
                     ON prempl.fempno = ladetail.fempno
                  LEFT OUTER JOIN
                     dbo.jomast jomast
                  ON ladetail.fjobno = jomast.fjobno
            WHERE ladetail.fedatetime >=
                     dateadd (dd, 0, datediff (dd, 0, '2009-04-11 00:00:01'))
                  AND ladetail.fedatetime <=
                        dateadd (dd, 1, datediff (dd, 0, '2009-04-17 23:59:59')
                        )
           GROUP BY prempl.FEMPNO,
                    (rtrim (prempl.ffname) + ' ' + prempl.fname),
                    (CASE rtrim (ltrim (jomast.fcusrchr1))
                        WHEN 'I' THEN 'Indirect'
                        WHEN 'E' THEN 'Engineering'
                        ELSE 'Direct'
                     END)) L
       ON emp.fempno = L.fempno AND emp.labortype = L.Type
ORDER BY emp.fempno, emp.labortype



DROP TABLE #employees

I've bolded the dates that would be parameters entered by the user. It works fine BTW. However, I am using SQL 2000 and don't have SQL Reporting Services. When I try to display the information using Crystal, it has all sorts of problems with the complex SQL statement.

When I try to display it using Excel 2003 and importing external data using a database query, that doesn't work either. When I paste the query into the excel wizard (Microsoft Query) , the information comes back perfectly. However, when I select Return Data To Microsoft Excel it thinks awhile and eventually shows nothing other than "Query from DB02" which is the name of my ODBC source.

What the heck can I do to display this data for users and get them to stop yelling at me?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-01 : 11:25:34

I would put the query into an SP with the dates as parameters.
As you have temp tables in the query you may well need

if 1=0
select FEMPNO = 0, ...
(I often create an empty format table for complicated resultsets and do a select * from that.)

Some client apps need to get the format of the resultset and use the first select in the query - which in this case would be an insert into a temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 11:48:58

One quick thing... I could be wrong but I don't think INFORMATION_SCHEMA.TABLES has information about temp tables (at least it doesn't work on my machine). Instead you can use the OBJECT_ID function to check for the existence of a temp table:

SELECT OBJECT_ID('tempdb..#LaborTypes', 'U')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources