Excel, views and groups 1

[bartsimpson]

I have a lot of data that I summarise in a view. This gives me data such as :

group     summary 1    summary 2
  1           57           26
  2           45           43

The data in the groups are sorted by date. I want to return this data to an Excel spreadsheet using MS Query, which can limit the data to specified date ranges. I can't just add a date field to put in the MS Query as a WHERE because it can't access data that isn't summary data. I need to limit the range of the raw data.

Can anyone think of a way that I can get Excel to specify the date range ?

 

[JohnDTampaBay]

Put the SELECT statement from the view into a stored procedure. Include parameters for the date range. Add a WHERE clause for the date range to the SELECT. Finally, pass the date range to the stored procedure from MS Query, which should return the summarized data for the date range passed. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[bartsimpson]

John,

Thanks for this. I don't know a whole lot about stored procedures at present, but it looks like I'm about to learn !

Kind Regards,

 

[JohnDTampaBay]

A stored procedure is nothing more than one or more SQL statements that are preparsed and stored on the server.

If your existing view looks like this...

SELECT [Group]
       , Count(*) AS [summary 1]
FROM Table
GROUP BY [Group]

...your stored procedure may look something like this...

CREATE PROC uspGetSummary
    @FromDate datetime
  , @ToDate datetime
AS
  SELECT [Group]
       , Count(*) AS [summary 1]
  FROM Table
  WHERE TheDate BETWEEN @FromDate AND @ToDate
  GROUP BY [Group]

...and you execute it like so...

EXEC uspGetSummary '1/1/2004', '3/31/2004'

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[bartsimpson]

John,

This looks easy enough. I'm tied up for a couple of days now, but I'll implement as soon as I have a little time.

Thanks for the clear explanation.

 

[ESquared]

As always, be careful with the between operator and dates. It's more complicated than you think. JohnDTampaBay was just giving an example, but you should know it would only work properly if all dates in the table being selected from had no times other than 12:00:00.000 am.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[bartsimpson]

I don't really understand what you mean, ESquared. When using views, if I select a day in the way JohnDTampaBay has suggested, it would run from 00:00:00 on the first day selected, to 00:00:00 on the final day - effectively missing out the last day.

This would give me 1st Jan 00:00:00 to 31st March 00:00:00 in John's example.

I'm also having trouble making the stored procedure visible. I can't see it at all in the ODBC connector. Is there any special permissions that I need to grant ? I have granted that SP execute rights for me.

 

[ESquared]

bartsimpson,

You understood exactly what I meant. If the dates in your table have no time component (all 12am) then using between would be fine. But if your dates have times attached, you would be better off using syntax like

WHERE Dt >= 'Date1' AND Dt < 'Date2+1'

Date1 and Date2 being the inclusive dates you wish to select between.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)