Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/

print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate

delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate

delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/

--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test

--need help

-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query help

I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

Query for the first and latest wish

Hi all,

I have the following table

Name Date Wish Valid

Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

So, a typical data set looks like:

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

I can do UNION, but is there another way to do that?
Thank you.On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

Hi all,
>
I have the following table
>
Name Date Wish Valid
>
Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.
>
So, a typical data set looks like:
>
Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1
>
My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.
>
I can do UNION, but is there another way to do that?
Thank you.

-- Put them into a temporary table:

SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate

-- Then compare the values

SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date

Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.

Good luck!

-Utah|||Eugene (alsu50@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:
>
Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1
>
My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hi all,

>

Quote:

Originally Posted by

I have the following table

>

Quote:

Originally Posted by

Name Date Wish Valid

>

Quote:

Originally Posted by

Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

>

Quote:

Originally Posted by

So, a typical data set looks like:

>

Quote:

Originally Posted by

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

>

Quote:

Originally Posted by

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

>

Quote:

Originally Posted by

I can do UNION, but is there another way to do that?
Thank you.

>
-- Put them into a temporary table:
>
SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate
>
-- Then compare the values
>
SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date
>
Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.
>
Good luck!
>
-Utah

Utah,

Thank you for the idea! However, having the extra step of getting the
temp table is not something that I think the DBA here would approve.
The good news is that the date field is the datetime (defaulting to
getdate()) and it puts the date and time up to milliseconds, so the
chances for two people making the wish at the same time are very
minimal.

Thanks again!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

>

Quote:

Originally Posted by

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

>

Quote:

Originally Posted by

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

>
SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Wow. Simple and elegant, what else can I say?! Thank you!|||On Feb 22, 1:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Eugene (als...@.gmail.com) writes:

Quote:

Originally Posted by

So, a typical data set looks like:

>

Quote:

Originally Posted by

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

>

Quote:

Originally Posted by

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

>
SELECT a.Name, a.FirstDate, f.Wish .FirstWish,
a.LastDate, l.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hm, I thought this was like conversation mode, so the reply would have
posted right underneath the answer. Anyway, Thanks a bunch, Erland!

BTW, for the folks who is looking at this some time later, the working
query looks like the following:

SELECT a.Name, a.FirstDate, b.Wish as FirstWish,
a.LastDate, c.Wish as LastWish
FROM (SELECT Name, FirstDate = MIN(Date), LastDate = MAX(Date)
FROM wishes
WHERE Date BETWEEN @.start AND @.end
GROUP BY Name) AS a
LEFT JOIN wishes b ON a.Name = b.Name AND a.FirstDate = b.date
LEFT JOIN wishes c ON a.Name = c.Name AND a.LastDate = c.date|||Eugene wrote:

Quote:

Originally Posted by

I have the following table
>
Name Date Wish Valid
>
Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

The 'Valid' column is redundant (you can use MAX(Date) instead) and
breakable (what if a row with Valid = 1 is deleted?). I'd ditch it
if I were you.|||On Feb 22, 6:41 pm, "Eugene" <als...@.gmail.comwrote:

Quote:

Originally Posted by

On Feb 22, 1:25 pm, Utahd...@.hotmail.com wrote:
>
>
>

Quote:

Originally Posted by

On Feb 22, 3:09 pm, "Eugene" <als...@.gmail.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hi all,

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I have the following table

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Name Date Wish Valid

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Name is person's name, date defaults to getdate() and is never
assigned directly (datetime field), Wish is some message, and Valid is
bit, 1 indicates if the wish is the latest, and therefore valid. All
previous wishes are kept in database, and are "invalidated" by setting
the Valid to 0.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

So, a typical data set looks like:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Name Date Wish Valid
Joe 02/01/2007 Ice Cream 0
Joe 02/04/2007 Bicycle 0
Joe 02/06/2007 PS3 0
Joe 02/22/2007 XBox 360 1
Mary 02/02/2007 Barbie 0
Mary 02/04/2007 Cindy 0
Mary 02/06/2007 Barbie house 0
Mary 02/20/2007 Get married 1

>

Quote:

Originally Posted by

Quote:

Originally Posted by

My users want to see the initial wish at some point and another one
some time later (they provide dates). So, if someone wanted to see
changes in wishes between 02/03 and till 02/15, they would get that
Joe's initial wish was Bicycle and the latest that he wanted was PS3.
As for Mary, she started wanting Cindy and ended up thinking about the
Barbie house.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I can do UNION, but is there another way to do that?
Thank you.

>

Quote:

Originally Posted by

-- Put them into a temporary table:

>

Quote:

Originally Posted by

SELECT Name, Min(Date) as FirstWishDate, Max(Date) as LastWishDate
INTO #FirstAndLast
FROM Wishlist
WHERE Date >= @.StartingDate
AND Date <= @.EndingDate

>

Quote:

Originally Posted by

-- Then compare the values

>

Quote:

Originally Posted by

SELECT t.Name, t.FirstWishDate, w1.Wish as FirstWish, t.LastWishDate,
w2.Wish as LastWish
FROM #FirstAndLast t,
WishList w1,
WishList w2
WHERE t.Name = w1.Name
AND t.FirstWishDate = w1.Date
AND t.Name = w2.Name
AND t.FirstWishDate = w2.Date

>

Quote:

Originally Posted by

Of course, this is supposing they've only made one wish per day,
otherwise you'll duplicate some rows. If that is the case, make sure
you are tracking times as well.

>

Quote:

Originally Posted by

Good luck!

>

Quote:

Originally Posted by

-Utah

>
Utah,
>
Thank you for the idea! However, having the extra step of getting the
temp table is not something that I think the DBA here would approve.
The good news is that the date field is the datetime (defaulting to
getdate()) and it puts the date and time up to milliseconds, so the
chances for two people making the wish at the same time are very
minimal.
>
Thanks again!

Oops, yeah, temporary tables have their places and this wouldn't be
one of them. But, I think you've got the idea.

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
Wang
What happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang
|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:
> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> > Hi,
> >
> > My query 1 needs to narrow down a time range. I index the column
> > (pos_trandate_time, plus the other on the where clause), but the
> performance
> > still very slow, around 1 minutes. Wherease the query on date only is much
> > faster. What should I do?
> >
> > --query 1, cost 99%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> > pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> > as tx_msg_type, pos_trans_code as tx_tran_type
> > from pos_txn_log (index=pk_pos_txn_log)
> > where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> > and pos_trandate_time <= '2004-07-05 23:59:59'
> > order by pos_trandate_time
> >
> > -- query 2, cost %1
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > order by pos_trandate_time
> >
> >
> > Thanks
> > Wang
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>

Query DateTime DataType for Current or Future Events

I have a sql server express 2005 database with a table named Events with a column named Date (datetime datatype). I want a query that will display all rows that are either current or future but not past. I suspect there is a simple way of doing this. As a Newbie searching archived threads this is what I have come up with so far. I determine the number of days from present:

SELECTDATEDIFF(day, Date,GETDATE())AS NumberOfDays

FROMEvents

This yields number of days from present with positive numbers in the past and negative numbers in the future. Thus setting a WHERE clause to <= 0 would limit my results to present or future events. Something like this:

SELECT*

FROM Events

WhereDATEDIFF(day, Date,GETDATE())AS NumberOfDays<= 0

The error message states: "Incorrect syntax near the keyword 'AS'"

This feels like a clumsy way to approach this problem, but I have to start where I am.

Any suggestions on how to proceed will be greatly appreciated.

SELECT*FROM EventsWhereDATEDIFF(day, Date,GETDATE())<= 0

or

select*FROM EventsWHERE Date>=GETDATE()

|||Thanks limno. Exactly what I needed.

Query datetime and its performance

Hi all,

I know I miss something very obvious here, but I can see it at the moment - I am very appreciated if someone could help.

Why am I have a table scan when I run the query below - note created_date is indexed.

DECLARE @.LastDate datetime

SET @.lastDate = (SELECT DATEADD(hh,-1,getdate())

SELECT * FROM Report WHERE Created_Date >@.lastDate

However, I don't have table scan when I do the following query

SELECT * FROM Report WHERE Created_Date > (Getdate()-1)

Thanks in advance

Because you are using SELECT statement to obtain the value. Use the following sentences instead:

DECLARE @.LastDate datetime;
SET @.lastDate = DATEADD(hh,-1,getdate();
SELECT * FROM Report WHERE Created_Date>@.lastDate;

Regards,|||

Thanks for replying Ronald

but nope, tried that - I still have table scan. :-(

BTW I am using SQL 2000

|||

Philly:

I think that the answer is in two parts. First, your SELECT * query requires a bookmark lookup for each row selected by your where clause whenever it uses an nonclustered index that does not include all columns of the table as part of the index -- and that is the case with your index based only on this date. The optimizer "knows" that if there are enough bookmark lookups that it is more efficient to do a table scan than an index scan that includes bookmark lookups. Second, (somebody PLEASE check me on this one) the optimizer has special handling for "getdate" that it does not have for the variable. Therefore, it can "figure out" that there are not enough rows in your GETDATE()-1 query so that the cost of the bookmark lookups do not outweigh the cost of the table scan. It does NOT make this optimization when you compare to your variable.

Dave

|||In this case, the table scan is probably the most effienct way to get the data, according to the query optimizer. Table scans are not always bad.

Your select statement is getting all fields in the table and excluding probably a handful of rows. If it uses an index, it will have to find each matching record in the index, get the PK from the index, look up the PK in the clustered index to get the record. Not using the secondary index, it just reads the table and does the compare and it is done.

Now, if you were doing a "SELECT Created_Date FROM ...." That should use the index, because the data is all contained in the index and doesn't need to reference the table at all.|||

Thanks all for replying,

I guess this is one case that query optimizer try to out smart us, which is not a bad idea in some case. The problem I have is the table I am query from is very large, several million records, so table scan is bad for me. I don't know if Getdate() does some special handling as Dave mentioned, but it definetly handle different than the variable - which is interesting. However, I understand Tom's suggestion is use "SELECT Created_Date FROM..". I tried that and it did works nicely, I wonder if there is other way to force sql use index here instead of including the created_field after the select statement.

Thanks again

|||

You can force SQL Server to use an index. This usually isn't recommended, but may solve your problem. The syntax would be...

SELECT * FROM Report With(Index=IndexNameHere) WHERE Created_Date>@.lastDate;

Query Data using Date

I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
I want to query a table which contains a column of data type datetime using
ASP form, where the input of type text. Before querying I cahnge input text
into date format using CDate() function.
When I run the query I got this message:
"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime value.
"
I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
Developer Ed. When I ran the same query It works.
Wish to know what is the problem?Hi
Use YYYYMMDD format to deal with dates.
"wira659" <wira659@.discussions.microsoft.com> wrote in message
news:9E5FE92D-BA5B-45F4-B445-FF2DA189EC72@.microsoft.com...
> I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
> I want to query a table which contains a column of data type datetime
using
> ASP form, where the input of type text. Before querying I cahnge input
text
> into date format using CDate() function.
> When I run the query I got this message:
> "Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
data
> type to a datetime data type resulted in an out-of-range datetime value.
> "
> I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
> Developer Ed. When I ran the same query It works.
> Wish to know what is the problem?

Query between Times

i have two DateTime fields One called 'Open' and one 'Closed' . I need to
know if a given time is inbetween the Open and Closed times ?Here's an example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
if @.dt >= @.OpenDate and @.dt <= @.CloseDate
print 'yep'
else
print 'nope'
Bryce|||Woops, I just noticed the word "QUERY" in your post. Here's another
example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
select case when @.dt >= @.OpenDate and @.dt <= @.CloseDate then 1 else 0
end
Of course, when selecting from a table, the variables can be replaced
with column names.
Bryce|||Assuming Open and Cl;osed values are in Variables @.Open and @.Closed,
If this is being done in a Select Statement, then put a Predicate expression
"GivenTimeColName Between Open And Closed"
in the Where Clause.
Select <col List>
From <Table(S)>
Where <GivenTimeColName > Between Open And Closed
If it's being done in T-SQL Code, just put the predicate after an "If "
If @.GivenTimeVariable Between @.Open And @.Closed
Begin
<T-SQL Statements to execute if true
End
Else
Begin
<T-SQL Statements t oexecute if false>
End
"Peter Newman" wrote:

> i have two DateTime fields One called 'Open' and one 'Closed' . I need to
> know if a given time is inbetween the Open and Closed times ?
>|||Use the BETWEEN keyword, as in:
select blablabla
from blablabla
where a_given_time between Open and Closed
Does this help?
Raj|||> set @.dt = '1/5/2005'
Ugh. Is that January 5th or May 1st? Who knows? It depends on several
factors, including regional settings on the machine, SET LANGUAGE, SET
DATEFORMAT settings, etc.
If you're going to hard code dates in the query like that, at least use a
non-ambiguous format.
SET @.dt = '20050105'

> set @.CloseDate = '1/30/2005'
> if @.dt >= @.OpenDate and @.dt <= @.CloseDate
Ugh again. You are including rows from midnight on the close date, but
nothing else. So if something happened at 12:01, it's ignored by your where
clause. I'm not sure if the original poster wanted to include that day or
use it as a cutoff. It is better to use:
IF @.dt >= '20050105' AND @.dt < '20050130'
or
IF @.dt >= '20050105' AND @.dt < '20050131'
(Depending on whether the data from throughout the day on 20050130 should be
included or not.)|||> Select <col List>
> From <Table(S)>
> Where <GivenTimeColName > Between Open And Closed
FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
queries (though it is great for INTs, I will agree). Except in the rare
case where only whole dates (with a midnight timestamp) are stored, it is
always better to use >= start_of_range and < (end_of_range + 1).
http://www.aspfaq.com/2280|||Thanks, Aaron. Very informative.
Bryce|||Aaron,
Is your main rationale for feeling this way becuase of Betweens
inflexible "Inclusive" behavior, which always also returns records which are
equal to the enddate value?
This IS a problem I have not found a clean answer for...
I still like Between, however, because it "reads" much moore naturally, (I
mean closer to the problem you are trying to solve.. It's not a big
difference, I grant you, but when you have constructions like:
Where My_DateCOlumnValue >= @.BeginDateRangeValue And My_DateCOlumnValue <
@.EndDateRangeValue
You have to read the token for the My_DateCOlumnValue twice, in both places,
to ensure that it is indeed the same token... this issue is more significant
when it's NOT a token or column name, but a longer expression based on one o
r
more columns, (Not desired, but sometimes necessary)
In those cases, the Between construction "reads" easier to me than the >= ..
... And ... < ... since the complex expression would only be there once...
But you still have to solve the Inclusive Issue...
on another note, I believe that the Query Processor converts the between
syntax into >= AND <= to execute it anyway ...
"Aaron [SQL Server MVP]" wrote:

> FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
> queries (though it is great for INTs, I will agree). Except in the rare
> case where only whole dates (with a midnight timestamp) are stored, it is
> always better to use >= start_of_range and < (end_of_range + 1).
> http://www.aspfaq.com/2280
>
>|||Aaron,
The only point you made worth speaking to is
<<<
Then you will include rows from 20040105 at exactly midnight, but nothing
later. This trips *many* people up, because they assume any row from
20040105 (say, 20040105 15:43:04.872) should be included.
And there are several solutions ot this, some of which I mentioned already.
You're right, this trips up a lot of folks. But I guess your approach is
everybody else (except for you of course) is too stupid to understand this
and therefore should just NOT use such potentially dangerous tools.
There are unfortunately, quite too many people who think that way in this
world.
I prefer to assume, (until proven otherwise) that anyone is capable of
undersstanding the dangers (and the power) of any tool enough to learn to us
e
it intelligently and wisely, at least if someone is kind enough to teach the
m
how, and offer them the knowledge and the opportunity.
So, t oanswer yr final thought, NO, I will not stop attempting to "drown
out" anyone who attempts to limit or restrict what information orr technique
s
are made available, especially whne done because in someone's opinion, some
technique is "too dangerous" for the average person to use...
"Aaron [SQL Server MVP]" wrote:
> places,
> How complex do you name your columns? This wouldn't be an issue if you us
e
> sensible names and avoid nedlessly long and complicated identifiers. And
> unless you are dealing solely with DATETIME columns throughout your entire
> environment, it's something you have to approach from a broader perspectiv
e
> than simply whether to use BETWEEN or separate clauses.
>
> or
> If you are performing calculations against dates on the left side of the
> equation, it is always possible to do tose same calculations on the right
> side of the equation (not only avoiding your complaint, but also giving th
e
> query a better chance to use an index). For example:
> WHERE DATEADD(MONTH, -2, DateTimeColumn) + 1 < '20050101'
> is the same as
> WHERE DateTimeColumn < DATEADD(MONTH, 2, '20050101') - 1
> Comparing these this way is actually more logical, in my opinion, and stil
l
> allows you to use an index and avoid scanning the left side for expression
s
> involving the column name.
>
> Yes, and that's WHY it's a problem. If you have:
> BETWEEN '20040101' AND '20040105'
> Then you will include rows from 20040105 at exactly midnight, but nothing
> later. This trips *many* people up, because they assume any row from
> 20040105 (say, 20040105 15:43:04.872) should be included. If you have the
> true boundary on the right side, you would have to use
> '2004-01-04T23:59:59.997' and pray it doesn't get implicitly converted to
> SMALLDATETIME (which rounds up, yielding the exact same problem).
> But go ahead, keep using BETWEEN. Doesn't sound like the points I'm raisi
ng
> are very important to you anyway (but please don't try to drown them out f
or
> those people who might consider them worthwhile).
> A
>
>

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <= @.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil