Don't know how... - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Don't know how...

Author

Topic

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 12:12:12

Okay... so I am pretty beginner at SQL... I need help if someone can please.

I have a table... it has columns for techs (uid), ticket numbers, ticket closure dates, and other unimportant fields for the purpose I'm interested in.

Here's a sample of one of my queries:

SELECT Tech, Count(TicketID) AS ["Number of Calls"]
FROM CallTracking
WHERE (((Month([ClosedDate]))=Month(Date())) AND ((Year([ClosedDate]))=Year(Date())) AND ((Day([ClosedDate]))=Day(Date())))
GROUP BY Tech
ORDER BY Count(TicketID) DESC;

Now, I have different counts for different date ranges, this one covering only tickets closed today.

But I have them as seperate two column queries like this:

Tech Name | # of Tickets Closed By Tech
---------------------------------------
Tech Name | # of Tickets Closed By Tech

I'd rather have the following:

Tech Name | # Closed Today | # Closed This Week | # Closed This Month
etc...

How do I get those counts based on ranges to all work in one query?

Thanks for any help you can provide!

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:21:29

[code]
select uid,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ticket closure date]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ticket closure date]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ticket closure date]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth
FROM table
GROUP BY uid
[/code]

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 12:22:26

Wow... can you help me understand this a little bit?

quote:
Originally posted by visakh16

select uid,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ticket closure date]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ticket closure date]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ticket closure date]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth
FROM table
GROUP BY uid
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:36:53

i've grouped on tech uid field to make record return as 1 per techid. then i've applied sum() to count number of occurances. for each record satisfying situation (like day=current day or month =currentmonth or week =current week) i add 1 else just 0 (ignores). so in the end what i get is count for the day, that week & that month which is what you want. the DATEADD(dd,DATEDIFF(dd,0,[ticket closure date]),0)... reduces the date field value to start of day,start of week,start of month ...

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 12:41:21

Thank you. I must be misunderstanding something though. I would like this for two more scenarios. One is for the current year and one is from a specified date. The one for the year, I would have thought I could just through in yy where mm or dd is and it would work. That does not appear to be the case. Tips?

quote:
Originally posted by visakh16

i've grouped on tech uid field to make record return as 1 per techid. then i've applied sum() to count number of occurances. for each record satisfying situation (like day=current day or month =currentmonth or week =current week) i add 1 else just 0 (ignores). so in the end what i get is count for the day, that week & that month which is what you want. the DATEADD(dd,DATEDIFF(dd,0,[ticket closure date]),0)... reduces the date field value to start of day,start of week,start of month ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:44:26

current year you mean count of each uid occurances in year?

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 12:45:07

Yes... same as you did with the others... month, week, etc... but for year instead.

quote:
Originally posted by visakh16

current year you mean count of each uid occurances in year?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:54:20

just make it year..thats all


SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ticket closure date]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 12:59:43

Yeah, that's what I thought.. tried it. Doesn't work:

Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'SUM'.

But if exclude that one line, and do just what you gave me previously (of course changing the names to correct fields) it all works fine. Year's not accepting for some reason.

quote:
Originally posted by visakh16

just make it year..thats all

SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ticket closure date]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 13:01:18

please post the full query then

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 13:01:40

select Tracker,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ClosedDate]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ClosedDate]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth
SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ClosedDate]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear
FROM CallLog
GROUP BY Tracker
ORDER BY Tracker

quote:
Originally posted by visakh16

please post the full query then

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 13:02:29

Nevermind. In posting it hear I saw the missing comma. :-|

quote:
Originally posted by MSmith5

select Tracker,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ClosedDate]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ClosedDate]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth
SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ClosedDate]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear
FROM CallLog
GROUP BY Tracker
ORDER BY Tracker

quote:
Originally posted by visakh16

please post the full query then

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 13:08:19

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 13:14:08

Okay... one last issue. Now I want the same kind of count from a specific date... October 15, 2008. How to modify for this?

quote:
Originally posted by visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 13:25:46

add:-

UM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ticket closure date]),0)='20081015' THEN 1 ELSE 0 END)AS ClosedThisMonth

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 13:46:28

Hmmmm... so this is what I have:

select Tracker,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ClosedDate]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ClosedDate]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth,
SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ClosedDate]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)='20081015' THEN 1 ELSE 0 END)AS ClosedSinceHere
FROM CallLog
GROUP BY Tracker
ORDER BY ClosedSinceHere DESC

It runs just fine. But everyone's total for the last line that you just gave me is 0. No one has closed anything since October 15, 2008. :-(

quote:
Originally posted by visakh16

add:-

UM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ticket closure date]),0)='20081015' THEN 1 ELSE 0 END)AS ClosedThisMonth

MSmith5
Starting Member

10 Posts

Posted - 2009-02-09 : 14:14:39

Got it...

select Tracker,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ClosedDate]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ClosedDate]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth,
SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ClosedDate]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear,
SUM(CASE WHEN DATEADD(dy,DATEDIFF(day,0,[ClosedDate]),0)>='20081015' THEN 1 ELSE 0 END)AS ClosedSinceHere
FROM CallLog
GROUP BY Tracker
ORDER BY ClosedSinceHere DESC

quote:
Originally posted by MSmith5

Hmmmm... so this is what I have:

select Tracker,
SUM(CASE WHEN DATEADD(dd,DATEDIFF(dd,0,[ClosedDate]),0)=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedToday,
SUM(CASE WHEN DATEADD(wk,DATEDIFF(wk,0,[ClosedDate]),0)=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisWeek,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN 1 ELSE 0 END) AS ClosedThisMonth,
SUM(CASE WHEN DATEADD(yy,DATEDIFF(yy,0,[ClosedDate]),0)=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) THEN 1 ELSE 0 END) AS CurrentYear,
SUM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ClosedDate]),0)='20081015' THEN 1 ELSE 0 END)AS ClosedSinceHere
FROM CallLog
GROUP BY Tracker
ORDER BY ClosedSinceHere DESC

It runs just fine. But everyone's total for the last line that you just gave me is 0. No one has closed anything since October 15, 2008. :-(

quote:
Originally posted by visakh16

add:-

UM(CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[ticket closure date]),0)='20081015' THEN 1 ELSE 0 END)AS ClosedThisMonth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources