Migrating to SQL 2005: date

Showing posts with label date. Show all posts

Monday, March 26, 2012

Minutes since midnight

I would like to know the number of minutes past midnight for the
system date. Is that possible?
-JohnYes, Dates are stored internally as decimal numbers where the fractional
part is just that, the time since midnight, so cast the datetime value to a
float, and extract the fractional portion , and multiply that times the
number of minutes in a day...
Select (Cast (getdate() as float) - Cast(getdate() as Int)) * 24 * 60
"John Baima" wrote:

> I would like to know the number of minutes past midnight for the
> system date. Is that possible?
> -John
>|||Thanks, that's interesting. How about
Select (Cast (getdate() as float) - round(Cast(getdate() as float), 0,
1)) * 24 * 60
instead? Yours is giving me a negative number.
-John
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote:
>Yes, Dates are stored internally as decimal numbers where the fractional
>part is just that, the time since midnight, so cast the datetime value to
a
>float, and extract the fractional portion , and multiply that times the
>number of minutes in a day...
>Select (Cast (getdate() as float) - Cast(getdate() as Int)) * 24 * 60
>"John Baima" wrote:
>|||Round will round "UP" if it's after noon...
"John Baima" wrote:

> Thanks, that's interesting. How about
> Select (Cast (getdate() as float) - round(Cast(getdate() as float), 0,
> 1)) * 24 * 60
> instead? Yours is giving me a negative number.
> -John
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote:
>
>|||try this:
select datediff(mi,convert(varchar(8),getdate()
,112),getdate())
dean
"John Baima" <john@.nospam.com> wrote in message
news:tsg5511f573eo7rbm31mavp76h78l7li95@.
4ax.com...
>I would like to know the number of minutes past midnight for the
> system date. Is that possible?
> -John|||Try,
select datediff(minute, convert(char(8), getdate(), 112), getdate())
go
AMB
"John Baima" wrote:

> I would like to know the number of minutes past midnight for the
> system date. Is that possible?
> -John
>|||I guess the Cast(Dattime as Int) is also rounding UP... The floor function
always gets the next lowest Integer...
Select (Cast (getDate() as float) - Floor(Cast(getdate() as float))) * 24 *
60
It's only 10:30 out here on the left coast...
"John Baima" wrote:

> Thanks, that's interesting. How about
> Select (Cast (getdate() as float) - round(Cast(getdate() as float), 0,
> 1)) * 24 * 60
> instead? Yours is giving me a negative number.
> -John
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote:
>
>sql

MINUS operation between table date ranges, is my algorithm sound?

