Views -between operator in where clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Views -between operator in where clause

Views -between operator in where clause

Rate Topic

Display Mode

Topic Options

Author

Message

var05

Posted Wednesday, December 05, 2012 9:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362

How to specify a where clause in a view with between operator???

I have a view which shows empname and sal data and week

Results displayed by the view are

select empname,sal,week
from view test

EmpName Sal Week
A 100 07/02/2012
A 200 07/02/2011
B 500 07/02/2012
B 1000 07/02/2011
C 200 07/09/2012
C 300 07/23/2011
D 100 07/12/2010

Query 1

select empname,sal,week
from view test
where week='07/02/2012'

Results Displayed:
A 100 07/02/2012
B 500 07/02/2012

Query 2:
select empname,sal,week
from view test
where week='07/09/2012'
Result
C 200 07/09/2012

Query 3 :

select empname,sal,week
from view test
where week between '07/02/2012' and '07/16/2012'

the results to be displayed are:

A 100 07/02/2012
B 500 07/02/2012
C 200 07/09/2012

But it displays all years data for the month 07 between dates 02 and 16..

A 100 07/02/2012
A 200 07/02/2011
B 500 07/02/2012
B 1000 07/02/2011
C 200 07/09/2012
D 100 07/12/2010

any help on this?

Post #1393085

Gazareth

Posted Wednesday, December 05, 2012 10:01 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:53 AM
Points: 1,474, Visits: 2,342

Hi, what's the datatype of the Week field?
Thanks

Post #1393106

GilaMonster

Posted Wednesday, December 05, 2012 11:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

var05 (12/5/2012)

any help on this?

Yeah, don't store dates as varchars, store them as datetime data types and then the comparisons will be date-based, not string based.

I strongly recommend fixing the data types. Any other form of workaround will be slow, painful or both.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1393137

var05

Posted Thursday, December 06, 2012 2:03 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362

Thanks for ur reply..

Week is a calcualted feild from date and I had changed the string to date and it works fine. Thanks!

Post #1393374

CELKO

Posted Thursday, December 06, 2012 11:43 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats (you did not; I will correct it). Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. What you did post did not have weeks in that column! Those were dates.

I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.

You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

There are several websites with calendars you can cut & paste, but you can start your search with: http:www.calendar-365.com/week-number.html

Since you did not give us any DDL, I load this non-table:

CREATE TABLE Foobar –- no key, no DDL, not a table!!
(emp_name CHAR(1) NOT NULL,
salary_amt DECIMAL (8,2) NOT NULL
CHECK (salary_amt > 0.00),
foobar_week CHAR(10) NOT NULL
CHECK ('[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7])
);

INSERT INTO Foobar
VALUES
('A', 100.00, '2012W27'),
('A', 200.00, '2011W27'),
('B', 500.00, '2012W27'),
('B', 1000.00, '2011W27'),
('C', 200.00, '2012W28'),
('C', 300.00, '2011W30'),
('D', 100.00, '2012W28');

YOu can fidn the week from a calendar date with

SELECT SUBSTRING (week_date,1, 7) AS foobar_week
FROM Calendar
WHERE cal_date = @in_foobar_date;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1393680

« Prev Topic | Next Topic »

Permissions