| Author |
 Topic  |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 03/20/2013 : 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...! |
Edited by - sz1 on 03/20/2013 09:22:51
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 03/20/2013 : 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
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/20/2013 : 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
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/20/2013 : 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
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/20/2013 : 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...! |
|
|
| |
Topic |
|
|
|
dateadd for calc
