Hi to all
i have a table which in which date is storing in three separate fields
all datatype char. Values storing are like
01 in day field Mar in month field and 2005 in year fields
. now when i try to get data between two dates results are not coming as
expected.
i m using following query
SELECT a.station_desc
,sum(b.ttl_appl_visited) Total_Token_Booked
,sum(b.ttl_urgent_tokens_today) Urgent_Token
FROM tbl_Value b
left JOIN dbo.Daily_Report_Station ON
dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
where report_day between '15' and '07' and report_month between 'Feb'
and 'Mar'
and report_year between '2006' and '2006'
group by a.station_desc
if i give report day value like report_day between '10' and '20' it
returns me correct value but when first value is greater no result comes
as in the query
Regards,
Farid
*** Sent via Developersdex http://www.examnotes.net ***For a start something like
where report_year = '2006'
and (( report_month = 2 and report_day >= 15 ) or
( report_month = 3 and report_day < 7 ))
If you're stuck with the design you currently have, write a UDF that takes
year, month and day as arguments and returns a datetime, then use
between dbo.MyGetDate( '2006', 'Feb', '15' ) and dbo.MyGetDate( '2006',
'Mar','07')
"Ghulam Farid" wrote:
>
> Hi to all
> i have a table which in which date is storing in three separate fields
> all datatype char. Values storing are like
> 01 in day field Mar in month field and 2005 in year fields
> . now when i try to get data between two dates results are not coming as
> expected.
> i m using following query
> SELECT a.station_desc
> ,sum(b.ttl_appl_visited) Total_Token_Booked
> ,sum(b.ttl_urgent_tokens_today) Urgent_Token
> FROM tbl_Value b
> left JOIN dbo.Daily_Report_Station ON
> dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
> where report_day between '15' and '07' and report_month between 'Feb'
> and 'Mar'
> and report_year between '2006' and '2006'
> group by a.station_desc
>
> if i give report day value like report_day between '10' and '20' it
> returns me correct value but when first value is greater no result comes
> as in the query
> Regards,
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
I'd say the problem is how you use between clause.
Between translates into pair of statements >= and <=
So first part of your condition would be
where report_day >= 15 and report_day <= 7.
This condition returns false, hence all following conditions are not parsed
afaik.
try this example
select 'test' where 2 between 2 and 4
select 'test' where 3 between 4 and 2
Also, do you want data between 07.02.2006 and 15.03.2006 or between 07 and
15 day of Feb and Mar in 2006?
HTH
Peter|||yes u r right first condition is making result false so wht would b the
apropriate condition using same data structure. and its true i want
result between 07-02-2005 and 15-03-2005 but result is not coming. any
help
*** Sent via Developersdex http://www.examnotes.net ***|||You'll have to convert the months to integers. Might be a good use of a
computed column or view with a case report_month when 'Jan' then 1 etc.
Then if you want between 15 Feb and 07 Apr, after converting the months to
integers you would write something like:
where (
( month = 2 and day >= 15) or /* handle February 15-28 */
( month > 2 and month < 4 ) or /* Mar or any other months inbetween */
(month = 4 and day <= 7 ) /* handle final month Apr */
)
"Ghulam Farid" wrote:
> yes u r right first condition is making result false so wht would b the
> apropriate condition using same data structure. and its true i want
> result between 07-02-2005 and 15-03-2005 but result is not coming. any
> help
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Stop doing this and use a DATETIME data type. One of your problems is
that you are mimicking a Cobol record, which has fields for the date
components. If you understood the concept of a column -- which is
nohting like a field -- you would not make this mistake.
sql
