Solved

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

Posted on 2008-06-18
14
156 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

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

21 Experts available now in Live!

Get 1:1 Help Now