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 )asbegin 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 returnend **********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 OrderByColfrom orders owhere -- 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=@StoreIDgroup by dateadd(day,datediff(day,0,o.Datecompleted),0) with rolluporder 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 lineo.StoreID=@StoreIDI 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. |
 |
|
|
|
|