Solved

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

Posted on 2008-06-18
14
155 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, again
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
is he in the data table?
0
 
LVL 1

Author Comment

by:drocco21
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using a SELECT statement inside of a CASE 5 14
VB.Net - For Loop Error 5 23
Round up to 100% in .NET 10 41
SQL Date Retrival 7 25
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

772 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

14 Experts available now in Live!

Get 1:1 Help Now