latest data for different dates

I have two tables
Well status
Date,wellid,gas
1/5,A,10
1/5,B,20
2/5,A,11
2/5,B,19
1/5,A,10
2/5,B,21
Well test
Date,wellid,gas
20/4,A,10
2/5,A,10
19/4,B,20
the output I want is
Date,Wellid,gas,LastweltestDate,GasfromL
astwelltest
1/5,A,10, 20/4,10
1/5,B,20, 19/4,20
2/5,A,11, 2/5,10
2/5,B,19, 19/4,20
1/5,A,10, 2/5,11
2/5,B,21, 19/4,20
Basically latest well test dates and corresponding gas for that each
day. There is a gap between two welltests. So if a well test happens on
1st and then on 5th, then till 5th the latestwell test date is 1st.
I hope I am clear.
thanks
PradeepPradeep (agarwalp@.eeism.com) writes:
> Well status
> Date,wellid,gas
> 1/5,A,10
> 1/5,B,20
> 2/5,A,11
> 2/5,B,19
> 1/5,A,10
> 2/5,B,21
>
> Well test
> Date,wellid,gas
> 20/4,A,10
> 2/5,A,10
> 19/4,B,20
> the output I want is
> Date,Wellid,gas,LastweltestDate,GasfromL
astwelltest
> 1/5,A,10, 20/4,10
> 1/5,B,20, 19/4,20
> 2/5,A,11, 2/5,10
> 2/5,B,19, 19/4,20
> 1/5,A,10, 2/5,11
> 2/5,B,21, 19/4,20
SELECT s.Date, s.wellid, s.gas, t.date, t.gas
FROM wellstatus s
JOIN welltest t ON s.wellid = t.wellid
AND t.date = (SELECT MAX(t2.date)
FROM welltest t2
WHERE t2.wellid = s.wellid
AND t2.date <= s.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|||Try:
SELECT dt, wellid, gas,
( SELECT t2.gas FROM test t2
WHERE t2.wellid = D.wellid
AND t2.dt = D.last_test )
FROM ( SELECT s1.dt, s1.wellid, s1.gas, MAX( t1.dt )
FROM status s1
INNER JOIN test t1
ON t1.wellid = s1.wellid
AND t1.dt <= s1.dt
GROUP BY s1.dt, s1.wellid, s1.gas
) D ( dt, wellid, gas, last_test ) ;
In the future, please post datatypes, keys, constraints etc. so that others
can better understand the nature of the data.
Anith|||Thanks,
I'll try and let you know.
Thanks a Lotsql

Last year comparable Business Day

/**
DESCRIPTION
Okay, I have an app that I need to compare information from two dates
in. The two dates are whatever date is passed to it, and its equivalent
business day from last year. For instance, today is January 10th,
2006. January 10th is the tuesday of the second w in 2006. I need to
be able to compare it the tuesday of the second FULL w in 2005 (Full
w being the catch).
So, if you give it 1/6/06, it will output 1/7/05
1/2/06 => 1/3/05
12/31/06 => 1/1/06
I have a function below that is almost there, but it runs into some
hiccups towards the end of the year. Try inputing 12/31/05, and you
will see what I mean.
That being said, here is my function. You wil have to doctor a few
lines due to word wrap.
FUNCTION
**/
CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @.DATELY datetime,
@.WEEKTY int,
@.WEEKLY int,
@.WEEKDAYTY int,
@.WEEKDAYLY int,
@.WDSUNDAYLY datetime,
@.WDSATURDAYLY datetime,
@.BOY datetime,
@.BOLY datetime
SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
SET @.BOY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.datetime)))
SET @.BOLY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
SET @.WEEKTY = DATEPART("wk", @.datetime)
SET @.WEEKLY = DATEPART("wk", @.DATELY)
BEGIN
IF @.WEEKTY <> @.WEEKLY
IF @.WEEKTY > @.WEEKLY
SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
ELSE IF @.WEEKTY < @.WEEKLY
SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
END
SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
6),@.BOLY)
BEGIN
IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
(@.WEEKTY <> @.WEEKLY)
BEGIN
SET @.DATELY = DATEADD("d",7,@.DATELY)
END
END
SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
BEGIN
IF @.WEEKDAYTY <> @.WEEKDAYLY
IF @.WEEKDAYTY > @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
@.DATELY)
END
RETURN @.DATELY
ENDOn 10 Jan 2006 14:11:54 -0800, Brian Baumann wrote:

>/**
>DESCRIPTION
>Okay, I have an app that I need to compare information from two dates
>in. The two dates are whatever date is passed to it, and its equivalent
>business day from last year. For instance, today is January 10th,
>2006. January 10th is the tuesday of the second w in 2006. I need to
>be able to compare it the tuesday of the second FULL w in 2005 (Full
>w being the catch).
(snip)
Hi Brian,
I didn't take the time to disect your code. Instead of going over the
top with complex date calculations, why not simply create a Calendar
table? (See www.aspfaq.com/2519). Your problem can then be solved by a
simple join on same w, same wday, and year = year - 1.
(snip)
>I have a function below that is almost there, but it runs into some
>hiccups towards the end of the year. Try inputing 12/31/05, and you
>will see what I mean.
I didn't test it, but might this not be related to an omission in the
specification? After all, some years have a 53rd w and others don't.
Or they do, but with different wdays in it. Your spec doesn't say how
to handle that case.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> I didn't take the time to disect your code. Instead of going over the
> top with complex date calculations, why not simply create a Calendar
> table? (See www.aspfaq.com/2519). Your problem can then be solved by a
> simple join on same w, same wday, and year = year - 1.
I don't think so. Brian said first full w. I interpret this as
that the corresponding day to 2007-01-08 will be 2006-01-09, but
the corresponding day to 2008-01-08, will be 2007-01-01, because
in 2008 New Years Day is on a Tuesday, so the first full w starts
on 2008-01-07. (Assuming here that you ws starts on Monday.)
In the two w-numbering system, w 1 is eiher the w that
Jan 1st falls on (this is what SQL Server uses) or the w that
Jan 4th falls on (this is the ISO standard for w numbering).
Of course Brian could number the ws in his calendar table after
his own liking, but then he still left with the problem that to number
them. There are also funny things, with New Years Eve mapping back
to New Years Day the same year. It would be a very strange calendar.
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 Tue, 10 Jan 2006 23:30:58 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
>I don't think so.
(snip)
>Of course Brian could number the ws in his calendar table after
>his own liking,
Hi Erland,
That's what I meant. I wanted to add this to my message, but forgot
about it and clicked Send.
Thanks for the addition!
Hugo Kornelis, SQL Server MVP|||So if it is not in the first full w (starting Monday?) then it is the
Monday of the next w?
So if Tues is the day in year x, and w 1 in year x-1 is Tues, then the
day you want is the next Monday?
What about holidays that land on business days?
William Stacey [MVP]
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1136931114.105675.287240@.g44g2000cwa.googlegroups.com...
> /**
> DESCRIPTION
> Okay, I have an app that I need to compare information from two dates
> in. The two dates are whatever date is passed to it, and its equivalent
> business day from last year. For instance, today is January 10th,
> 2006. January 10th is the tuesday of the second w in 2006. I need to
> be able to compare it the tuesday of the second FULL w in 2005 (Full
> w being the catch).
> So, if you give it 1/6/06, it will output 1/7/05
> 1/2/06 => 1/3/05
> 12/31/06 => 1/1/06
> I have a function below that is almost there, but it runs into some
> hiccups towards the end of the year. Try inputing 12/31/05, and you
> will see what I mean.
> That being said, here is my function. You wil have to doctor a few
> lines due to word wrap.
> FUNCTION
> **/
> CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
> RETURNS DATETIME
> AS
> BEGIN
> DECLARE @.DATELY datetime,
> @.WEEKTY int,
> @.WEEKLY int,
> @.WEEKDAYTY int,
> @.WEEKDAYLY int,
> @.WDSUNDAYLY datetime,
> @.WDSATURDAYLY datetime,
> @.BOY datetime,
> @.BOLY datetime
>
> SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
> SET @.BOY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.datetime)))
> SET @.BOLY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
> SET @.WEEKTY = DATEPART("wk", @.datetime)
> SET @.WEEKLY = DATEPART("wk", @.DATELY)
> BEGIN
> IF @.WEEKTY <> @.WEEKLY
> IF @.WEEKTY > @.WEEKLY
> SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
> ELSE IF @.WEEKTY < @.WEEKLY
> SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
> END
> SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
> SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
> 6),@.BOLY)
>
> BEGIN
> IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
> (@.WEEKTY <> @.WEEKLY)
> BEGIN
> SET @.DATELY = DATEADD("d",7,@.DATELY)
> END
> END
>
> SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
> SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
> BEGIN
> IF @.WEEKDAYTY <> @.WEEKDAYLY
> IF @.WEEKDAYTY > @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
> ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
> @.DATELY)
> END
>
> RETURN @.DATELY
> END
>|||This whole approach will fail when you get to lunar/solar holidays,
like Easter. Want to see the code for Easter after the Y2K
non-leapyear? Want to see the code for Easter before the Y2K
non-leapyear?
The best way is to build a Calendar table with a coumn for "equivalent
business day from last year" values. Build a decade or two with a
spreadsheet after talking to the accounting department.|||Not sure who to respond to on this, so I will respond to my original
post and hopefully it will get to everyone.
I figured out what to do, and might I add, it is way simpler than I had
ever imagined. Kinda makes me wanna kick myself in butt.
DATEADD("wk",-52,@.DATE)
Works like a champ and hasn't failed. Always gets me day for day of a
comparable look last year.|||Brian,
One thing you will want to take a look at is what happens in the last two
days of the year. Dec 31st will return Jan 1st of the current year in a
normal year, and Jan 2nd in a leap year. Dec 30th will return Jan 1st
during leap year.
Also, no matter what day it is you will never get back Jan 1st of the
previous year. Following a leap year you wont be able to get back Jan 2nd
either.
This may be perfectly acceptable for your business need. It is something
that needs to be understood before using this approach, however.
Jusat
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1137162876.033640.264260@.z14g2000cwz.googlegroups.com...
> Not sure who to respond to on this, so I will respond to my original
> post and hopefully it will get to everyone.
> I figured out what to do, and might I add, it is way simpler than I had
> ever imagined. Kinda makes me wanna kick myself in butt.
> DATEADD("wk",-52,@.DATE)
> Works like a champ and hasn't failed. Always gets me day for day of a
> comparable look last year.
>

