Solved

Help with NULL values and how to write in a subquery

Posted on 2013-01-24
10
345 Views
Last Modified: 2013-01-24
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

Open in new window


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!
0
Comment
Question by:JessyRobinson1234
  • 6
  • 4
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38813904
as we don't know which fields/rows are in which table, I will do a "educated guess", and suggest this code, which should do for both parts of the question:
* the LEFT JOIN to make sure you get rows  ...
* the simplifying of the query
select  'Line ' + cast(Lenovo_Events.line_key  as varchar(10))  as [Line],
max (Total_Qty) as 'Total',
max( round((Progress_Qty * 100)/Total_Qty,0)) as [progress]
from Lenovo_Events
LEFT 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
group by Lenovo_Events.line_key

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38813906
of course, if you don't want NULL but 0:

ISNULL ( <expression>, 0 )

to change that.
0
 

Author Comment

by:JessyRobinson1234
ID: 38813979
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38813986
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...
0
 

Author Comment

by:JessyRobinson1234
ID: 38814014
I have a table called Lenovo_Lines that has all 8 Lines

Lenovo_Lines table
My Lenovo_Events looks like this:

Lenovo_Events table
As you can see on these images I have nothing displayed in Lenovo_Events on today's date for Line_Key 4.

I hope this clarifies it a  bit.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38814098
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)

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38814108
0
 

Author Comment

by:JessyRobinson1234
ID: 38814126
Thank you again, I get this error when I execute your code:

The isnull function requires 2 argument(s).
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38814136
sorry, a ")" too much
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)

Open in new window

0
 

Author Closing Comment

by:JessyRobinson1234
ID: 38814147
That does the trick, THANK YOU VERY MUCH!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now