Data between 2 dates issue

I am very new to using SQL, so please be patient. I am using MS Access for
the front end and SQL for the backend.
I'm creating a view (using SQL Server Enterprise Manager) that I will link
to Access, but the view) keeps timing out. If I run the same view using
WinSQL, it works.
I am trying to lookup data between 2 dates in my main table. The parameter
dates are kept in a separate table having no fields I can join with my main
table. If I manually type the dates in, it works.
Here's my statement:
SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
Total
FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
dbo.oss_tran ON
dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
JOIN
dbo.oss_user ON dbo.oss_tran.ACCTNO =
dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
dbo.oss_proj ON dbo.oss_tran.PROJECTNO =
dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
WHERE (dbo.oss_tran.[DATE] BETWEEN
(SELECT BeginDate
FROM dbo.oss_vhb_Data_Input) AND
(SELECT EndDate
FROM dbo.oss_vhb_Data_Input)) AND
(dbo.oss_tran.read_period IS NULL)
GROUP BY RIGHT office
I hope that's not too confusing. I really need help with this.
Thanks,
Rachel
Hi Rachel
"RFrechette" wrote:

> I am very new to using SQL, so please be patient. I am using MS Access for
> the front end and SQL for the backend.
> I'm creating a view (using SQL Server Enterprise Manager) that I will link
> to Access, but the view) keeps timing out. If I run the same view using
> WinSQL, it works.
> I am trying to lookup data between 2 dates in my main table. The parameter
> dates are kept in a separate table having no fields I can join with my main
> table. If I manually type the dates in, it works.
I assume you mean there is a single row in the table?

> Here's my statement:
> SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> Total
> FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> dbo.oss_tran ON
> dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> JOIN
> dbo.oss_user ON dbo.oss_tran.ACCTNO =
> dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> dbo.oss_proj ON dbo.oss_tran.PROJECTNO =
> dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> WHERE (dbo.oss_tran.[DATE] BETWEEN
> (SELECT BeginDate
> FROM dbo.oss_vhb_Data_Input) AND
> (SELECT EndDate
> FROM dbo.oss_vhb_Data_Input)) AND
> (dbo.oss_tran.read_period IS NULL)
> GROUP BY RIGHT office
>
Try (untested):
SELECT w.Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
IS NULL
JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
r.[DATE] <= EndDate
GROUP BY w.office

> I hope that's not too confusing. I really need help with this.
> Thanks,
> Rachel
>
I am not sure why you are not passing your date criteria as parameters to a
stored procedure. The user an proj tables may not be adding anything to this
query.
John
|||Hi John,
Thank you so much for helping me.
I took out the "oss_user" table because I think you are right that it is not
being used. Also, I took out the "AND r.read_period IS NULL" because I
realized that should not be there as well. I really need only the 2 last
digits of the field "general1" from table r. I tried using
"r.Right(general1,2) AS Office but it doesn't seem to like that.
This is what I ended up with:
SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO =
p.ACCTNO INNER JOIN
dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
AND r.[DATE] <= d.EndDate
GROUP BY r.general1
It still times out on me. And of course, if it did work, it wouldn't be
grouped correctly because I'm using the whole "general1" field instead of the
last 2 digits.
I noticed in the diagram section, that the d table is joined to the w table.
Shouldn't the d table be joined to the r table because the r table hold the
Date field and the d table holds the BeginDate and EndDate fields?
I'm sorry I'm not more knowledgeble on this but I've had no classes and only
have 1 book and I kind of got thrown into this.
Do you have any more suggestions?
Thank you, Rachel
"John Bell" wrote:

