dateadd for calc - 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 2008 Forums

Analysis Server and Reporting Services (2008)

dateadd for calc

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-20 : 08:43:20

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...!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 09:25:04

--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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-20 : 09:35:14

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...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-20 : 10:21:05

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...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-20 : 11:37:19

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...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources