Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Views -between operator in where clause Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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
Posted Wednesday, December 5, 2012 10:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 1,930, Visits: 3,155
Hi, what's the datatype of the Week field?
Thanks
Post #1393106
Posted Wednesday, December 5, 2012 11:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 42,747, Visits: 35,835
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
Posted Thursday, December 6, 2012 2:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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
Posted Thursday, December 6, 2012 11:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:03 PM
Points: 1,945, Visits: 2,883
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 »

Add to briefcase

Permissions Expand / Collapse