?
Solved

SQL SERVER2005 Query

Posted on 2008-02-04
5
Medium Priority
?
134 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:onebite2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20820682
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
 
LVL 5

Expert Comment

by:ursangel
ID: 20820936
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
 

Author Comment

by:onebite2
ID: 20825382
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20828473
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20829694
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question