Hi everyone, i was hoping someone could help me.
i am looking to implement a MINUS operation between two tables which contain
date ranges.
table a:
[start] [finish] [group]
1 10 0
18 19 1
23 26 2
28 31 3
table b:
[start] [finish]
4 5
18 18
25 28
Result of table a - table b:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
Would anyone know how to implement this? I have done this by firstly
performing an AND operation between the two tables:
table a AND table b = table c (here the grouping colums tells me which group
from table a the date range is associated with
[start] [finish] [group]
4 5 0
18 18 1
25 26 2
28 28 3
Then I throw table a and c into another table (d). Here I assign:
start date of table a: status = 1
end date of table a: status = 0
start date of table b: status = 0
end date of table b: status = 1
start of date range in table a: boundary = 0
end of date range for table a: boundary = 2
boundary = 1 for all data coming from table b
table d:
[date] [status] [group] [boundary]
1 1 0 1
10 0 0 3
18 1 1 1
19 0 1 3
23 1 2 1
26 0 2 3
28 1 3 1
31 0 3 3
4 0 0 2
5 1 0 2
18 0 1 2
18 1 1 2
25 0 2 2
26 1 2 2
28 0 3 2
28 1 3 2
I then sort using the SQL statement: order by 3,1,2 to get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
4 0 0 2
5 1 0 2
10 0 0 3
18 1 1 1
18 0 1 2
18 1 1 2
19 0 1 3
23 1 2 1
25 0 2 2
26 1 2 2
26 0 2 3
28 1 3 1
28 0 3 2
28 1 3 2
31 0 3 3
I then update the table according to:
case
when boundary = 2 and status = 0 then data = date -1
when boundary = 2 and status = 1 then data = date +1
end
and I get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
18 1 1 1
17 0 1 2
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
27 1 2 2
26 0 2 3
28 1 3 1
27 0 3 2
29 1 3 2
31 0 3 3
I then filter the table and look for adjacient row pairs where row(i)=1 and
row(i+1)=0 :
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
18 1 1 1
17 0 1 2
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
27 1 2 2
26 0 2 3
28 1 3 1
27 0 3 2
29 1 3 2
31 0 3 3
I delete those pairs where (date(i) < date(i+1) and boundary(i) >
boundary(i+1) to get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
29 1 3 2
31 0 3 3
This then gives me the resultant table im looking for:
table d recast:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
Is my approach sound? I am yet to encode the above in SQL. Maybe there is a
faster approach to getting this result?
Any help most appreciated!
cheers, peter"peter walker" <pwalker@.nospam.com> wrote in message
news:ucoD$XLGGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, i was hoping someone could help me.
> i am looking to implement a MINUS operation between two tables which
> contain date ranges.
> table a:
> [start] [finish] [group]
> 1 10 0
> 18 19 1
> 23 26 2
> 28 31 3
>
> table b:
> [start] [finish]
> 4 5
> 18 18
> 25 28
> Result of table a - table b:
> [start] [finish]
> 1 3
> 6 10
> 19 19
> 23 24
> 29 31
> Would anyone know how to implement this? I have done this by firstly
Read my signature to see how much information you left out.
Let's assume your tables look like this:
CREATE TABLE a (start INTEGER NOT NULL PRIMARY KEY /* ' Was not specified
*/, finish INTEGER NOT NULL, CHECK (start<=finish) /* ? Was not specified
*/, grp INTEGER NOT NULL /* "GROUP" is a reserved word - not a good column
name */);
CREATE TABLE b (start INTEGER NOT NULL PRIMARY KEY /* ' */, finish INTEGER
NOT NULL, CHECK (start<=finish));
Your sample data:
INSERT INTO a (start,finish,grp)
SELECT 1, 10, 0 UNION ALL
SELECT 18, 19, 1 UNION ALL
SELECT 23, 26, 2 UNION ALL
SELECT 28, 31, 3;
INSERT INTO b (start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;
I'll also assume you have a table of numbers - all integers from 0 to some
arbitrarily large number. One way to get the missing numbers would be like
this:
SELECT n.num
FROM numbers AS n
LEFT JOIN b
ON n.num BETWEEN b.start AND b.finish
WHERE b.start IS NULL
AND n.num BETWEEN
(SELECT MIN(start)
FROM a) AND
(SELECT MAX(finish)
FROM a);
I don't quite understand the significance of the "group" column here. Please
give us a better spec if you need more help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi, thanks for the post. Im the original poster of this topic (at work
using a friend's account).
Please disregard the [group] column in my initial description of the
problem. I used a [group] column within my algorithm. In effect I would
like to know the best way to implement a subtraction of date ranges as
follows:
table a:
[start] [finish]
1 10
18 19
23 26
28 31
table b:
[start] [finish]
4 5
18 18
25 28
Result of table a - table b:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
in effect, if a date range in table_b intersects a date range in
table_a, then that data in the intersection is removed from the date
range in table_a
for example, the following are example cases where we subtract from a
range in table_a where there are ranges in table_b which intersect with
that range in table_a:
1. {23....37} - {25...29} = {23...24}
2. {23....37} - {26...32} = {23...25}, {33...37}
3. {23....37} - [ {25...27} , {31...33} ] = {23...24}, {28...30},
{34...37}
Any help on this would be great!
Many thanks.
peter|||Peter,
this is very ugly :)
but the SELECT statement should work
i use a table of natural numbers proposed by David
David, pardon me for the use of your idea :)
SELECT MIN(U.seq) as start, MAX(U.seq) as finish
FROM (SELECT G.seq, G.seq - COUNT(*)
FROM (SELECT GS.seq
FROM TableA AS GA, Sequence AS GS
WHERE GS.seq BETWEEN GA.start AND GA.finish
AND NOT EXISTS(SELECT *
FROM TableB AS GB, Sequence AS GSS
WHERE GSS.seq BETWEEN GB.start AND
GB.finish
AND GSS.seq = GS.seq)) AS G,
(SELECT LS.seq
FROM TableA AS LA, Sequence AS LS
WHERE LS.seq BETWEEN LA.start AND LA.finish
AND NOT EXISTS(SELECT *
FROM TableB AS LB, Sequence AS LSS
WHERE LSS.seq BETWEEN LB.start AND
LB.finish
AND LSS.seq = LS.seq)) AS L
WHERE L.seq <= G.seq
GROUP BY G.seq) AS U(seq, gb)
GROUP BY U.gb;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||On Sat, 14 Jan 2006 12:55:36 +1000, peter walker wrote:

>Hi everyone, i was hoping someone could help me.
(snip)
Hi Peter,
I just posted a reply to your first thread about this issue.
Hugo Kornelis, SQL Server MVP|||Hello There,
I hope this might solve your problem.
Create Table TableA
(
[start] int,
[finish] int,
[group] int
)
Go
Insert into TableA
Select 1 ,10 ,0
Union All
Select 18, 19 ,1
Union All
Select 23, 26 ,2
Union All
Select 28 ,31 ,3
Go
Create Table TableB
(
[start] int,
[finish] int
)
Go
Insert into TableB
Select 4 ,5
Union All
Select 18 ,18
Union All
Select 25 ,28
Go
Create View vwTmpData
As
Select * From (
Select 1 N
Union All
Select 2
Union All
Select 3
Union All
Select 4
Union All
Select 5
Union All
Select 6
Union All
Select 7
Union All
Select 8
Union All
Select 9
Union All
Select 10
Union All
Select 11
Union All
Select 12
Union All
Select 13
Union All
Select 14
Union All
Select 15
Union All
Select 16
Union All
Select 17
Union All
Select 18
Union All
Select 19
Union All
Select 20
Union All
Select 21
Union All
Select 22
Union All
Select 23
Union All
Select 24
Union All
Select 25
Union All
Select 26
Union All
Select 27
Union All
Select 28
Union All
Select 29
Union All
Select 30
Union All
Select 31
Union All
Select 32
Union All
Select 33
) Seq Inner Join TableA T1 On N Between T1.start and T1.finish
Where N Not In (Select Start From tableB Union Select Finish From
TableB)
Go
Select identity(int,1,1) N1,* into tmpData From vwTmpData
Update tmpData Set [group] = [group] + 1
Where N - N1> 0
Select Min(N) Start,Max(N) Finish From tmpData Group by [group]
Drop Table tmpData
Drop View vwTmpData
Drop Table TableA
Drop Table TableB
With Warm regards
Jatinder Singh

Friday, March 23, 2012

minimum price on earliest date

Ok my first posted question :
(This is related to a travel website)

I have the following table layout :

CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;

The package table contains a list (a very large one) for holiday accomodation packages.

What i'm trying to get is the following :

The MINIMUM price using the following for "price" :

CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))

AND

The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.

Any thoughts?

With any luck I will be able to give you an test data insert for this.

This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :

SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code

At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :

SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start

In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.

Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me

i would not use the same alias "p" in more than one place in the query

and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.

What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189

They both match the minimum price and date and thus both records are output. DOH!

As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql

Wednesday, March 21, 2012

Minimum Date in DateStamp field is not 1-Jan-100 (Silly by SQLServer guys)

Minimum Date in DateStamp field is not 1-Jan-100. Wouldn't we expect that.
In SQLServer2000 is it "1-Jan-100", if not WHY ??,
In the previous versions it is "01-Jan-1753"
bikramjeetThe problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP|||The problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP

I always found that to be a lame excuse...

As if there aren't enough...what the technical word they use...oh well, kludges in M$ internal code anyway...

If they really wanted to..(and who the hell said ancient dates are precise anyway)...bulid it in tho the damn date functions...why limit it?

Next they'll be telling us you can't travel fatser than the speed of light...

The Church of M$|||I always found that to be a lame excuse...Yeah, but Convert() would be kind of funky if it had arguments for your location, political affiliation, and religion!

Oracle solves this problem by simply ignoring the Gregorian reformation altogether... You simply learn to live on Oracle time!

There are other client programming languages that have library code that detects the PC's locale and uses that to make the decisions (which are frequently incorrect) about date conversions. This can be a real hoot to debug, since the exact same binary running on the exact same release of windoze would change its results based on where it thought the PC was!

That leads to the issue of lunar calendars that have leap months added on a tough to predict basis... That's even more fun!

I don't remember if you were involved in the discussion of database normalization that Rudy invited me to that was the first time I'd posted here... The issue of dates is a lot like the issue of normalization: if you know nothing about it, no problem; if you know a little bit about it, not a big problem; as you learn more than just a little, the problem gets bigger and uglier really fast. The whole thing becomes a slippery slope, so it gets tough to dance!

-PatP

Monday, March 19, 2012

Min(NULL) shows up as 12/30/1899 in cube

Hi!

I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.

I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.

Any help would be greatly appreciated. Thanks!

Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.

In a relational DB 1 + NULL = NULL

In an OLAP DB 1 + NULL = 1

You might have to change your approach, so possible avenues to explore would be

use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||

So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:

iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.

I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.

|||

That would work too.

You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.

|||

I've now created a calculated member as:

IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])

and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).

Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?

Thanks for all your help!

|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.

min(date) + 1

I know you can use the min function to get the first date within a
recordset. However, is there a way to get the second date in a
recordset? In other words, is there a way to get "min(date) + 1" or a
"next-to-earliest date" essentially?Try somehthing like...

select min(a.date) from (select date from table b where b.date > (select
min(c.date) from table c)) a

This should get the next earliest date not necessarily the next min date if
there are duplicate min dates.

<imani_technology_spam@.yahoo.com> wrote in message
news:1127338921.903049.218810@.z14g2000cwz.googlegr oups.com...
>I know you can use the min function to get the first date within a
> recordset. However, is there a way to get the second date in a
> recordset? In other words, is there a way to get "min(date) + 1" or a
> "next-to-earliest date" essentially?|||select top 1 a.date from
(select top 2 date from table order by date ) a
order by a.date desc|||On Wed, 21 Sep 2005 23:21:52 GMT, Danny wrote:

>Try somehthing like...
>select min(a.date) from (select date from table b where b.date > (select
>min(c.date) from table c)) a
>This should get the next earliest date not necessarily the next min date if
>there are duplicate min dates.

Hi Danny,

No need to use a derived table, though. This'll work as well:

SELECT MIN(a.date)
FROM table AS a
WHERE a.date > (SELECT MIN(b.date) FROM b.table))

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

min value for a date

Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <= @.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >= @.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
10026823 100 2008-02-08 00:00:00.000
10026824 150 2008-02-08 00:00:00.000
10026824 150 2008-02-09 00:00:00.000
10026823 100 2008-02-10 00:00:00.000
10026823 100 2008-02-11 00:00:00.000
10026823 100 2008-02-13 00:00:00.000
10026823 100 2008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?|||Hi
it seems that rate_id is not needed in the group by
select min(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
order by A.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
select min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
) b ON b.date = c.date AND b.minrate = s.rate
order by b.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>|||On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
> Hi,
> I have the following query which returns
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.valid_=from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date) >=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> order by
> =A0 =A0 =A0 =A0 A.date, minRate
> rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
Hi,
I have re-written your query a bit. You might try this although
untested.
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and R.room_type_id =3D 72 -- Double room
and R.board_type =3D 6 -- Bed and breakfast
and month(R.valid_from_date) <=3D @.month and
year(R.valid_from_date) <=3D
@.year
and month(R.valid_to_date) >=3D @.month and year(R.valid_to_date)
>=3D
@.year
and r.min_nights =3D 1 -- Standard type
and a.date >=3D getDate() -- we dont go back in
time
group by
r.Rate_id, A.date
having min(r.rate) =3D
(select min(s.rate)
from
hotel_acc_rates S
inner join
hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and S.room_type_id =3D 72 -- Double room
and S.board_type =3D 6 -- Bed and breakfast
and month(S.valid_from_date) <=3D @.month and
year(S.valid_from_date) <=3D
@.year
and month(S.valid_to_date) >=3D @.month and year(S.valid_to_date)
>=3D
@.year
and s.min_nights =3D 1 -- Standard type
and b.date >=3D getDate() -- we dont go back in
time
and R.Rate_id =3D S.Rate_id
and A.date =3D B.date
group by
s.Rate_id, B.date
)
order by
A.date, minRate|||On Feb 11, 2:41=A0pm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
>
>
> > Hi,
> > I have the following query which returns
> > select
> > =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> > from
> > =A0 =A0 =A0 =A0 hotel_acc_rates R
> > inner join
> > =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> > where
> > =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> > =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room=
> > =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and= breakfast
> > =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.vali=d_from_date) <=3D
> > @.year
> > =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_=to_date) >=3D
> > @.year
> > =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standar=d type
> > =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- =we dont go back in time
> > group by
> > =A0 =A0 =A0 =A0 r.Rate_id, A.date
> > order by
> > =A0 =A0 =A0 =A0 A.date, minRate
> > rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> > However I want to exclude row 2 as it is the same day but a higher
> > rate. I am working on an extremley old legacy system which allows lots
> > of different rates for the same day to entered which does not help!.
> > The structure cannot be changed so I am stuck with what I have.
> > How can I rewrite my query without creating a temp table?
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and
> year(R.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> having min(r.rate) =3D
> (select min(s.rate)
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates S
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and S.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and S.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(S.valid_from_date) <=3D @.month and
> year(S.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(S.valid_to_date) >=3D @.month and year(S.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and s.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and b.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> =A0 =A0 =A0 =A0 and R.Rate_id =3D S.Rate_id
> =A0 =A0 =A0 =A0 and A.date =3D B.date
> group by
> =A0 =A0 =A0 =A0 s.Rate_id, B.date
> )
> order by
> =A0 =A0 =A0 =A0 A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
=2E...
and A.date =3D B.date
group by
B.date
)
HTH

Min and Max for Dimension attribute

Hi all,

I want to get the minimum and maximum among the members of a dimension attribute. This attribute is a date. Can anybody give me a sample MDX query for this..

Regards...

Girija Shankar Beuria

Code Snippet

with member [Measures].[Min Date] as

MIN([Date].[Date].[Date].Members,[Date].[Date].CurrentMember.MemberValue)

member [Measures].[Max Date] as

MAX([Date].[Date].[Date].Members,[Date].[Date].CurrentMember.MemberValue)

select

{[Measures].[Min Date],[Measures].[Max Date]} on 0

from [Adventure Works]

;

Saturday, February 25, 2012

Migration ACCESS --> SQL date field

Hi,
I import an access table with date field in SQL with DTS import/export
wizard.
I got error : data overflow on date field. There is another way to do it ?In the DTS package, transform that column to varchar, and then run isdate()
against the new table to determine which rows need to be repaired.
"Sylvain Provencher" <sylvain.provencher@.nobelia.com> wrote in message
news:uZybbSLpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I import an access table with date field in SQL with DTS import/export
> wizard.
> I got error : data overflow on date field. There is another way to do it ?
>

Monday, March 26, 2012

Minutes since midnight

MINUS operation between table date ranges, is my algorithm sound?

Friday, March 23, 2012

minimum price on earliest date

Wednesday, March 21, 2012

Minimum Date in DateStamp field is not 1-Jan-100 (Silly by SQLServer guys)

Monday, March 19, 2012

Min(NULL) shows up as 12/30/1899 in cube

min(date) + 1

min value for a date

min value for a date

Min and Max for Dimension attribute

Saturday, February 25, 2012

Migration ACCESS --> SQL date field

Migrating to SQL 2005

Blog Archive

About Me