Views ?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Views ??

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 04:46:46

I need to query data in a table on a nightly basis. The table that I am querying contains sensitive information. I was thinking of doing a select on the table to create a view and then querying the view. The data in the table is updated daily therefore would need to create the view on a nightly basis before querying.
How can I recreate the view nightly, or is this the correct way to do this?

macca

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 05:01:03

A view is not materialized, so why do you need to recreate the view nightly?

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 05:08:09

I mean I need to update the view nightly as the data in the table is updated daily.
Do I need a trigger or something to create the view or how does the view get updated with the updated table data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 05:36:18

If you update the view, you are updating the underlying table.
Please rephrase and tell us what you really are trying to accomplish. Don't speak in toungue...

Be clear and consice about your requirements.

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 05:56:18

I want to query the data in a table but I do not want to query the table directly. Can I create a View that contains a subset of the data in the table and just query that rather tahn querying the table directly ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 06:07:04

Yes. Absolutely.

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 06:12:16

The problem I have is that the data in the table will be updated daily therefore will the view be updated daily also or do I need to do something else to ensure the view gets updated daily?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-23 : 06:20:42

it depends how you write your view. if your write it so it looks at January 2008, it will always look at Januray 2008. But if you write it so it looks at the previous weeks data, it will always look at the previous weeks data - and will therefore roll-on as the data grows in the underlying table without having to change the view

make sense?

Em

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 06:42:21

ok Em,

I have the following:

CREATE VIEW dbo.VIEW_pa
AS
SELECT *
FROM server.dbo.table paapplic_1
WHERE (date >= DATEADD([day], DATEDIFF([day], 0, GETDATE()) - 6, 0))
ORDER BY date

So if I run a query against the above View I will be querying the data in the table as far back as the 17 April. But if I query the view tomorrom it will only be the data back to 18 April. Is this correct??

macca

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 07:09:07

Skip the ORDER BY in the view.

The code above will give you a rolling 7-day period.
If you want full previous week of data, try this

SELECT	*
FROM	Server.dbo.Table
WHERE	Date >= DATEADD(WEEK, DATEDIFF(WEEK, '19000108', GETDATE()), '19000101')
	AND Date < DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101')

This will give you last week (mon-sun) for all days in this week. Then, when the day of week turns monday again (in 5 days) the query will give this weeks data.

And drop the "SELECT *" thingy.
Choose your columns explicit and you will have no problem with column order changes, or additions to base table etc etc.

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 07:12:49

Can you answer this bit of the above Peso, this is where my problem is !!

So if I run a query against the above View I will be querying the data in the table as far back as the 17 April. But if I query the view tomorrom it will only be the data back to 18 April. Is this correct??

macca

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 07:20:35

Yes!
As I wrote, you are selecting "a rolling 7-day period".

If you ever are unsure of what the WHERE part is doing, copy and paste that single part and run it like this

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 6, 0)

Now you can see that the query returns 2008-04-17, which is what you are having trouble with.

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 07:34:38

Thats fine.
Thanks Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources