Create View with parameter - 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

Transact-SQL (2000)

Create View with parameter

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 11:07:12

I want to make view which will pull data from Sale table for last 30 days.(On SQL server 7.0)

Select Item, Sum(Qty) from Sales where ShipDate Between Date - 30 and Date Group by Item;

This my does not work ... does anybody have idea how I need to make these date parameters;

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 11:13:16

will this do?

Select Item, Sum(Qty)
from Sales
where datediff(d, Date , getdate()) <= 30
Group by Item

Go with the flow & have fun! Else fight the flow

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-29 : 11:13:34

[code]

SELECT
s.Item,
SUM(s.Qty)
FROM
Sales AS s
WHERE
s.ShipDate BETWEEN DATEADD(day, -30, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY
s.Item

[/code]

Mark

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 11:27:23

Aaa ..getdate() ... I could not remember that ...

I tried this and it's working if I do not use grouping ...

Select Item, Sum(Qty) from Sales where ShipDate Beteween (GETDATE()- 30) And GETDATE() Group by Item;

But ... why is to slow if I group data (I even do not get resault ... I have server Time out)

... I need this summary of quantity ...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 11:29:49

use datediff. it's faster.

Go with the flow & have fun! Else fight the flow

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 11:37:19

quote:
Originally posted by spirit1

use datediff. it's faster.

Go with the flow & have fun! Else fight the flow

I tried ... it's same ... If I have grouping does not work...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 11:56:36

how does it not work??? post sample data, create table statement and desired results

Go with the flow & have fun! Else fight the flow

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 12:48:52

quote:
Originally posted by spirit1

how does it not work??? post sample data, create table statement and desired results

Go with the flow & have fun! Else fight the flow

I mean, when I open veiw designer and create view with my query ... If I do not have grouping in guery, when I run view(query) Grid on the bottom show immediatelly resault ... if I choose grouping and run view (query), it's look like that trying to pull data into grid but message 'Server ODBC Time Out expired' show up .....it's look like that go much slower than without grouping ...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 13:02:21

run this in Query Analyzer and tell us how that works


create view YourView
as
Select Item, Sum(Qty) 
from Sales 
where datediff(d, ShipDate  , getdate()) <= 30
Group by Item
go
select * from YourView
go
DROP VIEW YourView

Go with the flow & have fun! Else fight the flow

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 13:30:48

I did exactly how you said ... here I got resault ...but still slow ....I mesured ...after 50 seconds I have resault ...

What do you think about views ...are they good for something as this ...

Actually my table si huge .... there is total of 1,622,996 records ... with this view from Analyzer I got total of 8260 records ...

mybe this works good for small tables ...ha, what do you think ???

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 15:10:17

is there an index on your ShipDate column? You definitely need one with a table this size.

W/o grouping it appears to run fast because it can start right away, but in actuality it will take forever to finish.

with grouping, it must process all rows before it can start to return results, so it will appear to slow down when you add it in. there should also be an index on Item I would think as well.

Also, with a table this size, once you get the index on the ShipDate column, make sure you don't do this:

WHERE DATEDIFF(...) < 30

because even with an index that will be slow, since it must perform a scan to evaluate the DATEDIFF() expression over and over ... use the other method, like this:

WHERE ShipDate BETWEEN DateAdd(dd,-30, GetDate()) and GetDate()

or something similiar to that. however you do it, make sure you have proper indexes !!!!

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 15:20:52

You can maybe also consider partitioning your table.

ie one table for each month.
This way the tables will be smaller and queries against them will be a whole lot quicker.

This is not always a good solution I know but it does work in some cases.

Duane.

ilimax
Posting Yak Master

164 Posts

Posted - 2004-09-29 : 15:51:04

Thanks ....

bm1000
Starting Member

37 Posts

Posted - 2004-09-29 : 15:59:27

quote:
Originally posted by ilimax

Thanks ....

Select Item, Sum(Qty)
from Sales
where shipdate >= dateadd(d,-30, getdate())
Group by Item

This predicate is sargable. It doesn't have to manipulate every shipdate value before doing the comparison. Therefore, it is more likely to use an index.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 16:10:32

ilimax -- so did you add an index? Did either column (Item or ShipDate) have an index on it?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 16:11:28

quote:
Originally posted by bm1000

quote:
Originally posted by ilimax

Thanks ....

Select Item, Sum(Qty)
from Sales
where shipdate >= dateadd(d,-30, getdate())
Group by Item

This predicate is sargable. It doesn't have to manipulate every shipdate value before doing the comparison. Therefore, it is more likely to use an index.

Wow, bm, that's a great idea ! Why didn't I think of that !?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources