Need help with a query

[Glowworm27]

Hello Group,

I have a seamingly easy query to write but my heads foggy today.

I have 500 stores, each with a unique number
I can get the stores by doing a
'Select distinct Storenumber from Stores'

I need to create a record set that will display a Each storenumber and a date.

i.e.
Store, date
3000, '9/1/2004'
3001, '9/1/2004'

Now here is the hard part.

I need to list each store with a date, for the past 14 days.
So I would get something like this...

Store, Date
3000, '9/1/2004'
3000, '9/2/2004'
3000, '9/3/2004'
.
. -- all dates for the past 14 days would be here then
3001, '9/1/2004'
3001, '9/2/2004'
3001, '9/3/2004'
.
. -- This would continue till all the stores where listed once with each date for 14 days.

Any help would be appreciated...

thanks





George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[btgroup]

Something like this might work:

SELECT store, date FROM table
WHERE date > (getdate() - 14)
GROUP BY store, date
ORDER BY store

 

[Glowworm27]

Wont that just give me all the stores with the same date?

I need all the stores with all dates for a period of 14 days

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[btgroup]

No, you're grouping by store and then by date so it won't limit the grouping to only one of the same store. Give it a try.

 

[Glowworm27]

OK let me ask this

How can I get a recordset of Every date for the past 30 days

i.e.

9/1/2004
9/2/2004
9/3/2004
9/4/2004
etc....

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[btgroup]

It would be a very similar query:

SELECT date FROM table
WHERE date > (getdate() - 30)
GROUP BY date
ORDER BY date

You could also use distinct in that situation:

SELECT distinct(date)
FROM table
WHERE date > (getdate() - 30)

What exactly are you trying to do? This situation is different than your original post.

 

[SQLSister]

Glowworm27, I take it the dates are not stored in your table?

BTW, Since you said the store ids are unique you probably don't need Distinct in your select.

I see two ways of doing this.

One set up a loop or cursor to add the records with new dates cycling through the last 14 days.

Two set up a dates table with all the dates in teh year in it. Then cross join to this table and put in a where clasue which will filter for only the dates you want. Actually since cross joins create a lot of records I would do the wquery withthe where clasue first and then make it a derived table and cross join on that, now that I think of it.

Which is better depends on your own situation. For that number of records, you may not see a big improvement in efficency by eliminating the cursor and the date table clearly needs to be updated every year which is a pain. As much as I hate cursors, this may be one case where you wouldn't see much gain from using the method that eliminates the cursor. I'd prolly try it first and then only use the other method if it was really slow.




Questions about posting. See faq183-874