Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-20
7
Medium Priority
?
216 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 43

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 43

Accepted Solution

by:
pcelba earned 200 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 43

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 43

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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