Solved

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

Posted on 2009-05-20
7
186 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
  • 4
  • 3
7 Comments
 
LVL 41

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 41

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 41

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 41

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

861 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now