giving last date toal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

giving last date toal

Author

Topic

s_a_ravi@yahoo.com
Starting Member

4 Posts

Posted - 2005-01-07 : 04:41:57

hi i want to find out th no of rows available between fromdate and todate
my query is
SELECT cast(count(c.termcode) as numeric) from fbdbsrv.BCBS.dbo.Calls c inner join fbdbsrv.BCBS.dbo.Termcodes tc on c.termcode=tc.termcode where convert(datetime,calldate) between convert(datetime,@frdt) and convert(datetime,DateAdd(d, 1, @tdt)))

but it returnd the total for last date

for ex: from date is 09/01/2004 and todate is 09/07/2004
it returns only for 09/07/2004 and 09/07/2004

any can help
thanks in advancs

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 05:23:20

Put in your character dates as 20040901 and 20040907
Put a style in the calldate convert.
What format is calldate?
You might want to get rid of the time so you can remove the dateadd.
also
convert(datetime,DateAdd(d, 1, @tdt)))
the dateadd must be doing an implicit convert so the explicit convert is not needed.

They might help but I can't think of anything that would show that behaviour.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

s_a_ravi@yahoo.com
Starting Member

4 Posts

Posted - 2005-01-07 : 05:30:22

hi nr,
actually it returns the correct result.
but when i am going to divide with the total
like

x*100.0/(our total-discussing) it is only taking last day value.

do u understand. pls help.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 05:38:02

You mean can I guess what your query looks like and where the bug is?
No.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

s_a_ravi@yahoo.com
Starting Member

4 Posts

Posted - 2005-01-07 : 06:17:43

sure.
i will give u the query

declare @frdt as varchar(30)
declare @tdt as varchar(30)

here i am getting total for selected two dates
set @totMTD = (SELECT cast(count(c.termcode) as numeric) from Calls c inner join Termcodes tc on c.termcode=tc.termcode where convert(varchar(30),c.calldate,101) between convert(varchar(30),@frdt,101) and convert(varchar(30),@tdt,101))

then i am calculating percentage for day(each date) toal with this consolidated total(above) by using following query

SELECT p.[Description],
isnull(COUNT(c.Termcode),0)* 100/(CASE WHEN @totMTD<>0 THEN @totMTD ELSE 1 END AS 'mtdPer'
FROM rptBCBS AS P left outer JOIN fbdbsrv.BCBS.dbo.Calls AS C
ON p.Termcode =c.TermCode
AND convert(varchar(30),P.MonthDates,101) = CONVERT(varchar(30),C.Calldate,101)
GROUP BY p.[Description]

look the exaple

MTD MTD %
12 0.23%--formula 12*100/(12+24+34)
24 45.0%
34 12.89%

can u able to understand?
but the total is available for last date only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources