Tricky Different Status Fields

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 2000 Forums

Transact-SQL (2000)

Tricky Different Status Fields

Author

Topic

BCullenward
Starting Member

28 Posts

Posted - 2005-07-14 : 12:04:43

I'm having some difficulty with a query, There are 3 tables in this: Names, StatusTime, Status. The Names Table has a NameID and Name, the StatusTime has the NameID, a Status, and time and the Status table contains all the different possible statuses.

I want to get the name, status, and a count of how many statuses of that type the person has for the current week, if there is no status type I want it to say there were zero.

For example:


Name             StatusTime                 Status
------           -----------                ---------
NameID           NameID                     StatusID
Name             StatusID                   Status
                 Status
                 StatusTime

I've linked the NameIDs together and had the statuses or statusIDs linked, but when I set the requirements for the statustime being in the current week, it only returns the records that aren't null.

Here is my query
SELECT Names.Name, StatusTime.Status, ISNULL(COUNT(StatusTime.NameID), 0) AS icount
FROM StatusTime RIGHT OUTER JOIN
Names ON StatusTime.NameID = Names.NameID
WHERE (DATEPART(wk, StatusTime.StatusTime) = DATEPART(wk, GETDATE())) AND (DATEPART(yyyy, StatusTime.StatusTime) = DATEPART(yyyy, GETDATE()))
AND (StatusTime.StatusID = 1)
GROUP BY Names.Name, StatusTime.Status

There should be 20+ Names and all of them should have icount as a zero except for one (which tomorrow it could change and everyone could have several).

So my question is, what do I need to do to my statement to get it to return everything like the following:


Name      Status         icount
--------------------------------
John      StatA          0
Bob       StatA          0
Joe       StatA          1
Jane      StatA          0
John      StatB          12
Bob       StatB          99
Joe       StatB          0
Jane      StatB          8
John      StatC          0
Bob       StatC          0
Joe       StatC          0
Jane      StatC          0

Instead of:

Name      Status         icount
--------------------------------
Joe       StatA          1
John      StatB          12
Bob       StatB          99
Jane      StatB          8

Your help is appreciated

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 12:51:58

Can you throw up some dml and ddl for the problem?

BCullenward
Starting Member

28 Posts

Posted - 2005-07-14 : 12:56:25

quote:
Originally posted by ehorn

Can you throw up some dml and ddl for the problem?

