Latest quarterly default

In Query Analyzer, I would like to design a view that would that would return
data automatically for the most recent completed quarter based on a calendar
year.
For example(from the SOP30200 table in Great Plains Dynamics 7.0):
sopnumbe soptype docdate subtotal
-- -- -- --
I would like all data from July 1 through September 30, 2006 since today is
December 27 and this current calendar quarter is still in progress.
Thank you.
If you are still interested this should work (but it's a bit convoluted).
You can replace the @.Today WITH GETDATE() is you want to use the machine date.
DECLARE @.Today DATETIME
SELECT @.Today = '12/27/2006'
SELECT
@.Today AS TodaysDate,
DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 1
ELSE 0 END AS [Year],
CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 4 ELSE DATEPART(quarter,
@.Today) - 1 END AS [Quarter],
CAST(
CASE
WHEN DATEPART(quarter, @.Today) = 1 THEN '12/31/'
WHEN DATEPART(quarter, @.Today) = 2 THEN '03/31/'
WHEN DATEPART(quarter, @.Today) = 3 THEN '06/30/'
ELSE '09/30/'
END +
CAST(DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN
1 ELSE 0 END AS VARCHAR)
AS DATETIME) AS LastQuarterEnd
Results:
2006-12-27 00:00:00.000200632006-09-30 00:00:00.000
Regards,
JayAchTee
"chas2006" wrote:

> In Query Analyzer, I would like to design a view that would that would return
> data automatically for the most recent completed quarter based on a calendar
> year.
> For example(from the SOP30200 table in Great Plains Dynamics 7.0):
> sopnumbe soptype docdate subtotal
> -- -- -- --
> I would like all data from July 1 through September 30, 2006 since today is
> December 27 and this current calendar quarter is still in progress.
> Thank you.

latest enabled version of every document (was "help with sql please!")

Hi, I'm having trouble with some SQL. I have a document table which links to a document version table. I'd like to write a query to get the latest enabled version of every document - a resultset with one row per document. Is this possible with resorting to a cursor?

CREATE TABLE tblDoc (
DocId int
)
GO

CREATE TABLE tblDocVersion (
DocVersionId int,
DocId int,
DateCreated datetime,
IsEnabled bit
)
GO

INSERT INTO tblDoc (DocId) VALUES (1)
INSERT INTO tblDoc (DocId) VALUES (2)

INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '9/9/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '10/10/2006' , 0)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '11/11/2006' , 1)

Desired output:

DocId DocVersionId DateCreated
============================
1 2 9/9/2006
2 5 11/11/2006

Thanks!
MrsMUse a subquery:

select tblDocVersion.DocId,
tblDoVersion.DocVersionID,
tblDocVersion.DateCreated
from tblDocVersion
inner join --LastVersions
(select DocId,
max(DateCreated) as DateCreated
from tblDocVersion
group by DocId) LastVersion
on tblDocVersion.DocId = LastVersion.DocID
and tblDocVersion.DateCreated = LastVersion.DateCreated

By the way...why do you prefix your table name with "tbl"? Are you afraid you are going to forget what it is? Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?|||ah nice, joining on a sub query - never thought of that, thanks a lot.

the tbl thing is part of the standards at my company. doesn't do anyone any harm!

thanks again|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!that's highly doubtful

not the "part of the standards at my company" part

the other part

:)|||Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?

hah! :)

i always hated hungarian. intellisense seems to be slowly killing it off in the world of compiled code thankfully. from the bible of umaintainable code (http://mindprod.com/jgloss/unmain.html):

a_crszkvc30LastNameCol:

"It took a team of maintenance engineers nearly 3 days to figure out that this whopper variable name described a const, reference, function argument that was holding information from a database column of type Varchar[30] named "LastName" which was part of the table's primary key"|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!Why don't you suggest a standard that all desktop PCs be painted schoolbus yellow, all laptops be candy-striped red, and all printers have blue polka-dots? Also harmless, and just as sensible.|||I knew one team at MS that had a convention of putting _sp at the end of every sproc.

Presumably this was because they knew there was some downside to putting sp_ at the front.

I'm sure it really helped them distingush procs from tables, which, btw, all had _tbl at the end. ;)

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever. I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
Maury
Hi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maury

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever. I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
MauryHi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maurysql

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever.
I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
MauryHi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maury

Last_day and First_Day of the month

Hi,

Can I use Last_day(table_date) and first day? I have the query below and it is not working. If not is there any suggestions? I error is pointing at the "First_day".

SQL> SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),' MM/DD/YYYY') END_DATE
3 FROM schedule s;
SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
*
ERROR at line 1:
ORA-00904: invalid column name

Thanks a lotThere is an Oracle function called LAST_DAY that returns last day of month for given date, but not FIRST_DAY. For first day of month use TRUNC(s.key_date,'MM')|||What about formatting it like this ('MM/DD/YYYY'), remeber the actual table date is in 8 bytes varchar2.

Thanks|||My first reponse to that is: "why?" Why would anyone store dates as VARCHAR2 and not as DATE? Does not compute...

Anyway, you must convert the VARCHAR2 to a DATE before applying any other functions that work with dates:

