Solved

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

Posted on 2008-06-18
14
157 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
  • 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 500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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