SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 dateadd for calc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 03/20/2013 :  08:43:20  Show Profile  Reply with Quote
Hi

Trying to calculate the correct days using dateadd but the days are not working out as expected, for example yesterday is showing as 0 even though it should be 1 and the 18th is showing as 1 as oppose to 2 days?

Can anyone help with this one?

Thanks
You can see here the days calc
http://www.pcfixerman.com/index.php/screenshot

Ok I changed the code to this:
=Sum(IIF(Fields!DateDiff.Value =0,1,0 and Fields!Agreed_Solved_Date___Time.Value = Today())) but it still counts 0 days for yesterday even though the AND statement should cancel that out as the solved date should be today?

I dont get it?

SZ1
Learning and development is the driving force in the universe...!

Edited by - sz1 on 03/20/2013 09:22:51

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/20/2013 :  09:25:04  Show Profile  Reply with Quote
--Check this example...
DECLARE @table TABLE (dateCol DATETIME2)
INSERT INTO @table VALUES
('2013-03-19 00:00:00.0000000'),
('2013-03-18 00:00:00.0000000'),
('2013-03-17 00:00:00.0000000'),
('2013-01-03 00:00:00.0000000'),
('2012-05-21 00:00:00.0000000'),
('1997-06-25 00:00:00.0000000')
SELECT DATEDIFF(DD, datecol, GETDATE())
FROM @table

Show us the logic.....

--
Chandu
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 03/20/2013 :  09:35:14  Show Profile  Reply with Quote
Im using preset tables in the report builder and have added a datediff column of
=datediff("d",Fields!Agreed_Solved_Date___Time.Value,Today())
this works out my days from the AgreedSolvedDate filed to today, so for yesterday I would expect to see a 1 in the column where is is yesterdays date 19th no a 0, that should be just today, are you saying I need to write a script for all this to work correctly?
Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 03/20/2013 :  10:21:05  Show Profile  Reply with Quote
I'm trying to do something like this, need CASE to work out the totals for each:

select distinct [ID],c.DESC_SHORT,c.STATUS_NAME,
c.OCCURED_DT AS "Open Date",
DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
Convert(Varchar, c.AGREED_SOLVED_DT,100) AS 'SolvedDate',
DATEDIFF(D,c.AGREED_SOLVED_DT,GETDATE()) AS "Days From Solved",
/*AVG(DATEDIFF(d, c.OCCURED_DT, GETDATE())) AS "Day Average",*/
c.ASSIGNED_GRP_NAME,
c.ASSIGNED_REP_NAME, c.PRIORITY_NAME,
TYPE, [SYMPTOM], [CONTACT_FIRST_NAME] + ' ' + [CONTACT_LAST_NAME] AS "Contact Name",
INTI_CATEGORY,
Case
When 'Days From Solved' = 0 Then Sum(*) AS Today
When 'Days From Solved' = -1 Then Sum(*)AS Tomorrow
When 'Days From Solved' >= 1 Then Sum(*) AS Breached
End

from DIM_CALL c
where c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
AND TYPE ='Incident'
AND STATUS_NAME Not In ('Closed','Resolved','Problem')
--and c.ASSIGNED_REP_NAME in (@User)
Group By
[ID],
c.DESC_SHORT,
c.STATUS_NAME,
c.OCCURED_DT,
c.AGREED_SOLVED_DT,
c.ASSIGNED_GRP_NAME,
c.ASSIGNED_REP_NAME,
c.PRIORITY_NAME,
TYPE,
SYMPTOM,
CONTACT_FIRST_NAME,
CONTACT_LAST_NAME,
INTI_CATEGORY

Order by "Days From Solved" DESC, OCCURED_DT

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
457 Posts

Posted - 03/20/2013 :  11:37:19  Show Profile  Reply with Quote
Sorted with last statement...made new datasource with that code.
Just one thng why is the GETDATE()) mor powerful/accurate than the TODAY()) function?
Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000