LAST_DAY( TO_DATE(s.key_date,'MM/DD/YYYY') )

TRUNC( TO_DATE(s.key_date,'MM/DD/YYYY'), 'MM' )

Last time synchronized

I use SQLCE2.0 and merge replication.

Is there a way to query SQLCE to find the time of the last synchronization without manually tracking the time myself.

Thank you.

The following query works with SQL Mobile 2005 and later:

SELECT LastSuccessfulSync FROM __sysMergeSubscriptions WHERE Publication = " + strPublicationName

- not sure it works with SQL CE 2.0, though.

Last Query Statement fired into the MSSQL Engine

Hi,

How can you get the Query Statement of the last executed SQL command.

I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).

Thanks for your help and pointers.

My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.

Warm Regards,
Ranjit S Hans.

---------------------
Everywhere is a walking distance if you have the time - Steven Wrightdbcc inputbuffer(@.spid)|||You can also use fn_get_sql, but it is a little more awkward.|||Hi Matt/ Jezemine,

Thanks so much for the help .. You have been wonderful all along.

I am looking forward to try out using fn_get_sql sometime soon.

Thanks once again !!

Warm Regards,
Ranjit S Hans
----------------------
Everywhere is a walking distance if you have the time - Steven Wright|||fn_get_sql has a number of benefits over dbcc inputbuffer actually. dbcc cuts off the query returned at 255 chars as I recall. Usually thats enough, unless you have apps out there making ad-hoc sql calls doing a 10-way join. :)

last is not a recognised function name

Hi all,
ive tried to run a query in a view and am getting this error message:
'last' is not a recognised function name
i was sure that last was used in sql, how can i get the same result if it wont accept 'last'?
greguse MAX

LAST is a microsoft access function|||oh yeah!! forgot the syntax changed-

cheers

greg

Last Function in SQL Server ?

Hi there,
--Access Query that Doesn't work in SQL
SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
FROM Employees
--Access Query that work with SQL if we have an EmployeeID
SELECT TOP 1 FirstName,LastName
FROM Employees
ORDER BY EmployeeID DESC
--SQL only if we don't have an EmployeeID
Declare @.FirstName varchar(20)
Declare @.LastName varchar(20)
SELECT @.FirstName = FirstName, @.LastName = LastName
from Employees
SELECT @.FirstName, @.LastName
--Test1 to make sure that we looking for last record
Select * FROM Employees
What is the equivalent Last access function in T-SQL
Thanks
Oded Dror
Email: odeddror@.cox.netA table in SQL has no inherent logical order so there is no "first" or
"last" row. How do you want to define which row should be the last?
I believe that if you don't specify ORDER BY then Access just picks the
last record based on insertion order. However, SQL Server doesn't
preserve the information about insertion order unless you explicitly
create a column or columns to record that information.
For example, if you have a modification_date in your table you could
take the latest date:
SELECT first_name, last_name
FROM Employees
WHERE modification_date =
(SELECT MAX(modification_date)
FROM Employees)
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||First or Last concept can be applied just to an ordered set.
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid desc
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname, lastname
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname desc, lastname desc
AMB
"Oded Dror" wrote:

> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
>|||The concept of "last" does not exist in SQL nor SQL Server as SQL thinks in
terms of sets. Last must derived from the data on which you are querying usi
ng
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order i
n
which it was entered. No such guarantee exists in most database products lik
e
SQL Server or Oracle.
Thomas
"Oded Dror" <odeddror@.cox.net> wrote in message
news:%23gtUM9sYFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>

LAST FUNCTION

I have a sql database that I am trying to make this query work. The
problem is that I can't get the right info out, right'
So, I exported two tables to an Access database, and this is my query
that works beautifully.. Question is, I don't want to keep exporting
this info everytime, I want to make this query in SQL and leave it there
or use it for SQL Reporting Services..
Note that the table names changes from Access to SQL, but I have made
all those mods already.. The problem is with the "LAST" function on line
5 from the Access Query. and this is the error I'm getting when
applying to SQL:
Server: Msg 195, Level 15, State 10, Line 1
'Last' is not a recognized function name.
ACCESS QUERY:
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
========================================
===========================
SQL Query::
SELECT prod9eps.portal.Users.last_name, rod9eps.portal.Users.first_name,
Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm) AS LastLogin,
prod9phy.dbo.UtilLogRecord.UserID FROM prod9phy INNER JOIN
prod9eps.portal.Users ON prod9phy.dbo.UtilLogRecord.UserID =
prod9eps.portal.Users.user_name
GROUP BY prod9eps.portal.Users.last_name,
prod9eps.portal.Users.first_name, prod9phy.dbo.UtilLogRecord.UserID
HAVING (((Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm))<Date()-90))
ORDER BY prod9eps.portal.Users.last_name, prod9eps.portal.Users.first_name;Please post DDL, sample data and required end results, otherwise we can only
guess.
http://www.aspfaq.com/etiquette.asp?id=5006
The Access version of your query doesn't look like a well-defined result.
The ORDER BY list is what should drive the result of the LAST function in
Access but in your case the ORDER BY columns are also grouped so the result
of the LAST function will be determined by the physical record order in the
table. Since you haven't specified what that order is it's not clear just
what result you want.
In SQL Server there is no direct equivalent of the Access FIRST and LAST
functions. A table in SQL is not logically ordered so there is no first or
last row - you have to specify the column/expression that determines which
rows you want.
Here's an example taken from the Pubs database giving the first and last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in which
they were entered into a table then you need to add a DATETIME column to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||David, thank you for your explanation, but there is a DATETIME field.
The "Results.EventStartDtTm" is a DATETIME
I can't use MAX because there is number, the same goes for MIN. 99% of
the data is text.. Except for the "Results.EventStartDtTm" that is
(01/01/00 00:00:00)
Again, my query works great in Access but not in SQL.
David Portas wrote:
> Please post DDL, sample data and required end results, otherwise we can on
ly
> guess.
> http://www.aspfaq.com/etiquette.asp?id=5006
> The Access version of your query doesn't look like a well-defined result.
> The ORDER BY list is what should drive the result of the LAST function in
> Access but in your case the ORDER BY columns are also grouped so the resul
t
> of the LAST function will be determined by the physical record order in th
e
> table. Since you haven't specified what that order is it's not clear just
> what result you want.
> In SQL Server there is no direct equivalent of the Access FIRST and LAST
> functions. A table in SQL is not logically ordered so there is no first or
> last row - you have to specify the column/expression that determines which
> rows you want.
> Here's an example taken from the Pubs database giving the first and last
> title based on the MIN and MAX values of Title_id.
> SELECT
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MIN(title_id)
> FROM Titles)) AS First_Title,
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MAX(title_id)
> FROM Titles)) AS Last_Title
> A common requirement is to extract a single row of a group based on some
> criteria. The query below retrieves values from the "Last" (maximum
> title_id) row for each Type in the Titles table.
> SELECT T1.type, T1.title_id, T1.title, T1.price
> FROM titles AS T1
> JOIN
> (SELECT MAX(title_id) AS title_id
> FROM titles
> GROUP BY type) AS T2
> ON T1.title_id = T2.title_id
> If you need to retrieve the first or last rows based on the order in which
> they were entered into a table then you need to add a DATETIME column to t
he
> table to record the entry date:
> date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
>|||> I can't use MAX because there is number
Please explain that statement. Sounds to me like MAX is exactly what you
want but if you won't give us a better description of your problem then how
can we help you? (see the link I posted previously).
David Portas
SQL Server MVP
--|||Ok, here we go..
I have the following file, of course, there is plenty more entries than
this, but just sample..
Tablename = UtilLogRecord
FieldNames::: Event,UserID,EventStartDtTm
Sign-In,aabraham,08/26/04 11:49:58
Sign-In,aabraham,08/26/04 12:13:28
Sign-In,aabraham,08/27/04 10:16:16
Sign-In,aabraham,08/27/04 10:17:53
Sign-In,aabraham,08/27/04 10:12:31
Sign-In,aabraham,08/27/04 11:33:34
Sign-In,aabraham,08/27/04 11:58:09
Sign-In,aabraham,08/27/04 11:59:24
Sign-In,aabraham,08/27/04 12:00:05
Sign-In,aabraham,08/27/04 13:11:58
Sign-In,aabraham,08/27/04 13:38:00
Sign-In,aabraham,08/27/04 13:39:11
Sign-In,aabraham,08/27/04 13:36:27
I also have the following:
Tablename = Users
FieldNames:: user_name,first_name,last_name,role
fherdoci,Filiberto,Herdocia,404
mromano,Mary,Romano,404
sparker,Susan,Parker,506
ltidwell,Liana,Tidwell,506
When I do:
============================
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
=========================
I get
Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
logged in to the system 08/27/04)
In MSSQL, I tried the MAX and it does not work at all. If this were
you, how would you write that statement..
Again, it works in Access and I can keep on doing it that way, but I
rather not.
./ray medina
David Portas wrote:
>
> Please explain that statement. Sounds to me like MAX is exactly what you
> want but if you won't give us a better description of your problem then ho
w
> can we help you? (see the link I posted previously).
>|||Ray,
Your code does not match your data,
and your answer does not give the last
time the user logged in. The most recent
login is
Sign-In,aabraham,08/27/04 13:39:11
Your result is wrong, if you want the
most recent login, but using MAX should
work.
The information you posted is not much help.
Your query refers to a table called [Results],
which you don't give the structure of, and it
joins UserID to user_name, which for this
sample data gives no match. You say your
query returns Sign-In,aabraham,08/27/04 13:36:27,
and you say this is the most recent login, but it
is not, and you don't give data that shows
how the query returns this.
It is also not helpful at all for you to say
"I tried the MAX and it does not work at all."
without explaining at all what you mean. When
you replace LAST with MAX, what doesn't work?
Wrong answers, no results, computer turns into
a piece of burned toast? Please give more information
in a form that is useful to someone trying to help.
If you learn to present your problems clearly and
thoroughly, you may soon discover that you have
fewer problems, too.
Steve Kass
Drew University
Ray Medina wrote:
> Ok, here we go..
> I have the following file, of course, there is plenty more entries
> than this, but just sample..
> Tablename = UtilLogRecord
> FieldNames::: Event,UserID,EventStartDtTm
> Sign-In,aabraham,08/26/04 11:49:58
> Sign-In,aabraham,08/26/04 12:13:28
> Sign-In,aabraham,08/27/04 10:16:16
> Sign-In,aabraham,08/27/04 10:17:53
> Sign-In,aabraham,08/27/04 10:12:31
> Sign-In,aabraham,08/27/04 11:33:34
> Sign-In,aabraham,08/27/04 11:58:09
> Sign-In,aabraham,08/27/04 11:59:24
> Sign-In,aabraham,08/27/04 12:00:05
> Sign-In,aabraham,08/27/04 13:11:58
> Sign-In,aabraham,08/27/04 13:38:00
> Sign-In,aabraham,08/27/04 13:39:11
> Sign-In,aabraham,08/27/04 13:36:27
> I also have the following:
> Tablename = Users
> FieldNames:: user_name,first_name,last_name,role
> fherdoci,Filiberto,Herdocia,404
> mromano,Mary,Romano,404
> sparker,Susan,Parker,506
> ltidwell,Liana,Tidwell,506
> When I do:
> ============================
> SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
> AS LastLogin, Results.UserID
> FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
> GROUP BY Users.last_name, Users.first_name, Results.UserID
> HAVING (((Last(Results.EventStartDtTm))<Date()-90))
> ORDER BY Users.last_name, Users.first_name;
> =========================
> I get
> Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
> logged in to the system 08/27/04)
> In MSSQL, I tried the MAX and it does not work at all. If this were
> you, how would you write that statement..
> Again, it works in Access and I can keep on doing it that way, but I
> rather not.
> ./ray medina
> David Portas wrote:
>

Last Date of previous Month

ive been trying very unsucessfully to try and get the last date of the
previous month,
ie if i run the query today i want the result to be 31/01/2006
declare @.d datetime
set @.d='20060201'
select DATEADD(day,-DATEPART(day,@.d),@.d)|||print dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:168DF5B1-BCE5-4601-802A-8FF10CAD3F2E@.microsoft.com...
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006
>|||Have you looked up how to use a Calendar table? The table could
include a code for last (business?) day of each month, so you can pull
out a set of such dates for reports. I
Your other options are computational. There is the "COBOL method" and
the "UNIX method". The Cobol guys think of dates as character strngs,
and they will build a date with concatendation and extraction
functions. The bad news is that they will probably use a local date
format instead of ISO-8601 style.
The UNIX method uses extraction functions and temporal math -- go to
the first of this month and subtract 1 day. Since Sybase/SQL Server
use a floating point number for date time, it can be pretty fast for
one-at-time processing.|||SELECT DATEADD(d,- 1,CAST(CONVERT(char(6),CURRENT_TIMESTAMP
,112) + '01'
as smalldatetime))
Here are a few other little tricks too:
http://sqlnerd.blogspot.com/2005/06...sql-tricks.html
*mike hodgson*
http://sqlnerd.blogspot.com
Peter Newman wrote:

>ive been trying very unsucessfully to try and get the last date of the
>previous month,
>ie if i run the query today i want the result to be 31/01/2006
>
>|||Why go to all the effort and extra storage of building a calendar table when
you can simply use one of SQL Servers built in functions...
dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138838149.462000.53340@.o13g2000cwo.googlegroups.com...
> Have you looked up how to use a Calendar table? The table could
> include a code for last (business?) day of each month, so you can pull
> out a set of such dates for reports. I
> Your other options are computational. There is the "COBOL method" and
> the "UNIX method". The Cobol guys think of dates as character strngs,
> and they will build a date with concatendation and extraction
> functions. The bad news is that they will probably use a local date
> format instead of ISO-8601 style.
> The UNIX method uses extraction functions and temporal math -- go to
> the first of this month and subtract 1 day. Since Sybase/SQL Server
> use a floating point number for date time, it can be pretty fast for
> one-at-time processing.
>|||>>Why go to all the effort and extra storage of building a calendar table wh
en you can simply use one of SQL Servers built in functions...<<
1) Portability. Temporal functions vary quite a bit from product. Did
you mean to avoid using the Standard CURRENT_TIMESTAMP with the
proprietary getdate()?
2) Standards conformance. Did you notice that you assumed the date
format is "dd-mm-yyyy" because you are British? An American would
assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
'yyyy-mm-dd" is the only format allowed in the ISO standards
3) Your code will have problems with the time part of a DATETIME. the
last day of a month should end at the last possible fraction of a
second to be sure to catch everything. The standard SQL version would
be:
CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
|| EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
|| '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.
Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
/ <<insert cowboy coding fad here>>. the current batch programmers
think only in terms of the immediate problem. The results are bad.
It is not just duplicated code that cannot be found to be refactored.
It is a lot of code that does the same task in many different ways.
Arrrgh!|||I'm with Joe on this one. Solve the problem with data rather than
code. Then you can get the last day of last month, the last business
day of last month, the first day of this month, the first business
day... and so on.
Best
Payson
Peter Newman wrote:
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006|||> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
Oh dear, not checked the product specs lately?
I use the format DD MMMMMMMM YYYY, example 1 February 2006; so there is NO
confusion at all. Also, I remove the time component entirely and reset back
to midnight on casting to smalldatetime
print '1 ' + datename( month, getdate() ) + ' ' + datename( year,
getdate() )
Even a beginner would **know** to look at the manual and not guess how
things work, how come you make such fundemental mistakes?

> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
That was not the posters requirement, they required a date without the time
component, which we don't have in SQL Server, so within SQL Server we use
00:00:00.000 for when no time component is required - again, even a beginner
would **know** that.
The syntax you give doesn't even work in SQL Server and as this is a
MICROSOFT SQL SERVER forum I would appreciate it if you at least point out
the differences when you post your unusable rubbish.

> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
A very big overhead for a simple problem, again, stop bloating up the design
to make more money. Keep things Simple Sweet and the design will be more
supportable and less likely to be buggy.

> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
Seriously, you need to get some real programming experience because you
don't seem to have any, either that or just stick to logical design and live
the programming to people who have been professionally trained and not
picked their knowledge up from reading books.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908162.308682.73220@.f14g2000cwb.googlegroups.com...
> 1) Portability. Temporal functions vary quite a bit from product. Did
> you mean to avoid using the Standard CURRENT_TIMESTAMP with the
> proprietary getdate()?
> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
>|||I frequently use calendar table for calculations like '5 business days
later' or
'now many business daya are there between 15-jan-2006 and 25-jan-2006'.
Ignoring any theories, with calendar table my queries sometimes perform
better.
I don't take that for granted and like to re-check that I'm using
efficient queries from time to time.
In this particular case, I took 500K rows from a production table. I
ran this query:
set statistics time on
go
select min(dateadd( day, -1, cast( '1 ' + datename( month, order_dt ) +
' ' +
datename( year, order_dt ) as smalldatetime ) )) mmin, max(dateadd(
day, -1, cast( '1 ' + datename( month, order_dt ) + ' ' +
datename( year, order_dt ) as smalldatetime ) ) ) mmx
from orders
...
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 17423 ms.
Then I built a calendar table:
create table month_calendar(num int, date_from datetime, date_to
datetime)
declare @.d1 datetime
declare @.d2 datetime
declare @.n int
set @.d1='19700101'
set @.d2='19700201'
set @.n=1
while @.d1 < '20060505'
begin
insert into month_calendar values(@.n, @.d1,@.d2)
set @.n=@.n+1
set @.d1 = @.d2
set @.d2 = dateadd(month, 1, @.d2)
end
go
select min(date_from), max(date_to) from month_calendar
select count(*) from month_calendar
create unique clustered index uci on month_calendar(date_from)
select * from month_calendar
then I ran another query:
select min(m.num), max(m.num)
--select count(*)
from month_calendar m join orders on date_from<=order_dt and
order_dt<date_to
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 1010 ms.
(both queries were rerun several times to account for warm/cold cache,
random fluctuations,
whatever else)
So for this particular scenario in this particular environment I'd
definitely go for a calendar table, if and only if the query is run
frequently.
I'm not saying calendar tables are the best choice in all the cases.
Any comments?

Last and Previous Query

I'm kindof at a lose for how to ask my database to show me the last payment amount and payment date for clients and also show me the previous payment amount and payment date for clients based on a date range.

Could anyone offer any pointers or examples?

Thank you very much.

We could use a very simple table and field setup like this just so you could show an example:

tblClient
Client_ID

tblOrder
Order_ID
Client_ID

tblOrderPayments
Payment_ID
Order_ID
Client_ID
PaymentDate
PaymentAmountLookup aggregates such as MAX and check out TOP.|||I looked them up but the only thing is that most examples are of there simplist use. So getting the max is easy but then getting the payment just prior to that is another thing all together. If anyone can offer any suggestions, I thank you,|||Well one way, and they'll be others, would be to get the latest set and store them in a table var. Then run the same query again except you exclude those you found in the first. Now you can do that all in 1 batch but the syntax can get a bit confusing. I like table vars, sure they're not optimal but they do make the SQL nice and easy. Once you get it working you can always optimise out the table var.

last 30 dates measures

Hi all,

I want to do a top / order by query like

select top 30 f.measure, d.date
from fact f
join date_dim d on f.date_key = f.date_key
group by d.date
order by d.date desc

you get the picture,

in MDX.

I know I can use now() but how do I get the dd/mm/yyyy format and use strtomember to put this on the rows axis?

also, I cannot get order() to order the dates axis (or any other dimension for that matter)

any ideas?

Thanks

Hi,

To convert the current date, you can use something like

StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]")

"[Period].[Calendar].[Day].&[" - represents the structure of your date dimension.

To get a 30 dates from the current date use something like -

WITHSET [30 Dates] AS {StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]"):StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]").lag(30)}

SELECT [Measures].[Value] on COLUMNS,

[30 Dates] on ROWS

FROM [CUBE]

Note, this query is only showing you, the measure you are interested in for the last 30 days.

Does this help?

When you want to order the dates, what exactly do you mean? Do you want to order them by a certain property or the value of your measures.

Thanks

Punita

LAST / TOP 1 / ...?

