SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with Count and Null Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Alexd91
Starting Member

2 Posts

Posted - 01/11/2014 :  16:47:06  Show Profile  Reply with Quote
Hello,

I joined different tables and got a result like this:

result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL

Now I want to count the results by counting the processes and group them by the result.

But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).

But now the result "best" disappears, because that column has no date.

Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.

I want to have this result for process4:
count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best

But I got only:
count | result
------ -------
1 | good
1 | bad

I have tried a lot, but nothing works.

The whole result (best, good, ok, bad) are stored in an other table and I joined it.

Could somene help me?

It would be very great :)

Thanks,
Alex

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/12/2014 :  01:37:12  Show Profile  Reply with Quote
Can you explain your output. How did you get 0 for on for process4? you've record below
ok | process4 | 1 | 12.10.2013
so shouldnt it be 1 for ok?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Alexd91
Starting Member

2 Posts

Posted - 01/12/2014 :  07:02:53  Show Profile  Reply with Quote
No, because it should only count the latest date and result for each goal.
For goal = 1:
ok | process4 | 1 | 12.10.2013
good | process4 | 1 | 03.01.2014

And 03.01.2014 > 12.10.2013 so, +1 for good.

But that works fine, i just want to have the result with 0 when count=0.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/12/2014 :  14:20:20  Show Profile  Reply with Quote
quote:
Originally posted by Alexd91

No, because it should only count the latest date and result for each goal.
For goal = 1:
ok | process4 | 1 | 12.10.2013
good | process4 | 1 | 03.01.2014

And 03.01.2014 > 12.10.2013 so, +1 for good.

But that works fine, i just want to have the result with 0 when count=0.



Sorry I dont understand . In first post you specified you run it for each process. Can you tell me how you got bad as 1? bad for process4 is on 30.12.2013 which is not latest date.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

IleanaAlvare
Starting Member

Canada
7 Posts

Posted - 01/31/2014 :  09:59:21  Show Profile  Reply with Quote
Hey Alex,

Did you find a way to solve this problem?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000