| Author |
 Topic  |
|
|
jctech
Starting Member
USA
28 Posts |
 Posted - 06/21/2012 : 16:59:58
|
Hello,
I am trying to attach another column to a query and am receiving "NULL"s for all the results. The line in bold is what I added which is resulting as null. I do not understand why.
Here is the query...
select A.employid,A.chekdate,A.untstopy,B.untstopy,C.untstopy
from A
left outer join B on (A.employid=B.employid and A.chekdate=B.chekdate)
left outer join C on (A.employid=C.employid and A.chekdate=C.chekdate)
order by A.employid, A.chekdate
A,B,C are views I created. The UNTSTOPY column are the number of hours worked. A is showing regular time. B is showing overtime. C is showing PTO. A,B,and C are linked by the employid's and chekdate.
Below is an example of the results:
employid chekdate untstopy untstopy untstopy
096058 2011-06-10 27.75000 1.00000 NULL
096058 2011-06-24 80.00000 2.24000 NULL
096058 2011-07-08 77.88000 3.23000 NULL
096058 2011-07-22 80.00000 3.94000 NULL
096058 2011-08-05 64.00000 3.96000 NULL
096058 2011-08-19 80.13000 2.79000 NULL
096058 2011-09-02 40.00000 1.51000 NULL
I am pretty new to SQL so I hope I am making sense...thank you.
-Jae |
Edited by - jctech on 06/21/2012 17:12:25
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 06/21/2012 : 17:42:57
|
Looks like you have no rows in C for any of those particular employid and chekdate values. OR you have rows but the unstopy column in C is null.
try:
select * from C where employid = '096058' and chekdate = '2011-06-10'
Another possibility is that your employid values and/or your chekdate values are formatted differently in C than in A and B so they aren't correlating.
Be One with the Optimizer
TG |
 |
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/21/2012 : 17:58:51
|
When I run a query "select * from C" I get several hundred results with untstopy populated.
It is just on this multi-table query that shows the untstopy column from C populated with NULL for every and all employid.
Is the 2nd left outer join combining with the 1st left outter join...or are they independant of each other? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 06/21/2012 : 18:16:17
|
i think its problem with date values been not matching between A and C
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/21/2012 : 18:27:46
|
Tables A,B,C are created views all from the same master table.
The difference between the three views are filters in the paycode. One view is for regular time, another for overtime, and PTO time.
The paydates are the same....shared.
I'm just trying to get the different paycode hours to show in one query result. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 06/22/2012 : 15:46:00
|
>>Tables A,B,C are created views all from the same master table.
>>I'm just trying to get the different paycode hours to show in one query result.
Then why don't you just query the master table then?
>>When I run a query "select * from C"...
My point was to run it WHERE employid = '096058' and chekdate = '2011-06-10'
That is what you are correlating on so I expect there is either no row for that particular employee and date or that row has a NULL untstopy value.
Be One with the Optimizer
TG |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/22/2012 : 17:13:39
|
Here is what the original table looks like:
PAYROLCD UNTSTOPY
H-RT 80
H-OT 0.92
H-RT 50.94
S-OT 16
S-PTO 24
S-PTO 16
S-PTO 16
Those are the two main columns...with employid, chekdate, and several other columns.
I created views(A,B,C) to have the unit hours from UNTSTOPY filtered.
A is showing regular time. B is showing overtime. C is showing PTO.
I don't know of a way to query the master table where the same column would show 3 times....each filtered differently.
Would you know how to go about that...? |
Edited by - jctech on 06/22/2012 17:14:41 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 06/22/2012 : 22:48:47
|
yep. thats a typical cross tab scenario. you can use either of below two method
1.Pivot (SQL 2005 and above)
SELECT *
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
PIVOT(SUM(UNTSTOPY) FOR Category IN ([RT],[OT],[PTO]))p
2. Any version solution
SELECT employid,chekdate,
SUM(CASE WHEN Category = 'RT' THEN UNTSTOPY END) AS [RT],
SUM(CASE WHEN Category = 'OT' THEN UNTSTOPY END) AS [OT],
SUM(CASE WHEN Category = 'PTO' THEN UNTSTOPY END) AS [PTO]
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
GROUP BY employid,chekdate
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/25/2012 : 14:39:44
|
The two results are different but it looks like the pivot works. Thank you visakh16!
I've never heard of pivot queries...but I have seen some Case/End queries.
On a side note, where could I go to read/learn more about pivot queries and is there a good online course that you could recommend for SQL?
Thanks again~!
-Jae |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/25/2012 : 16:12:49
|
By the way, how would I go about removing the results for all three RT,OT,and PTO showing "NULL"?
I tried "where Category <> NULL" but I receive 0 results when I input that...thanks again. |
Edited by - jctech on 06/25/2012 16:31:02 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 06/25/2012 : 16:54:08
|
You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL
if you need more help let us know which version you are using (pivot or any version).
Be One with the Optimizer
TG |
|
|
|
stonebreaker
Yak Posting Veteran
USA
82 Posts |
Posted - 06/26/2012 : 12:19:59
|
Are the dates you are comparing actually DATE data types, or are they DATETIME or TIMESTAMP? If the latter, you would probably want to just use the date part of the DATETIME value.
Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/29/2012 : 15:26:19
|
For the pivot above, how and where would I insert "WHERE RT, OT, PTO IS NOT NULL"?
I would want to exclude lines where all three RT, OT, PTO equals NULL...
SELECT *
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
PIVOT(SUM(UNTSTOPY) FOR Category IN ([RT],[OT],[PTO]))p |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 06/29/2012 : 17:09:48
|
see if this works:
select *
from (
SELECT employid,chekdate,UNTSTOPY, Category
FROM (
SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category
FROM [table]
) t
where Category in ('RT','OT','PTO')
) d
PIVOT(SUM(UNTSTOPY) FOR Category IN ([RT],[OT],[PTO]))p
Be One with the Optimizer
TG |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 06/29/2012 : 18:04:19
|
Wow, I think that did it. What do those "t" and "d" stand for and what are those expressions called?
Thanks a bunch TG! |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 06/29/2012 : 20:46:03
|
The statements inside parentheses are derived tables. The d and t are table aliases for those derived tables.
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 06/29/2012 : 22:38:59
|
quote:
Originally posted by TG
You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL
if you need more help let us know which version you are using (pivot or any version).
Be One with the Optimizer
TG
need to add "under default conditions"
ANSI NULL settings can change behaviour
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5500 Posts |
Posted - 07/01/2012 : 12:27:57
|
quote:
Originally posted by visakh16
quote:
Originally posted by TG
You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL
if you need more help let us know which version you are using (pivot or any version).
Be One with the Optimizer
TG
need to add "under default conditions"
ANSI NULL settings can change behaviour
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
I intentionally did not want to encourage setting ansi_nulls off 
I probably should have said "shouldn't" instead of "can't".
Be One with the Optimizer
TG |
|
|
| |
Topic |
|
help with multiple tables using outer joins