Hi,
I always thought there was a way to have the Last record of the group of
records that accorded to your query. But when I tried "LAST" this didn't
work really (althoug the Query analyzer recognized it as a valid
statement?).
I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
really be nice if there was something like a LAST and FIRST-function.
I have now:
SELECT TOP 1 c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
ORDER BY c21.ID DESC
But it would be nicer if this could be without the ORDER BY and some
LAST-function:
LAST(c21.ID), c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
Anybody knows anything about this subject? hehe :-)
Thanks a lot in advance,
PieterThat would imply that a table is ordered, which it isn't.
(Think about it, for each way to represent information, you need 4 set of
operators, read, add, remove and change. In SQL, we already have SELECT,
INSERT, DELETE and UPDATE. I don't think we want 4 more operators just to
add the concept of ordered tables.)
However, Yukon will probably have some language constructs which make paging
and stuff a bit easier to manage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eNryScgAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I always thought there was a way to have the Last record of the group of
> records that accorded to your query. But when I tried "LAST" this didn't
> work really (althoug the Query analyzer recognized it as a valid
> statement?).
> I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
> really be nice if there was something like a LAST and FIRST-function.
> I have now:
> SELECT TOP 1 c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
> ORDER BY c21.ID DESC
> But it would be nicer if this could be without the ORDER BY and some
> LAST-function:
> LAST(c21.ID), c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
>
> Anybody knows anything about this subject? hehe :-)
> Thanks a lot in advance,
> Pieter
>

LAST / TOP 1 / ...?

Hi,
I always thought there was a way to have the Last record of the group of
records that accorded to your query. But when I tried "LAST" this didn't
work really (althoug the Query analyzer recognized it as a valid
statement?).
I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
really be nice if there was something like a LAST and FIRST-function.
I have now:
SELECT TOP 1 c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
ORDER BY c21.ID DESC
But it would be nicer if this could be without the ORDER BY and some
LAST-function:
LAST(c21.ID), c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
Anybody knows anything about this subject? hehe :-)
Thanks a lot in advance,
PieterThat would imply that a table is ordered, which it isn't.
(Think about it, for each way to represent information, you need 4 set of
operators, read, add, remove and change. In SQL, we already have SELECT,
INSERT, DELETE and UPDATE. I don't think we want 4 more operators just to
add the concept of ordered tables.)
However, Yukon will probably have some language constructs which make paging
and stuff a bit easier to manage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eNryScgAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I always thought there was a way to have the Last record of the group of
> records that accorded to your query. But when I tried "LAST" this didn't
> work really (althoug the Query analyzer recognized it as a valid
> statement?).
> I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
> really be nice if there was something like a LAST and FIRST-function.
> I have now:
> SELECT TOP 1 c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
> ORDER BY c21.ID DESC
> But it would be nicer if this could be without the ORDER BY and some
> LAST-function:
> LAST(c21.ID), c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
>
> Anybody knows anything about this subject? hehe :-)
> Thanks a lot in advance,
> Pieter
>

Large Volumes of Varchar Data - Design Advice

Hello all,

I have recently been task with rewriting a database that holds large volumes of data, whilst ensuring that query can be run in optimal time. Having never really delved into this sort of thing before, I hoped you guys might be able to offer some advice and guidance.

The design I have inherited is based around 2 main tables:

[captured_traps]
[id] [int] IDENTITY (1, 1) NOT NULL
[snmp_version] [int] NULL
[community_name] [varchar] (255)
[packet_type] [varchar] (50)
[oid] [varchar] (500)
[source_ip] [varchar] (15)
[generic] [int] NULL
[specific] [int] NULL
[time_stamp] [varchar] (15)
[trap_entered] [datetime] NULL
[status] [int] NULL

[captured_varbinds]
[id] [int] IDENTITY (1, 1) NOT NULL
[captured_trap_id] [int] NOT NULL
[varbind_oid] [varchar] (500)
[varbind_text] [varchar (500)

The relationship between the two tables is on the "captured_traps (id)" to "captured_varbinds (captured_trap_id)". Currently the "captured_traps" table contains around 350 million rows, the "captured_varbinds" table contains around 900 million rows.

Now as you can probably gather this model runs like a...well it sort of hobbles more than runs hence the need to redesign.

My current thoughts on this are:

- Normalising all varchars - there is alot of duplicate values in most of the varchar fields.
- Full Text Indexing

However beyond that I am not sure which route to go down. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is fatally flawed somewhere.what fields do you currently search and join on? are there indices there? if so have they been maintained? are they in seperate filegroups and on seperate disks than the data? Any thought given to partititioning?|||As far as searches are concerned users may search any field or combination of fields. A typical search example might be:

snmp_version = 2
oid = 1.2.5.67345.12.34
trap_entered > a UTC date time
trap_entered < a UTC date time

This would be expected to return all data from captured_traps and all associated captured_varbinds with the relationship on "captured_traps (id)" to "captured_varbinds (captured_trap_id)".

or a user might search on the varbind table instead, and expect to see all traps with a specific varbind_oid value, for example

varbind_oid = 2.3.4.123.6.4.5.8766
trap_entered > a UTC date time
trap_entered < a UTC date time

This would be expected to return all traps within the given date range that have a varbind with of the given type

The previous owner of this DB only put indexes on the two id columns

As far as filegroups and partioning are concerned, I have encountered these whilst searching for solutions - and if viable may be implemented.

Basically I have a clean slate here, so i am open to any and all suggestions|||what version of SQL Server are you running?

We will also need to see the indexing methodology...do you know how to script those?

Also, how is all the data accessed? Stored Procedures only?

What do you get when you do sp_depends on those 2 tables?|||As mentioned earlier I have a clean slate to work from, so version can be anything from 2000sp4 to 2005. Which ever is better suited to the task.

Data will only ever be accessed via stored procedures, however due to the fact that searches can be on any fields or combination of fields the main search stored procedure will build and execute the SQL dynamically (unless anybody has any better ideas)

As far as indexes are concerned, this are the full scripts for the current model

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_snmp_captured_varbinds_snmp_captured_traps]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[snmp_captured_varbinds] DROP CONSTRAINT FK_snmp_captured_varbinds_snmp_captured_traps
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snmp_captured_traps]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[snmp_captured_traps]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snmp_captured_varbinds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[snmp_captured_varbinds]
GO

