CODEWITHSUNDEEP
sqllistdate
Ted Goggleye
Ted Goggleye

Reputation: 1

I need help displaying a field that is zero

In this query I'm counting work orders that were created the previous week and displaying a count by wotype2. If a wotype2 for the previous week is zero, I need the wotype2 to appear in my results. Any ideas on how to pull this off?

-- Retrieve Last Week's New Work Orders.

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime

 --get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
 --get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
 --get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())


SELECT wotype2 as WOType, COUNT(*) as NewWOsLastWeek
FROM tasks
WHERE ((OpenDATE BETWEEN 
    CONVERT(VARCHAR, @StartOfPrevWeek,7)  AND
    CONVERT(VARCHAR, @EndOfPrevWeek+1,7)) AND
    (TYPE = 'Information Systems')        AND
    (RESPONS != 'ADMIN'))
group by wotype2
order by wotype2

Upvotes: 0

Views: 40

Answers (1)

Marlin Pierce
Marlin Pierce

Reputation: 10089

You may need to do an outer join (say a left outer join) with a table which has all the possible values of wotype2.

If there is such a table, let's say it's named wotype2s, then the SQL would be:

SELECT wotype2s.wotype2 as WOType, COUNT(*) as NewWOsLastWeek
FROM wotype2s left outer join tasks on wotype2s.wotype2 = tasks.wotype2
WHERE ((OpenDATE BETWEEN 
    CONVERT(VARCHAR, @StartOfPrevWeek,7)  AND
    CONVERT(VARCHAR, @EndOfPrevWeek+1,7)) AND
    (TYPE = 'Information Systems')        AND
    (RESPONS != 'ADMIN'))
group by wotype2s.wotype2
order by wotype2s.wotype2

or, if there is no such table,

SELECT wotype2s.wotype2 as WOType, COUNT(*) as NewWOsLastWeek
FROM (select distinct wotype2 from tasks) wotype2s
  left outer join tasks on wotype2s.wotype2 = tasks.wotype2
WHERE ((OpenDATE BETWEEN 
    CONVERT(VARCHAR, @StartOfPrevWeek,7)  AND
    CONVERT(VARCHAR, @EndOfPrevWeek+1,7)) AND
    (TYPE = 'Information Systems')        AND
    (RESPONS != 'ADMIN'))
group by wotype2s.wotype2
order by wotype2s.wotype2

Upvotes: 1

Related Questions