what? dml? ddl? (forgive me, I'm new to sql server and the lingo)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 13:00:08

ddl (data definition language) would be the create table scripts and dml (data manipulation language) would be the insert statements for some sample data.

BCullenward
Starting Member

28 Posts

Posted - 2005-07-14 : 13:05:59

I gave what the sample data was from each table. I am trying to create a view that will return a 0 on the count for a status that is in the status table but is not in the statustime table for the current week. This is the only thing I am having difficulty with, as I am able to pull all the records in the StatusTable for the current week as well as link the names to them.

BCullenward
Starting Member

28 Posts

Posted - 2005-07-15 : 17:06:04

anyone?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-15 : 17:49:57

Give this a spin:

SELECT Names.Name, Names.Status, ISNULL(COUNT(StatusTime.NameID), 0) AS icount
FROM (SELECT A.Name, A.Nameid, B.status, B.statusid FROM Name A CROSS JOIN Status B) AS Names
LEFT JOIN StatusTime
ON StatusTime.NameID = Names.NameID AND StatusTime.Statusid = Names.Statusid
AND (DATEPART(wk, StatusTime.StatusTime) = DATEPART(wk, GETDATE())) AND (DATEPART(yyyy, StatusTime.StatusTime) = DATEPART(yyyy, GETDATE()))
GROUP BY Names.Name, Names.Status

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-15 : 17:56:45

quote:
Originally posted by BCullenward

I gave what the sample data was from each table.

If nosepicker's solution doesn't work for you, then we need the information in a certain format in order for us to help you. The problem is that we need to create your table and sample data in our own SQL Server, so we need the code from you.

Please see this link for an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29085

Now check out this link for more information:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-15 : 18:24:24

If you want to get rid of the "NULL value ..." warning message, you can also do it this way:

SELECT Names.Name, Names.Status, SUM(CASE WHEN Statustime.NameID IS NOT NULL THEN 1 ELSE 0 END) AS icount
FROM (SELECT A.Name, A.Nameid, B.status, B.statusid FROM Name A CROSS JOIN Status B) AS Names
LEFT JOIN StatusTime
ON StatusTime.NameID = Names.NameID AND StatusTime.Statusid = Names.Statusid
AND (DATEPART(wk, StatusTime.StatusTime) = DATEPART(wk, GETDATE())) AND (DATEPART(yyyy, StatusTime.StatusTime) = DATEPART(yyyy, GETDATE()))
GROUP BY Names.Name, Names.Status

BCullenward
Starting Member

28 Posts

Posted - 2005-07-18 : 10:45:09

That doesn't really work for me, instead of giving all zeros for the statuses that had <Nulls> it gave the status as a null. What I need is for it to return the data in the following format.


Name             StatusTime                 Status
------           -----------                ---------
NameID           NameID                     StatusID
Name             StatusID                   Status
                 Status
                 StatusTime

Name             StatusTime                 Status
------           -----------                ---------
1 John           1 20 StatA 7-8-2005...     20 StatA
2 Bob            1 23 StatB 7-9-2005...     23 StatB
3 Joe            2 20 StatA 7-9-2005...     24 StatC
4 Jane           2 23 StatB 7-10-200...          
                 3 23 StatA 7-10-200...    
                 4 23 StatB 7-10-200...
                 1 20 StatA 7-11-200...

Needs to be

Name      Status       icount
-----     -------      ------
John      StatA          2
Bob       StatA          1
Joe       StatA          1
Jane      StatA          0
John      StatB          1
Bob       StatB          1
Joe       StatB          0
Jane      StatB          1
John      StatC          0
Bob       StatC          0
Joe       StatC          0
Jane      StatC          0

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-18 : 12:36:48

Did you see my post?

Tara

BCullenward
Starting Member

28 Posts

Posted - 2005-07-18 : 12:47:29

yes I did, and my problem is that when there are zero of one status type it does not get returned.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-18 : 12:52:03

Ummm, I think you saw nosepicker's post, not mine. My post will show you how to provide the information that we need in order to work on your problem in our own environments. Please take a look.

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-18 : 16:06:36

The reason my query didn't work is because the example data you gave is not in the same week as this week. Actually, your example data is spread over two weeks. If you make your data correct, or if you take out the two "AND" conditions of my query, you will get the results you are looking for.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 16:11:00

Too tired to figure out something better, please let me know if it works:

select n.Name, ns.Status, count(st.Status)
from (select a.nameID, b.StatusId, b.Status
        from (select distinct nameID
                from StatusTime) as a
        cross join Status as b) as ns
join names n on n.nameID = ns.nameID
left join StatusTime st on st.NameID = ns.NameID and st.StatusID = ns.StatusID
group by n.Name, ns.Status

BCullenward
Starting Member

28 Posts

Posted - 2005-07-18 : 16:49:40

mmarovic, I appreciate the tip, however when I tried that, it only returned those statuses that have been used.

Nosepicker, the first time I tried yours it didn't work. It gave me a status of <null> for those that had no status of that type, but upon trying it again it looks like it works now. I profusely thank you.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 16:56:41

Sorry, the correct code should be:

select n.Name, ns.Status,
       sum(case
             when st.Status is null then 0
             else 1
           end)
from (select a.nameID, b.StatusId, b.Status
        from (select distinct nameID
                from StatusTime) as a
        cross join Status as b) as ns
join names n on n.nameID = ns.nameID
left join StatusTime st on st.NameID = ns.NameID and st.StatusID = ns.StatusID
group by n.Name, ns.Status

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources