June 24, 2011 at 4:45 pm
Here is relevant dat :
CREATE DATABASE HIS;
GO
USE HIS
CREATE TABLE ROOM_TYPES
(
ROOMTYPE_ID INT IDENTITY (1,1)PRIMARY KEY CLUSTERED,
ROOM_TYPE VARCHAR(6) NOT NULL,
ROOM_DESCRIPTION VARCHAR(60)
);
GO
USE HIS
CREATE TABLE ROOM_RATES
(
ROOM_RATE_ID INT IDENTITY (1,1)PRIMARY KEY CLUSTERED,
ROOM_TYPE VARCHAR(6),
RATE_START_DATE DATETIME,
RATE_END_RATE DATETIME,
RATE MONEY
);
GO
USE HIS
INSERT INTO ROOM_TYPES (ROOM_TYPE,ROOM_DESCRIPTION)
VALUES
('DBLM','DVOPOSTELJNA SOBA MORJE'),
('DBLMS','DVOPOSTELJNA SOBA SUPERIOR MORJE'),
('DBL','DVOPOSTELJNA SOBA STANDARD CESTA'),
('DBLS','DVOPOSTELJNA SOBA SUPERIOR CESTA'),
('SGLM','ENOPOSTELJNA SOBA MORJE'),
('SGLMS','ENOPOSTELJNA SOBA SUPERIOR MORJE'),
('DBLMAN','DVOPOSTELJNA SOBA MANSARDA'),
('SUIR','APPARTMAN 124 ROCCO MORJE'),
('SUIR','APPARTMAN 126 RONDOLA MORJE'),
('SUI','002-008 MORJE'),
('VP1','MESTO 28 M'),
('VP2','MESTO 35 M'),
('VP3','MANDRAČ 42 M');
GO
USE HIS
SET DATEFORMAT DMY
INSERT INTO ROOM_RATES (ROOM_TYPE,RATE_START_DATE,RATE_END_RATE,RATE)
VALUES
('DBLMS','3/1/2011','16/4/2011','104'),
('DBLMS','22/10/2011','28/12/2011','104'),
('DBLMS','25/4/2011','25/6/2011','118'),
('DBLMS','24/9/2011','22/10/2011','118'),
('DBLMS','16/4/2011','25/4/2011','122'),
('DBLMS','25/6/2011','30/7/2011','122'),
('DBLMS','20/8/2011','24/9/2011','122'),
('DBLMS','30/7/2011','20/8/2011','132'),
('DBLMS','28/12/2011','2/1/2012','132');
GO
I tried to run this query but it does not work :
use HIS
SELECT
room_type,rate_start_date,rate_end_date,rate,
(DATEDIFF( IF (rate_end_date > '2011-04-16' , '2011-04-16', rate_end_date),
IF ( rate_start_date < '2011-04-20' , '2011-04-20' , rate_start_date )) )
AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND rate_end_date > '2011-04-14'
I get :
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'.
Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
What am I missing here ?
June 24, 2011 at 4:58 pm
There is no IF() function in T-SQL. You're mixing something from another language in there.
SELECT
room_type,rate_start_date,rate_end_date,rate
, DATEDIFF( case when rate_end_date > '2011-04-16'
then '2011-04-16'
else rate_end_date
end,
case when rate_start_date < '2011-04-20'
then '2011-04-20'
else rate_start_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND rate_end_date > '2011-04-14'
June 24, 2011 at 5:18 pm
You're right (it's from mysql)
I tried your version but get :
Msg 174, Level 15, State 1, Line 3
The datediff function requires 3 argument(s).
June 24, 2011 at 5:21 pm
skynet_si (6/24/2011)
You're right (it's from mysql)I tried your version but get :
Msg 174, Level 15, State 1, Line 3
The datediff function requires 3 argument(s).
Yeah, he forgot the dd component.
Use:
DATEDIFF( dd, case
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2011 at 5:27 pm
Doh!
Yep, Datediff needs a type and then the arguments: http://msdn.microsoft.com/en-us/library/ms189794.aspx
The CASE is here: http://msdn.microsoft.com/en-us/library/ms181765.aspx
basically you can use it in the SELECT or the WHERE clauses as
CASE WHEN xx then yyy
WHEN zz then aaa
else bbb
end
June 24, 2011 at 6:04 pm
when I run this :
SELECT
room_type,rate_start_date,rate_end_date,rate
, DATEDIFF(DAY,case when rate_end_date > '2011-04-14'
then '2011-04-14'
else rate_end_date
end,
case when rate_start_date < '2011-04-20'
then '2011-04-20'
else rate_start_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND rate_end_date > '2011-04-14'
I get :
room_typerate_start_daterate_end_dateratedays
DBLMS2011-01-03 00:00:00.0002011-04-16 00:00:00.000104,006
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,006
However,the query should display :
room_typerate_start_daterate_end_dateratedays
DBLMS2011-01-03 00:00:00.0002011-04-16 00:00:00.000104,002
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,004
since I am asking for prices and days from 2011-04-14 to 2011-04-20
?!
June 24, 2011 at 6:05 pm
There's a price change on 2011-04-16 ...
June 24, 2011 at 7:07 pm
This seems to produce the right results:
SELECT
room_type,rate_start_date,rate_end_date,rate
, DATEDIFF(DAY,case when rate_end_date < '2011-04-20'
then '2011-04-14'
else rate_start_date
end,
case when rate_start_date > '2011-04-14'
then '2011-04-20'
else rate_end_date
end
) AS days FROM room_rates
WHERE room_type = 'DBLMS' AND rate_start_date <= '2011-04-20'
AND rate_end_date > '2011-04-14'
result:
room_typerate_start_daterate_end_dateratedays
DBLMS2011-01-03 00:00:00.0002011-04-16 00:00:00.000104,002
DBLMS2011-04-16 00:00:00.0002011-04-25 00:00:00.000122,004
start-end ....I think i had a brain fart...
Is the query Ok ??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply