query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!
"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!
|||<snip>[vbcol=seagreen]
<snip>
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)
|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!

> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>
sql

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!<snip>
> > ((dbo.Lead.LeadClosed)=01/01/1900) AND
<snip>
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
--
(Please reply only to the newsgroup)|||try this
SELEC
Personal.ID
Personal.Surname1,
Lead.FactfindCompleted
Lead.FactfindCompletedB
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.I
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHER
Lead.FactfindCompleted > 01/01/2004 AN
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AN
Mortgage.MortgageApplicationClosed Is Nul
ORDER BY Lead.FactfindCompleted
G

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>sql

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!|||<snip>
<snip>[vbcol=seagreen]
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integ
er arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>,
<, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LE
FT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>

Query performance

Hi,

we have a cube which is basically structured like that:

We have dimensions like D1 (2000 records), D2 (6000 records) and D3 (6000 records). There are some others (like a time dimension), but they don't matter so much (I think). D1, D2 and D3 are defined as Parent-Child dimensions, but we don't use this structure, yet.

We also have some facts, just around 10,000 records.

There are other fact tables using some of the dimensions as well, but I guess that's not important at all.

We have a query which runs forever. This is using some attributes from D1, D2 and D3 and just one measure. Basically it's a select of one measure on the columns and a non empty crossjoin of the three attribute hierarchies we use from the dimensions. So really nothing fancy. We have some calculated members (measures) as well, but they are not used in this query...

Select {[Measures].[X]} on columns, non empty {{[D1].[H1].[All].children}*{[D2].[H2].[All].children}*{[D3].[H3].[All].children}} on rows from [DB]

We constructed the MDX by ProClarity, they add some cell properties at the end of the query, using that we see the memory flowing away to msmdsrv.exe...

I understand that internally SSAS has to calculate a cross join with 2000x6000x6000 combinations. But this is something SSAS has to be able to handle, otherwise the concept of Attribute Hierarchies doesn't work at all.

We're using SSAS 2005 RTM, a colleague tested that with SP1, no change.

So what's wrong with the query or the concept or the person typing this post?

Thanks,

I think you should try nonemptycrossjoin instead:

Select {[Measures].[X]} on columns, {nonemptycrossjoin([D1].[H1].[All].children,[D2].[H2].[All].children,[D3].[H3].[All].children)} on rows from [DB]

nonemptycrossjoin is more efficient when there're several dimensions,

|||

I think you should try nonemptycrossjoin instead:

Select {[Measures].[X]} on columns, {nonemptycrossjoin([D1].[H1].[All].children,[D2].[H2].[All].children,[D3].[H3].[All].children)} on rows from [DB]

nonemptycrossjoin is more efficient when there're several dimensions,

|||

Have you thought of creating a fact dimension containing H1, H2, H3?

With your attributes approaching size of the fact table it might be better idea.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Query performance

Hello,
I wrote the query below, but I was wondering whether there was a better way
of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
****************************************
************************************
*****************
****************************************
************************************
*****************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId = 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
BEGIN
select @.customerId = customerId,
@.parentId = parentId,
@.levelId = levelId
FROM #t
WHERE dummyId = @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId = @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId = @.parentId
and not exists (select * from #t where c.customerAutoId = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId = @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId = t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GOIf you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:

> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> ****************************************
******************************
*
> **********************
> ****************************************
******************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId =
> customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.
207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>
> *
> *
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

query on date field with AM/PM designation

I need to query a table that has the date field formatted as follows:

12/1/2000 12:33:16 PM

The query basically is looking for a range between two dates.

select * from area_formulae where create_dt > to_date('12/1/2000 12:33:15','mm/dd/yyyy hh:mi:ss')
and create_dt < to_date('12/1/2000 12:33:18','mm/dd/yyyy hh:mi:ss')

This query that will work but it ignores the AM or PM designation. How would I query for a range of dates that would cover both the AM and PM ranges?Can you do this?

'mm/dd/yyyy hh:mi:ss AM/PM'

Query multiple database tables

Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?

If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.

Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.