SQL server2005 query +Date function

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources