Solved

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

Posted on 2009-05-20
7
210 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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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