Solved

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

Posted on 2009-05-20
7
201 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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