[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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

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
drocco21
Asked:
drocco21
  • 8
  • 6
1 Solution
 
chapmandewCommented:
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
 
drocco21Author Commented:
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
 
chapmandewCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
drocco21Author Commented:
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
 
drocco21Author Commented:
Thanks, again
0
 
chapmandewCommented:
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
 
drocco21Author Commented:
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
 
chapmandewCommented:
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
 
drocco21Author Commented:
Haha, that is hilarious lol
So since you are pulling the .starttime and .endtime, it knows that its the updates table then
0
 
drocco21Author Commented:
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
 
chapmandewCommented:
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
 
drocco21Author Commented:
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
 
chapmandewCommented:
is he in the data table?
0
 
drocco21Author Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now