Solved

How a query to list out the function result and group by day?

Posted on 2009-05-20
7
207 Views
Last Modified: 2012-05-07
If I select dbo.Func_Daily_Turnover('2009-05-20','2009-05-21') it will return today turnover.

If I use

select t.trandatetime, dbo.Func_Daily_Turnover(t.trandatetime,t.trandatetime) turnover from account a
inner join alltransaction t on a.id = t.accountid
where a.id > 0 group by t.trandatetime order by t.trandatetime

turnover shows all null and the trandatetime has duplicated
2009-05-11 16:07:30.607
2009-05-11 16:07:51.043
2009-05-12 07:28:06.090
2009-05-12 07:29:26.590
2009-05-12 07:30:11.577
2009-05-12 07:49:12.653
2009-05-12 14:04:05.000
2009-05-14 12:59:35.340
2009-05-14 13:00:39.373

If I use
select t.trandatetime, dbo.Func_Daily_Turnover('2009-05-20','2009-05-21') turnover from account a
inner join alltransaction t on a.id = t.accountid
where a.id > 0 group by t.trandatetime order by t.trandatetime

turnover shows all same amount and trandatetime not unique by date

2009-05-11 16:07:30.607      19947.4700
2009-05-11 16:07:51.043      19947.4700
2009-05-12 07:28:06.090      19947.4700
2009-05-12 07:29:26.590      19947.4700
2009-05-12 07:30:11.577      19947.4700
2009-05-12 07:49:12.653      19947.4700
2009-05-12 14:04:05.000      19947.4700
2009-05-14 12:59:35.340      19947.4700
2009-05-14 13:00:39.373      19947.4700

How should I edit the query to list turnover day by day?

ALTER FUNCTION [dbo].[Func_Daily_Turnover] 
(
@trandatetime datetime,
@addoneday datetime
)
Returns decimal(18,4)
AS
BEGIN
Declare @return decimal(18,4)
Declare @transactiontypeid int
Declare @sumcountervalue decimal(18,6)
 
select @return = 0  
 
select
@sumcountervalue = sum(countervalue), 
@return = sum(case when transactiontypeid in (1, 2) then - countervalue 
when transactiontypeid in (3, 4, 20) then countervalue else 0 end)
from alltransaction where trandatetime > @trandatetime and trandatetime <= @addoneday
 
RETURN @return
END

Open in new window

0
Comment
Question by:techques
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 24433004

SELECT DISTINCT CONVERT(varchar(10),  t.trandatetime, 102) trDate, 
dbo.Func_Daily_Turnover(CAST( CONVERT(varchar(10),  t.trandatetime, 102) AS smalldatetime), DATEADD(day,1,CAST( CONVERT(varchar(10),  t.trandatetime, 102) AS smalldatetime))) turnover 
FROM alltransaction t 

Open in new window

0
 
LVL 42

Accepted Solution

by:
pcelba earned 50 total points
ID: 24433069
This one could be faster:
SELECT trDate, dbo.Func_Daily_Turnover(trDate, DATEADD(day,1,trDate)) turnover 
  FROM (SELECT DISTINCT CAST(CONVERT(varchar(10), t.trandatetime, 102) AS smalldatetime) trDate
         FROM alltransaction t) distDate

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 24433119
I you pass two dates as parameters to Func_Daily_Turnover then it should be updated to be more accurate:

Instead of
from alltransaction where trandatetime > @trandatetime and trandatetime <= @addoneday

it should be
from alltransaction where trandatetime >= @trandatetime and trandatetime < @addoneday

The best way would be just one parameter because if it is DAILY Turnover then it should calculate just for one day.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:techques
ID: 24438009
If I use the following SP, it will show all months turnover result. If I just want to view April and I pass '04' to it. How should I edit the SP to accept the '04' where condition?

Do you mean I should pass today, e.g. 2005-05-21 to the SP and use DATEADD(day,1,trDate) ?
e.g.
@trandatetime datetime
where trandatetime >= @trandatetime and trandatetime < DATEADD(day,1,@trandatetime )

SELECT trDate, dbo.Func_Daily_Turnover(trDate, DATEADD(day,1,trDate)) turnover 
FROM (SELECT DISTINCT CAST(CONVERT(varchar(10), t.trandatetime, 102) AS smalldatetime) trDate 
FROM alltransaction t) distDate

Open in new window

0
 

Author Comment

by:techques
ID: 24439284
I tried to add

where year(trandate) = '2009' and month(trandate) = '05'

in the end of your query and it seems work. Do you think it is ok or not?
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24439393
All your above suggestions are correct. You should add the Year and Month restriction to the inner most subquery (in parenthesis) to avoid useless calls to Func_Daily_Turnover
0
 

Author Closing Comment

by:techques
ID: 31583554
Great help! Thanks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard 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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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