SQL SERVER2005 Query

I wrote a function and a SQL to get the  3 columns Date,Total Orders & Amount, for dates between Date Started and Date Completed if I pass different Dates in the SQL I get the correct result but if I pass same dates then I don't get the result I am looking for .

For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.
But I give same dates for  Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.

Some how I could not trace what could be the error in my SQL /Function.

I appreciate if I could get some work around for this.

Thanks!


create function dbo.CreateDateList(@start datetime, @end datetime)
returns @t table ( [date] datetime )
as
begin
  if @start is null or @end is null 
    return
 
  if @start > @end 
    return
 
  set @start = convert(datetime, convert(varchar(10), @start, 120), 120)
  set @end = convert(datetime, convert(varchar(10), @end, 120), 120)
 
  while @start < @end
  begin
    insert into @t ( [date] ) values (@start)
    set @start = dateadd(day, 1, @start)
  end
 
  return
end
 
**********SELECT qUERY***********
 
SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol
 FROM dbo.CreateDateList(@DateFrom , @DateTo) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) 
WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101)
 Union 
SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol 
FROM dbo.CreateDateList(@DateFrom , @DateTo) l
 LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
 WHERE StoreID=@StoreID 
Order by Date

Open in new window

onebite2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
ALTER function dbo.CreateDateList(@start datetime, @end datetime)
returns @t table ( [date] datetime )
as
begin
  if @start is null or @end is null
    return
 
  if @start > @end
    return
 
  set @start = convert(datetime, convert(varchar(10), @start, 120), 120)
  set @end = convert(datetime, convert(varchar(10), @end, 120), 120)
 
  while @start <= @end ---------------------------------- modified
  begin
    insert into @t ( [date] ) values (@start)
    set @start = dateadd(day, 1, @start)
  end
 
  return
end
GO
0
ursangelCommented:
I agree with aneesh. The reason is you are converting the start date and end date to varchar(10), so only the date part alone will be taken into consideration.
yyyy-mm-dd
so when both the dates are same, your function falis in the 'IF' condition where it is checking for only
IF STARTDATE < ENDDATE.

Change the code and try it.
0
onebite2Author Commented:
Hey aneeshattinga,
I did try the function which you posted but did not get the output  when 2dates are same...

Can you tell me any other work around for this.

Thanks!:
0
Anthony PerkinsCommented:
onebite2,

Just a reminder you have 40 open question, one over a year old.  Please don't abuse the good will of this community.  

I will be happy to list them if you are having a hard time tracking them.
0
Aneesh RetnakaranDatabase AdministratorCommented:
change this
  and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))


and o.Datecompleted <= dateadd(day,1,convert(Datetime, l.[date],101))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.