Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 SQL server2005 query +Date function

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
Go to Top of Page

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!

Go to Top of Page

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.

Go to Top of Page
   

- Advertisement -