JessyRobinson1234
asked on
Help with NULL values and how to write in a subquery
select 'Linea 101' as 'Line',
Total_Qty as 'Total',
round((Progress_Qty * 100)/Total_Qty,0) as 'progress'
from Lenovo_Events
Join Lenovo_Lines on Lenovo_Events.Line_Key = Lenovo_Lines.Line_Key
where CONVERT(VARCHAR(8),Event_Date, 1) = CONVERT(VARCHAR(8), GETDATE(), 1)
and shift_key = 4
and Lenovo_Events.line_key =1
union all
select 'Linea 102' as 'Line',
Total_Qty as 'Total',
round((Progress_Qty * 100)/Total_Qty,0) as 'progress'
from Lenovo_Events
Join Lenovo_Lines on Lenovo_Events.Line_Key = Lenovo_Lines.Line_Key
where CONVERT(VARCHAR(8),Event_Date, 1) = CONVERT(VARCHAR(8), GETDATE(), 1)
and shift_key = 4
and Lenovo_Events.line_key =2
union all
select Lenovo_Lines.Line as 'Line',
Total_Qty as 'Total',
round((Progress_Qty * 100)/Total_Qty,0) as 'progress'
from Lenovo_Events
Join Lenovo_Lines on Lenovo_Events.Line_Key = Lenovo_Lines.Line_Key
where CONVERT(VARCHAR(8),Event_Date, 1) = CONVERT(VARCHAR(8), GETDATE(), 1)
and shift_key = 4
and Lenovo_Events.line_key =3
union all
select Lenovo_Lines.Line as 'Line',
Total_Qty as 'Total',
round((Progress_Qty * 100)/Total_Qty,0) as 'progress'
from Lenovo_Events
Join Lenovo_Lines on Lenovo_Events.Line_Key = Lenovo_Lines.Line_Key
where CONVERT(VARCHAR(8),Event_Date, 1) = CONVERT(VARCHAR(8), GETDATE(), 1)
and shift_key = 4
and Lenovo_Events.line_key =4
This returns:
Linea 101 22 50
Linea 102 23 39
Linea 103 67 2
As you can see the Last select did not write a row because there are no values for progress_Qty and Total_Qty.I do, however want to still write this row as:
Linea 104 0 0
Secondly, how can I rewirte the query as a subquery where the and shift_key = 4
only needs to be written once? I want to pass this a query parametere in my application. Thanks!
of course, if you don't want NULL but 0:
ISNULL ( <expression>, 0 )
to change that.
ISNULL ( <expression>, 0 )
to change that.
ASKER
HI Angelll,
First of all thank you for your quick response. Based on what's currently stored in Lenovo_Events ,I believe I am approaching it the wrong way. The third way indicates the Line_Key (6,1,2,5,3) so there is no 4 key record. however, i still want that line listed with 0 values. How can I do that?
16 1/24/2013 12:00:00 AM 6 4 1 1 False
17 1/24/2013 12:00:00 AM 1 4 22 11 False
18 1/24/2013 12:00:00 AM 2 4 23 9 False
19 1/24/2013 12:00:00 AM 5 4 55 8 False
20 1/24/2013 12:00:00 AM 3 4 67 2 False
First of all thank you for your quick response. Based on what's currently stored in Lenovo_Events ,I believe I am approaching it the wrong way. The third way indicates the Line_Key (6,1,2,5,3) so there is no 4 key record. however, i still want that line listed with 0 values. How can I do that?
16 1/24/2013 12:00:00 AM 6 4 1 1 False
17 1/24/2013 12:00:00 AM 1 4 22 11 False
18 1/24/2013 12:00:00 AM 2 4 23 9 False
19 1/24/2013 12:00:00 AM 5 4 55 8 False
20 1/24/2013 12:00:00 AM 3 4 67 2 False
do you have soem tabke which IS listing all the possible key values?
you may create such a table, if not yet presend, and left join from that table.
now, what is the table "lines" containaing" ?
why do you join that table?
your sample data is "incomplete", as it doesn't tell us the column names...
you may create such a table, if not yet presend, and left join from that table.
now, what is the table "lines" containaing" ?
why do you join that table?
your sample data is "incomplete", as it doesn't tell us the column names...
ASKER
that makes it as simple as this:
select l.line_key as [Line],
isnull(e.Total_Qty, 0) as 'Total',
isnull ( round(( e.Progress_Qty * 100)/e.Total_Qty,0)), 0) as [progress]
from Lenovo_Lines l
LEFT Join Lenovo_Events e
ON e.Line_Key = l.Line_Key
AND e.Event_Date >= CONVERT(datetime, CONVERT(VARCHAR(10), GETDATE(), 120) , 120)
AND e.Event_Date < CONVERT(datetime, CONVERT(VARCHAR(10), GETDATE() + 1, 120) , 120)
date conversion/syntax explained here:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
aliases usage explained here:
https://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
aliases usage explained here:
https://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
ASKER
Thank you again, I get this error when I execute your code:
The isnull function requires 2 argument(s).
The isnull function requires 2 argument(s).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That does the trick, THANK YOU VERY MUCH!
* the LEFT JOIN to make sure you get rows ...
* the simplifying of the query
Open in new window