CREATE TABLE [dbo].[snmp_captured_traps] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[snmp_version] [int] NULL ,
[community_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[packet_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oid] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source_ip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[generic] [int] NULL ,
[specific] [int] NULL ,
[time_stamp] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[packet_data] [image] NULL ,
[trap_entered] [datetime] NULL ,
[status] [int] NULL ,
[support_reference] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[listener_event_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[snmp_captured_varbinds] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[trap_id] [int] NULL ,
[varbind_oid] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varbind_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_traps] WITH NOCHECK ADD
CONSTRAINT [PK_snmp_captured_traps] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_varbinds] WITH NOCHECK ADD
CONSTRAINT [PK_snmp_captured_varbinds] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_traps] WITH NOCHECK ADD
CONSTRAINT [DF_snmp_captured_traps_status] DEFAULT (1) FOR [status]
GO

CREATE INDEX [IDX_snmp_captured_varbinds_trap_id] ON [dbo].[snmp_captured_varbinds]([trap_id]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_varbinds] ADD
CONSTRAINT [FK_snmp_captured_varbinds_snmp_captured_traps] FOREIGN KEY
(
[trap_id]
) REFERENCES [dbo].[snmp_captured_traps] (
[id]
) ON DELETE CASCADE
GO

Edit : in the new model the packet_data column of the snmp_captured_traps table is not required|||If you are using sprocs, why would you need dynamic sql?

In any case, you only have 1 index...

How do the rows get added to these tables, and what is the rate of inserts

You will need more indexes, and probably need to utilize index intersection instead of of coverering indexes

With the additional indexes you are probably scanning the tables...|||Well the dynamic SQL is an assumption I am carrying across from the current design. Currently there is no stored proc for doing searches, the application that sits ontop of this database builds an SQL string based on user input and then executes it. Something along the lines of:

SELECT x,y,z FROM snmp_captured_traps WHERE a=b AND c=d AND e>f AND g<h

I dont like the fact that that the SQL string is controlled by the app, it also means that remote users have to have select priveledges on the necessary tables. Hence the idea of moving that code logic into a stored procedure. I am open to any ideas/suggestions on how to do this using an alternative system

As far as getting data into the tables at the moment there is simply a series of simple insert stored procedures, e.g.

CREATE PROCEDURE add_captured_trap
(
@.snmp_version int,
@.community_name varchar(255),
@.packet_type varchar(50),
@.oid varchar(500),
@.source_ip varchar(15),
@.generic int,
@.specific int,
@.trap_entered datetime,
@.packet_data image,
@.listener_event_id int
)
AS BEGIN TRANSACTION

INSERT INTO snmp_captured_traps (
snmp_version,
community_name,
packet_type,
oid,
source_ip,
generic,
specific,
trap_entered,
packet_data,
listener_event_id
)
VALUES (
@.snmp_version,
@.community_name,
@.packet_type,
@.oid,
@.source_ip,
@.generic,
@.specific,
@.trap_entered,
@.packet_data,
@.listener_event_id
)

select scope_identity();

COMMIT TRANSACTION
GO

CREATE PROCEDURE add_captured_varbind
(
@.trap_id int,
@.varbind_oid varchar(500),
@.varbind_text text
)
AS BEGIN TRANSACTION

INSERT INTO snmp_captured_varbinds (
trap_id,
varbind_oid,
varbind_text
)
VALUES (
@.trap_id,
@.varbind_oid,
@.varbind_text
);

COMMIT TRANSACTION
GO

As you can see nothing to clever, and it worked ok until the load became to much.

As far as volumes of inserts, on average the system receives 50 traps a second, each trap has an average of 2 varbinds. So thats 150 inserts

One other thing, everything I have said so far seems to indicate that I want to redesign from scratch. Thats not entirely true, if there is something that I can apply to the current model in order to get things running quicker then that is my preferred short term option

large union query