> Hi Rachel
> "RFrechette" wrote:
> I assume you mean there is a single row in the table?
> Try (untested):
> SELECT w.Office,
> SUM(t.CHARGE) AS Charge,
> SUM(t.TAXCHARGE) AS TaxCharge,
> SUM(t.CHARGE + t.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w
> JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> IS NULL
> JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> r.[DATE] <= EndDate
> GROUP BY w.office
>
> I am not sure why you are not passing your date criteria as parameters to a
> stored procedure. The user an proj tables may not be adding anything to this
> query.
> John
|||Hi Rachel
"RFrechette" wrote:
[vbcol=seagreen]
> Hi John,
> Thank you so much for helping me.
> I took out the "oss_user" table because I think you are right that it is not
> being used. Also, I took out the "AND r.read_period IS NULL" because I
> realized that should not be there as well. I really need only the 2 last
> digits of the field "general1" from table r. I tried using
> "r.Right(general1,2) AS Office but it doesn't seem to like that.
> This is what I ended up with:
> SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO =
> p.ACCTNO INNER JOIN
> dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> AND r.[DATE] <= d.EndDate
> GROUP BY r.general1
> It still times out on me. And of course, if it did work, it wouldn't be
> grouped correctly because I'm using the whole "general1" field instead of the
> last 2 digits.
> I noticed in the diagram section, that the d table is joined to the w table.
> Shouldn't the d table be joined to the r table because the r table hold the
> Date field and the d table holds the BeginDate and EndDate fields?
> I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> have 1 book and I kind of got thrown into this.
> Do you have any more suggestions?
> Thank you, Rachel
>
> "John Bell" wrote:
The join condidition is
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=
EndDate
Therefore I am not sure what your diagram is showing!
Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
statement
SELECT RIGHT(r.general1,2) AS Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
r.[DATE] <= EndDate
GROUP BY RIGHT(r.general1,2)
I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
want to look to see if that is contributing anything that is not necessary
(if office is not in this view why do you need it?)
John
|||Hi John,
I want to thank you for helping me.
The query is working perfectly when I use it in WinSQL, but it's still
timing out when I use it in SQL Server Enterprise Manager. So there must not
be anything wrong with the query itself. Maybe there is a way to extend the
time out in Enterprise Manager?
I'm about ready to give up. I guess I just need to find some SQL classes.
(Or classes in using SQL Server Enterprise Manager, if any exist.
I really do appreciate all your help though.
Rachel
"John Bell" wrote:

> Hi Rachel
> "RFrechette" wrote:
> The join condidition is
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=
> EndDate
> Therefore I am not sure what your diagram is showing!
> Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> statement
> SELECT RIGHT(r.general1,2) AS Office,
> SUM(t.CHARGE) AS Charge,
> SUM(t.TAXCHARGE) AS TaxCharge,
> SUM(t.CHARGE + t.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w
> JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> r.[DATE] <= EndDate
> GROUP BY RIGHT(r.general1,2)
> I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> want to look to see if that is contributing anything that is not necessary
> (if office is not in this view why do you need it?)
> John
>
|||Hi Rachel
You don't say what time it takes in WinSQL (which I am note sure it is!)
Try using Query Analyser!!
In Enterprise Manager if you click on the server, then choose Tools/Options
there is a query timeout value on the advanced tab, this should be 0 for no
timeout. In Enterprise Manager how are you running this?
John
"RFrechette" wrote:
[vbcol=seagreen]
> Hi John,
> I want to thank you for helping me.
> The query is working perfectly when I use it in WinSQL, but it's still
> timing out when I use it in SQL Server Enterprise Manager. So there must not
> be anything wrong with the query itself. Maybe there is a way to extend the
> time out in Enterprise Manager?
> I'm about ready to give up. I guess I just need to find some SQL classes.
> (Or classes in using SQL Server Enterprise Manager, if any exist.
> I really do appreciate all your help though.
> Rachel
> "John Bell" wrote:
|||Hi John,
I checked the query time out value in Enterprise, it's 0. Here's what I'm
getting using the query you gave me:
WinSQL:
Works in about 3 minutes with correct results.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
SQL Server Enterprise:
After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
Driver] Timeout expired.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
Query Analyser:
Works in about 3 minutes with correct results.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
Does that information help at all?
Thank you.
Rachel
"John Bell" wrote:
[vbcol=seagreen]
> Hi Rachel
> You don't say what time it takes in WinSQL (which I am note sure it is!)
> Try using Query Analyser!!
> In Enterprise Manager if you click on the server, then choose Tools/Options
> there is a query timeout value on the advanced tab, this should be 0 for no
> timeout. In Enterprise Manager how are you running this?
> John
> "RFrechette" wrote:
|||Hi Rachel
"RFrechette" wrote:
[vbcol=seagreen]
> Hi John,
> I checked the query time out value in Enterprise, it's 0. Here's what I'm
> getting using the query you gave me:
> WinSQL:
> Works in about 3 minutes with correct results.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> SQL Server Enterprise:
> After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
> Driver] Timeout expired.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> Query Analyser:
> Works in about 3 minutes with correct results.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> Does that information help at all?
> Thank you.
> Rachel
> "John Bell" wrote:
I assume that the date table is necessary because of the way your
applications works?
In query analyser look at the query plan (to this on by checking under the
show execution paln on the query menu), you will hopefully be able to see
what is inefficient when you run this query. If sounds like there are missing
indexes, so using the index tuning wizard may give you some clue as to what
is missing.
John
|||Hi John,
Yes, the Date table is necessary. I hope I haven't included too much data
for you here...
I looked at the query plan, unfortunately it didn't make much sense to me.
When I go the "Manage Indexes" for oss_tran it shows:
Index: pkey, Clustered: No, Columns: Acctno, Date, OrderNo
Index: pkey2, Clustered: No, Columns: Date
Index: pkey3, Clustered: No, Columns: OrderNo
Index: pkey4, Clustered: No, Columns: Acctno, ProjectNo
I used the Index Tuning Wizard. On the Index Recommendations page it showed:
Clustered: Yes, Index: oss_tran1, Table: dbo.oss_tran, Column: AcctNo
Clustered: No, Index: pkey4, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
Clustered: No, Index: pkey, Table: dbo.oss_tran, Column: AcctNo, Date, OrderNo
Clustered: No, Index: pkey2, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
Clustered: No, Index: pkey3, Table: dbo.oss_tran, Column: OrderNo
(It showed that if I implemented the recommended changes, I should see a 79%
change in performance.) Unfortunately, I can not do this without permission,
which will be hard to get.
Under the Analysis on the Index Recommendations page, it showed for the
Index Usage Report (Recommended Configuration):
Table: dbo.oss_tran, Index: [oss_tran1], % usage: 100, Size: 19011696
Table: dbo.oss_tran, Index: [pkey4], % usage: 0, Size: 1,390,096
Table: dbo.oss_tran, Index: [pkey1], % usage: 0, Size: 1,553,640
Table: dbo.oss_tran, Index: [pkey2], % usage: 0, Size: 695,048
Table: dbo.oss_tran, Index: [pkey3], % usage: 0, Size: 858,592
Do you think that the problem I’m having getting the query to run in SQL
Enterprise would be solved if the recommendations were implemented?
Also, when I look at the properties of the oss_tran table it shows the Full
Text Indexes as inactive and none of the columns are checked off for Full
Text Indexes.
Could this also be the problem?
I truly appreciate your help on this. I feel like an idiot.
Thank you, Rachel
"John Bell" wrote:

> Hi Rachel
> "RFrechette" wrote:
> I assume that the date table is necessary because of the way your
> applications works?
> In query analyser look at the query plan (to this on by checking under the
> show execution paln on the query menu), you will hopefully be able to see
> what is inefficient when you run this query. If sounds like there are missing
> indexes, so using the index tuning wizard may give you some clue as to what
> is missing.
> John
|||Hi Rachel
"RFrechette" wrote:
[vbcol=seagreen]
> Hi John,
> Yes, the Date table is necessary. I hope I haven't included too much data
> for you here...
> I looked at the query plan, unfortunately it didn't make much sense to me.
> When I go the "Manage Indexes" for oss_tran it shows:
> Index: pkey, Clustered: No, Columns: Acctno, Date, OrderNo
> Index: pkey2, Clustered: No, Columns: Date
> Index: pkey3, Clustered: No, Columns: OrderNo
> Index: pkey4, Clustered: No, Columns: Acctno, ProjectNo
> I used the Index Tuning Wizard. On the Index Recommendations page it showed:
> Clustered: Yes, Index: oss_tran1, Table: dbo.oss_tran, Column: AcctNo
> Clustered: No, Index: pkey4, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> Clustered: No, Index: pkey, Table: dbo.oss_tran, Column: AcctNo, Date, OrderNo
> Clustered: No, Index: pkey2, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> Clustered: No, Index: pkey3, Table: dbo.oss_tran, Column: OrderNo
> (It showed that if I implemented the recommended changes, I should see a 79%
> change in performance.) Unfortunately, I can not do this without permission,
> which will be hard to get.
> Under the Analysis on the Index Recommendations page, it showed for the
> Index Usage Report (Recommended Configuration):
> Table: dbo.oss_tran, Index: [oss_tran1], % usage: 100, Size: 19011696
> Table: dbo.oss_tran, Index: [pkey4], % usage: 0, Size: 1,390,096
> Table: dbo.oss_tran, Index: [pkey1], % usage: 0, Size: 1,553,640
> Table: dbo.oss_tran, Index: [pkey2], % usage: 0, Size: 695,048
> Table: dbo.oss_tran, Index: [pkey3], % usage: 0, Size: 858,592
> Do you think that the problem I’m having getting the query to run in SQL
> Enterprise would be solved if the recommendations were implemented?
> Also, when I look at the properties of the oss_tran table it shows the Full
> Text Indexes as inactive and none of the columns are checked off for Full
> Text Indexes.
> Could this also be the problem?
> I truly appreciate your help on this. I feel like an idiot.
> Thank you, Rachel
>
> "John Bell" wrote:
The index tuning wizard is effectively saying that the indexes on the single
columns are not useful for this query, and that covering indexes would be
alot better. It does not mean that they are not useful to other queries that
are executed on your systems and a more thorough indexing excercise would
take a larger sample of queries and produces the best indexing strategy for
the overall system.
You may want to try using SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT to get
information about the expected query plan i.e.
SET SHOWPLAN_ALL ON before your query and SET SHOWPLAN_ALL OFF after it. You
may then be able to post the plans.
Can you use a stored procedure to encapsulate this query?
e.g.
CREATE PROCEDURE spr_execmyquery
AS
BEGIN
DECLARE @.datestart datetime, @.dateend datetime
SELECT @.datestart = BeginDate,
@.dateend = EndDate
FROM dbo.oss_vhb_Data_Input
SELECTRIGHT(w.general1,2) AS Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND t.[DATE] >=
@.datestart AND t.[DATE] <= @.dateend
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
GROUP BY RIGHT(w.general1,2)
END
I am a bit confudled regarding which tables currently being used and what
their aliases are!!
John