Solved

SQL select statment (and more) question

Posted on 2011-03-25
3
276 Views
Last Modified: 2012-05-11
I have the following SQL query set up to keep track of info on our presses.  I recently added in the "Hours of Work" (marked by a **) into the output of the select statement.  The only issue is it turns out that for the hours of work on each press we need to call from LCCNs 52000 to 52099 for the 6/c and from 55200 to 55299 for the PM and so on.  But I can't change the entire code to call from that cause the other variables need to call from only 52015 and 55215 and so on.

So my question is, is there an easy way to have just the Hours call from all the LCCN's they need?  Or I guess if there's not an easy way, is there a hard way?  Thanks for the help

/*  Change @CountType to 0 for regular counts; 2 for rework; any other number (i.e. 1) for total count.
      Change @StartDate and @EndDate to the start and end dates of the search, respectivly.

                   Press Chart
            LCCN                  Press
            52015                  6/c Press
            55215                  PM
            39010                  M700
            39100                  DE
*/
DECLARE @StartDate DATETIME, @EndDate DATETIME, @CountType INT
SET @StartDate = '2011-02-01'
SET @EndDate = '2011-02-28'
SET @CountType = 0
SELECT
Press = CASE WHEN LCCN = 52015 THEN '6/c Press'
                  WHEN LCCN = 55215 THEN 'PM'
                  WHEN LCCN = 53015 THEN 'DC'
                  WHEN LCCN = 39010 THEN 'M700'
                  WHEN LCCN = 39110 THEN 'DE'
            END,
      YEAR(UpdateDate) as 'Year',
      DATENAME(mm,UpdateDate) as 'Month',
      SUM(LaborQuantity) as 'Total',
**      SUM(Hours) as 'Hours of Work',
      COUNT(DISTINCT JobN) as 'Number of Jobs'
FROM
      JobLabor
WHERE
      UpdateDate BETWEEN @StartDate AND @EndDate
      AND LCCN IN (52015, 55215, 53015, 39010, 39110)
      AND (prefix = @CountType
            OR @CountType NOT IN (0,2))
GROUP BY
      CASE WHEN LCCN = 52015 THEN '6/c Press'
                  WHEN LCCN = 55215 THEN 'PM'
                  WHEN LCCN = 53015 THEN 'DC'
                  WHEN LCCN = 39010 THEN 'M700'
                  WHEN LCCN = 39110 THEN 'DE'
            END,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
ORDER BY
      CASE WHEN LCCN = 52015 THEN '6/c Press'
                  WHEN LCCN = 55215 THEN 'PM'
                  WHEN LCCN = 53015 THEN 'DC'
                  WHEN LCCN = 39010 THEN 'M700'
                  WHEN LCCN = 39110 THEN 'DE'
            END,
      YEAR(UpdateDate),
      MONTH(UpdateDate),
      DATENAME(mm,UpdateDate)
0
Comment
Question by:mjburgard
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 35220039
Try something like this:
SELECT CASE 
          WHEN LCCN BETWEEN 52000 AND 52099 THEN '6/c Press'
          WHEN LCCN BETWEEN 55200 AND 55299 THEN 'PM'
          WHEN LCCN BETWEEN 53000 AND 53099 THEN 'DC'
          WHEN LCCN BETWEEN 39000 AND 39099 THEN 'M700'
          WHEN LCCN BETWEEN 39100 AND 39199 THEN 'DE'
       END AS [Press]
     , YEAR(UpdateDate) as [Year]
     , DATENAME(mm,UpdateDate) as [Month]
     , SUM(CASE 
              WHEN LCCN IN (52015, 55215, 53015, 39010, 39110)
                 THEN LaborQuantity
              ELSE 0
           END) as [Total]
     , SUM(Hours) as [Hours of Work]
     , COUNT(DISTINCT CASE 
                WHEN LCCN IN (52015, 55215, 53015, 39010, 39110)
                   THEN JobN
             END) as [Number of Jobs]
FROM JobLabor
WHERE UpdateDate BETWEEN @StartDate AND @EndDate
  AND (LCCN BETWEEN 52000 AND 52099 /*52015*/
       OR LCCN BETWEEN 55200 AND 55299 /*55215*/
       OR LCCN BETWEEN 53000 AND 53099 /*53015*/
       OR LCCN BETWEEN 39000 AND 39099 /*39010*/
       OR LCCN BETWEEN 39100 AND 39199 /*39110*/)
  AND (prefix = @CountType OR @CountType NOT IN (0,2))
GROUP BY CASE 
            WHEN LCCN BETWEEN 52000 AND 52099 THEN '6/c Press'
            WHEN LCCN BETWEEN 55200 AND 55299 THEN 'PM'
            WHEN LCCN BETWEEN 53000 AND 53099 THEN 'DC'
            WHEN LCCN BETWEEN 39000 AND 39099 THEN 'M700'
            WHEN LCCN BETWEEN 39100 AND 39199 THEN 'DE'
         END
       , YEAR(UpdateDate)
       , MONTH(UpdateDate)
       , DATENAME(mm,UpdateDate)
ORDER BY Press, [Year], MONTH(UpdateDate)
;

Open in new window

0
 
LVL 1

Author Closing Comment

by:mjburgard
ID: 35245012
Worked like a charm, and thanks for cleaning up some other areas of the code in the process.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 35245995
You are most welcome.  Glad I was able to help!
Best regards and happy coding,

Kevin
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP/VB email question 4 31
SQL Insert parts by customer 12 31
Help creating a spatial object in SQL Server 4 20
TSQL query to generate xml 4 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

786 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