Last two dates in T-SQL?

I have table which have a day-to-day information. How to use sql to
retrive the last two days information? Thanks.SELECT *
FROM MyTable
WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<gouqizi.lvcha@.gmail.com> wrote in message
news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>I have table which have a day-to-day information. How to use sql to
> retrive the last two days information? Thanks.
>|||Ouch.
That will result in a table scan. Never put the column inside the function.
Always evaluate the column against a constant.
Try this
SELECT *
FROM MyTable
WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
That will be for the last 48 hours. If you want to have since the second
midnight previous, that is a different query.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
> SELECT *
> FROM MyTable
> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
> <gouqizi.lvcha@.gmail.com> wrote in message
> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>|||Daniel
As Geoff indicates, you should not put the column inside a function.
However, the statement that the column must be evaluated against a constant
is a little too strict. The column should be all by itself, but can be
compared to an expression, including one containing the dateadd function, as
Geoff uses in his example.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
> Ouch.
> That will result in a table scan. Never put the column inside the
> function. Always evaluate the column against a constant.
>
> Try this
> SELECT *
> FROM MyTable
> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
> That will be for the last 48 hours. If you want to have since the second
> midnight previous, that is a different query.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>|||Geoff, Kalen,
Thanks for the tip on functions doing table scans with column arguments. Is
that something I overlooked in Books Online, or is that one of those things
one learns only in situations like this? Though as I think about it now, it
would have to apply the function to every value in the column to evaluate
the expression. Furthermore, Geoff's WHERE clause will improve performance
only if there is an index on MyDateColumn; without the index a scan would
still be requried.
For the previous two calendar days:
declare @.midnight datetime;
set @.midnight = convert(datetime, floor(convert(float, getdate())))
SELECT *
FROM MyTable
WHERE MyDateColumn BETWEEN DATEADD(day, -2, @.midnight) AND
DATEADD(millisecond, @.midnight, -3)
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Daniel
> As Geoff indicates, you should not put the column inside a function.
> However, the statement that the column must be evaluated against a
> constant is a little too strict. The column should be all by itself, but
> can be compared to an expression, including one containing the dateadd
> function, as Geoff uses in his example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the second
>> midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>|||Daniel
It may be buried in the Books Online somewhere, but any book or article on
query tuning will mention that the optimizer will never decide to do an
index seek unless the column with an index is all by itself in a WHERE
clause.
And of course, you're completely right, that if there is no index on
MyDateColumn, it's a moot point.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uWx6KMM9HHA.1184@.TK2MSFTNGP04.phx.gbl...
> Geoff, Kalen,
> Thanks for the tip on functions doing table scans with column arguments.
> Is that something I overlooked in Books Online, or is that one of those
> things one learns only in situations like this? Though as I think about
> it now, it would have to apply the function to every value in the column
> to evaluate the expression. Furthermore, Geoff's WHERE clause will
> improve performance only if there is an index on MyDateColumn; without the
> index a scan would still be requried.
> For the previous two calendar days:
> declare @.midnight datetime;
> set @.midnight = convert(datetime, floor(convert(float, getdate())))
> SELECT *
> FROM MyTable
> WHERE MyDateColumn BETWEEN DATEADD(day, -2, @.midnight) AND
> DATEADD(millisecond, @.midnight, -3)
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> Daniel
>> As Geoff indicates, you should not put the column inside a function.
>> However, the statement that the column must be evaluated against a
>> constant is a little too strict. The column should be all by itself, but
>> can be compared to an expression, including one containing the dateadd
>> function, as Geoff uses in his example.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the
>> second midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>>
>|||<gouqizi.lvcha@.gmail.com> wrote in message
news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>I have table which have a day-to-day information. How to use sql to
> retrive the last two days information? Thanks.
>
"Last two" meaning latest two days?
SELECT ...
FROM tbl
WHERE dt IN
(SELECT DISTINCT TOP 2 dt
FROM tbl
ORDER BY dt DESC);
Or latest two rows?
SELECT TOP 2 ...
FROM tbl
ORDER BY dt DESC;
Or yesterday and today?
SELECT ...
FROM tbl
WHERE dt >= '20070910' AND dt < '20070912';
--
David Portas|||David,
Your first query works only if date-only timestamps are stored in dt. If dt
has transactional timestamps with time, then you'll just get the two most
recent rows.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3JadnUmUnONrgXrbnZ2dnUVZ8tWnnZ2d@.giganews.com...
> <gouqizi.lvcha@.gmail.com> wrote in message
> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
> "Last two" meaning latest two days?
> SELECT ...
> FROM tbl
> WHERE dt IN
> (SELECT DISTINCT TOP 2 dt
> FROM tbl
> ORDER BY dt DESC);
> Or latest two rows?
> SELECT TOP 2 ...
> FROM tbl
> ORDER BY dt DESC;
> Or yesterday and today?
> SELECT ...
> FROM tbl
> WHERE dt >= '20070910' AND dt < '20070912';
> --
> David Portas
>|||Kalen is correct, as usual. The Dateadd(day,GETDATE()-,-2) expression is
evaluated once for the entire query rather than once per row. This means
that the comparison for the row match is an index entry (assuming the table
is properly indexed for this query) compared to a constant. If the column
is inside a function, ALL rows (or index entries) must be readm, run through
the function, and compared.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Daniel
> As Geoff indicates, you should not put the column inside a function.
> However, the statement that the column must be evaluated against a
> constant is a little too strict. The column should be all by itself, but
> can be compared to an expression, including one containing the dateadd
> function, as Geoff uses in his example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the second
>> midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>|||"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:ObC5QoM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> David,
> Your first query works only if date-only timestamps are stored in dt. If
> dt has transactional timestamps with time, then you'll just get the two
> most recent rows.
>
Good catch Daniel. Thanks. In SQL Server 2005 the DENSE_RANK function can
solve that one nicely.
Quota queries are much harder than they seem. You can usually bet that when
someone says "Top N of X" they haven't considered all the possible meanings
that phrase could have.
CREATE TABLE tbl (i INT PRIMARY KEY, dt DATETIME NOT NULL);
INSERT INTO tbl VALUES (1,'2000-01-01T15:00:00');
INSERT INTO tbl VALUES (2,'2000-01-01T15:00:00');
INSERT INTO tbl VALUES (3,'2000-01-01T16:00:00');
INSERT INTO tbl VALUES (4,'2000-01-02T20:00:00');
INSERT INTO tbl VALUES (5,'2000-01-02T21:00:00');
INSERT INTO tbl VALUES (6,'2000-01-03T22:00:00');
INSERT INTO tbl VALUES (7,'2000-01-03T23:00:00');
SELECT i,dt
FROM
(SELECT i,dt, DENSE_RANK()
OVER (ORDER BY DATEDIFF(DAY,'20000101',dt) DESC) AS rnk
FROM tbl) t
WHERE rnk <=2;
i dt
-- --
6 2000-01-03 22:00:00.000
7 2000-01-03 23:00:00.000
4 2000-01-02 20:00:00.000
5 2000-01-02 21:00:00.000
(4 row(s) affected)
--
David Portas|||On Tue, 11 Sep 2007 15:11:25 -0700, Kalen Delaney wrote:
>Daniel
>As Geoff indicates, you should not put the column inside a function.
>However, the statement that the column must be evaluated against a constant
>is a little too strict. The column should be all by itself, but can be
>compared to an expression, including one containing the dateadd function, as
>Geoff uses in his example.
... just as long as said expression does not include any columns from
the same table as the indexed column.
</nitpick>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

last day of the month

Hi all,
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))

> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>

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