Help with Query - 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

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

andy.cater@usbank.com
Starting Member

2 Posts

Posted - 2009-02-04 : 13:55:57

Hello, new to this forum and new to SQL. Hoping someone can help me along. Thanks!

I am trying to display all UserID (Table: Users) with AccessLevel Greater than or equal to 2 then show counts where NotificationTime (Table: HoldList) is less than and greater than 14 days

My Query so far:
SELECT COUNT(apps.Tech) FROM Apps LEFT OUTER JOIN HoldList ON Apps.ID = HoldList.AppID WHERE OpenClosed = 'Hold' AND Tech IN (SELECT UserID FROM Users WHERE AccessLevel >= 2 AND UserID NOT LIKE 'U_%%') AND HoldList.NotificationTime > '2009:02:18:00:00:00' GROUP BY apps.tech

My Problem:
This query only displays counts for UserID with a hold associated with them, If they have no holds I want them to display as 0, as it is now my data and column headers do not match up.

Results I want to see:
User1 user2 user3 user4
Holds Over 14 days: 2 0 0 0
Holds less 14 days: 0 2 0 0

Sample data from the tables involved:

Table: HoldList
AppID NotificationTime
766 2009:02:10:00:00:00
708 2009:02:10:00:00:00
521 2009:02:27:00:00:00
532 2009:02:27:00:00:00

Table: Apps
ID OpenClosed Tech
322 Hold user3
488 Hold user3
521 Hold user2
532 Hold user2
708 Hold user1
766 Hold user1

Table: Users
UserID AccessLevel
User1 7
user2 3
user3 2
user4 2
user5 1
user6 1
U_user1 2
U_user2 2
U_user3 2

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-04 : 19:17:32

This should give you the results as a list. If you want to pivot on UserID (columns), search this forum for Dynamic Pivot.


SELECT  apps.Tech, 
		SUM(CASE WHEN HoldList.NotificationTime > dateadd(d, 14, datediff(d, 0, getdate())) THEN 1 ELSE 0 END) AS 'Holds Over 14 days',
		SUM(CASE WHEN HoldList.NotificationTime <= dateadd(d, 14, datediff(d, 0, getdate())) THEN 1 ELSE 0 END) AS 'Holds Less 14 days'
FROM Apps 
LEFT OUTER JOIN HoldList 
	ON Apps.ID = HoldList.AppID 
WHERE OpenClosed = 'Hold' 
	AND Tech IN (SELECT UserID FROM Users WHERE AccessLevel >= 2 AND UserID NOT LIKE 'U_%%') 
GROUP BY apps.tech

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 07:44:20

your sample data and output doesnt match how does user2 got 2 under holds less than 14 days though the notification value is 2009:02:27:00:00:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources