| Author |
Topic |
|
Muri
Starting Member
3 Posts |
 Posted - 2008-02-05 : 10:28:42
|
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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-05 : 11:00:23
|
See the error in this function logic when the dates are equal?
while @start < @end
You're making things too complex; you don't need the date table.
select
case when dateadd(day,datediff(day,0,o.Datecompleted),0) is null then 'Grand Total'
else
convert(varchar(15),dateadd(day,datediff(day,0,o.Datecompleted),0),101) end as Date,
count(o.OrderID ) AS TotalOrders,
isnull(round(sum(o.SubTotal),2),0) AS Amount ,
1 as OrderByCol
from
orders o
where
-- Greater than or equal to Start date
o.Datecompleted >= dateadd(day,datediff(day,0,@start),0) and
-- Less than day after end date
o.Datecompleted < dateadd(day,datediff(day,0,@end)+1,0) and
o.StoreID=@StoreID
group by
dateadd(day,datediff(day,0,o.Datecompleted),0)
with rollup
order by
case when dateadd(day,datediff(day,0,o.Datecompleted),0) is null then 1 else 0 end,
dateadd(day,datediff(day,0,o.Datecompleted),0)
CODO ERGO SUM |
 |
|
|
Muri
Starting Member
3 Posts |
Posted - 2008-02-05 : 11:27:23
|
I did try the SQL which you posted but its throwing me errors....
Error1:
Column 'orders.DateCompleted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So hope you understood how I need my output...
I need three clauses to be embedded :
1.)If 2 dates are on same day,then Dtae,Totalorders,amount should come up.
2.)If 2 dates are different -then group based on one day(which happened in my earlier query)
3.)If 2 dates are from different months then group by month.
I appreciate if you can help me fix this !
Thanks!
|
|
|
|
Muri
Starting Member
3 Posts |
Posted - 2008-02-05 : 11:33:19
|
Hey,
If I put the Start and End Dates different without including this line
o.StoreID=@StoreID
I am getting the output which I got earlier with my query and function.
But when I put both the dates same the fields are blank not pulling any records....
Just want to tell you more clearly the errors so that we cna find a work around...
Thanks,
Muri.
|
|
|
|
|
|
|
SQL server2005 query +Date function