Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Setting a view for tables in SQL, using if then for sql views

Posted on 2008-06-18
14
Medium Priority
?
164 Views
Last Modified: 2013-11-26
I am stumped, I have a SQL views that needs created. I have 3 tables: data, defaults, updates.

So the data table will be updated constantly throughout the day. The defaults table has default values, so lets say the name field in the data table is "Tom". The location associated with "Tom" would be "room 1".

Now the updates table has a start time and end time. If the time in the data table is between the start time and end time of the updates table, then the location should be that of the updates table instead of the defaults table for that time frame. I have banged my head over this and know there is an answer, but I cannot seem to find what I need. Any help would be awesome and thanks in advance.

I will attach a visual so this makes more sense that has all of the fileds.
tbl-layout.xls
0
Comment
Question by:drocco21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21814976
Can you post some examples of the data you have in the time field in your data table?  What data type is it?  Also, are the starttime and endtime fields datetime data types?
0
 
LVL 1

Author Comment

by:drocco21
ID: 21815146
All of the time fields are date time. Also, the judge is the common field that will be focused on, not the ID field. Attached is some example data
tbl-layout.xls
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21815165
see if this helps:

select  location = case when d.time between u.starttime and u.endtime then u.location else d.location end, *
from    data d
        join defaults dd on d.judge = dd.judge
        join updates u on d.judge = u.judge
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:drocco21
ID: 21815470
I am amazed and stunned by your knowledge 8-)
Seriously I have no idea what just happened but thank you so much, it works perfectly. I am accepting that as my answer, but by any chance could you explain kind of what is happening in the statement? I am so confused where the data actually pulls from the tables that I showed you but I know that it is. Not sure like how it knows that u.location is from the updated table.

Wow, thanks so much
0
 
LVL 1

Author Closing Comment

by:drocco21
ID: 31468480
Thanks, again
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815517
Sure....I gave all of the table aliases.  u is an alias for the updates table.  In this statement:

case when d.time between u.starttime and u.endtime then u.location else d.location end

I check to see if the time field from the data table (d alias) occurs between the value from the fields starttime and endtime from the updates table.  If it does, then I show the value from the location field from the updates table.  Otherwise, I return the value from the location field in the data table.
0
 
LVL 1

Author Comment

by:drocco21
ID: 21815642
yah still stumped lol. So in this statement I could change all of the u's to updates, d to data and dd to defaults, and it would still work correct? If not and if I am way off that is fine, I dont want to take up all of your time and really appreciate your help with this
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815741
You can change them, sure.  You can change them to anything you want.

select  location = case when data.time between updates.starttime and updates.endtime then updates.location else data.location end, *
from    data
        join defaults on data.judge= defaults.judge
        join updates  on data.judge = updates.judge

OR

select  location = case when hippopotamus.time between baboon.starttime and baboon.endtime then baboon.location else hippopotamus.location end, *
from    data hippopotamus
        join defaults zebra on hippopotamus.judge= zebra.judge
        join updates baboon on hippopotamus.judge = updates.judge
0
 
LVL 1

Author Comment

by:drocco21
ID: 21815766
Haha, that is hilarious lol
So since you are pulling the .starttime and .endtime, it knows that its the updates table then
0
 
LVL 1

Author Comment

by:drocco21
ID: 21816115
I know I marked this as answered, but just realized a slight issue. The updates table will be null most of the time, and I noticed that if the judge is not listed in the updates table, it is not displayed. Any idea of how to get the dat to display if an update is not yet applied? I am thinking maybe if updates is not null or something
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21816140
try this:

select  location = case when d.time between u.starttime and u.endtime and u.judge is not null then u.location else d.location end, *
from    data d
        join defaults dd on d.judge = dd.judge
        left join updates u on d.judge = u.judge
0
 
LVL 1

Author Comment

by:drocco21
ID: 21816236
hmmm, still not displaying
If you look at the excel file I sent, there is a judge named smith3, and he is also in the defaults table but not in the updates table. He is still not showing up on the main query.

Thanks,
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21816251
is he in the data table?
0
 
LVL 1

Author Comment

by:drocco21
ID: 21816295
Oh crap, you know what? The problem is me not you 8-(
The d table actually should not have the location and location should only be in the dd or updates table. Im a jack, im sorry. I changed the code to "else dd.location" instead of d.location. Below is the code that is working, thank you so much, and I am done bothering now I promise 8-)
SELECT     CASE WHEN d .time BETWEEN u.starttime AND u.endtime AND u.judge IS NOT NULL THEN u.location ELSE dd.location END AS location, d.ID, d.judge, 
                      d.time, d.location AS Expr1, d.name, dd.ID AS Expr2, dd.judge AS Expr3, dd.location AS Expr4, u.ID AS Expr5, u.judge AS Expr6, u.location AS Expr7, 
                      u.starttime, u.endtime
FROM         dbo.data AS d INNER JOIN
                      dbo.defaults AS dd ON d.judge = dd.judge LEFT OUTER JOIN
                      dbo.updates AS u ON d.judge = u.judge

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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