Hi there
I have a large union query that produces a "timeout expired" error. I've
changed the timeout settings to 600 seconds but I still get the message.
My query and underlying database are characterized as follows:
a) two fairly large database tables (1.5m and 0.7m rows respectively)
b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
c) a couple of "LIKE '%this%'" clauses in each SELECT statement
The query should return around 7-8000 rows.
Are there any guidelines, either as to the maximum feasible size of such a
query, or as to an alternative method to achieve the same thing?
Best regards
Loane
Hi
LIKE '%this%'" results in a table scan as it can not use an index. Is there
any other column that is not using a like that could reduce the number of
rows SQL Server would need to process?
Scanning 2.2m rows can take long. How long does it take in query analyzer?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp@.hotmail.com> wrote in message
news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Hi there
> I have a large union query that produces a "timeout expired" error. I've
> changed the timeout settings to 600 seconds but I still get the message.
> My query and underlying database are characterized as follows:
> a) two fairly large database tables (1.5m and 0.7m rows respectively)
> b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
> c) a couple of "LIKE '%this%'" clauses in each SELECT statement
> The query should return around 7-8000 rows.
> Are there any guidelines, either as to the maximum feasible size of such a
> query, or as to an alternative method to achieve the same thing?
> Best regards
> Loane
>
|||Hi Mike
Thanks for this ... I'm pretty much self-taught on SQL Server and generally
prefer to use T-SQL from the SQL pane, which no doubt this causes all sorts
of problems. Moreover, I'm not a DBA (actually an economist/statistician
type) so my use of SQL Server is probably not the one for which it was
designed. But it is a bit worrying to me that changing the timeout settings
to 10 minutes (Tools>Options>Advanced...) doesn't make an impact on a large
or complex query, which still returns a timeout error after 4 or 5 seconds.
I haven't used Query Analyser much and will check this out.
Best regards
Loane
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23SHhklALFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi
> LIKE '%this%'" results in a table scan as it can not use an index. Is
> there
> any other column that is not using a like that could reduce the number of
> rows SQL Server would need to process?
> Scanning 2.2m rows can take long. How long does it take in query analyzer?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Loane Sharp" <look_sharp@.hotmail.com> wrote in message
> news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
>

large union query

Hi there
I have a large union query that produces a "timeout expired" error. I've
changed the timeout settings to 600 seconds but I still get the message.
My query and underlying database are characterized as follows:
a) two fairly large database tables (1.5m and 0.7m rows respectively)
b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
c) a couple of "LIKE '%this%'" clauses in each SELECT statement
The query should return around 7-8000 rows.
Are there any guidelines, either as to the maximum feasible size of such a
query, or as to an alternative method to achieve the same thing?
Best regards
LoaneHi
LIKE '%this%'" results in a table scan as it can not use an index. Is there
any other column that is not using a like that could reduce the number of
rows SQL Server would need to process?
Scanning 2.2m rows can take long. How long does it take in query analyzer?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp@.hotmail.com> wrote in message
news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Hi there
> I have a large union query that produces a "timeout expired" error. I've
> changed the timeout settings to 600 seconds but I still get the message.
> My query and underlying database are characterized as follows:
> a) two fairly large database tables (1.5m and 0.7m rows respectively)
> b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
> c) a couple of "LIKE '%this%'" clauses in each SELECT statement
> The query should return around 7-8000 rows.
> Are there any guidelines, either as to the maximum feasible size of such a
> query, or as to an alternative method to achieve the same thing?
> Best regards
> Loane
>|||Hi Mike
Thanks for this ... I'm pretty much self-taught on SQL Server and generally
prefer to use T-SQL from the SQL pane, which no doubt this causes all sorts
of problems. Moreover, I'm not a DBA (actually an economist/statistician
type) so my use of SQL Server is probably not the one for which it was
designed. But it is a bit worrying to me that changing the timeout settings
to 10 minutes (Tools>Options>Advanced...) doesn't make an impact on a large
or complex query, which still returns a timeout error after 4 or 5 seconds.
I haven't used Query Analyser much and will check this out.
Best regards
Loane
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23SHhklALFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi
> LIKE '%this%'" results in a table scan as it can not use an index. Is
> there
> any other column that is not using a like that could reduce the number of
> rows SQL Server would need to process?
> Scanning 2.2m rows can take long. How long does it take in query analyzer?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Loane Sharp" <look_sharp@.hotmail.com> wrote in message
> news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
>

Large table/slow query/ can performance be improved?

I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?

Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?

Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.

If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.
|||Hi,

These are some tips from my side.

1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.

Finally, can you elaborate/post what exactly u r trying to do and
achive.

Amit

"Robert" <stop.spam@.whitehouse.gov> wrote in message news:<HLFKqK.MqD@.news.boeing.com>...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform more
> of the work on the server there by minimizing the amount of extraneous table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?|||As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.

1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)

2) In Access, make a new query, but don't choose any tables.

3) Choose Query/SQL Specific>Pass-Through

4) Paste the SQL Statement in there

5) Choose View/Properties

6) Set the Connect string (and ODBC Timeout!) appropriately

Run the query.

I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!

The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!

You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)

Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.

If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through...this is the best way to optimize performance.

CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!

I'd be interested in seeing the SQL Statement of the 6-hr query.