| Author |
 Topic  |
|
Alan Schofield
Starting Member
United Kingdom
23 Posts |
 Posted - 02/27/2013 : 12:50:10
|
| Your welcome mate.. but one again I forgot to add ".Date" in the datediff function, best just to copy that line from your existing function. Too tired, I did a 22hr stint yesterday followed by 2hrs sleep, it's taking it's toll! |
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/04/2013 : 06:21:57
|
Hi Alan,
Thats some shift mate no wonder you were knacked!
Yeah I have this in the code:
Public Function fnGetConditionalColor(pDate as datetime) AS String
Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
IF d<0 THEN
c="Green"
ELSE IF d >=0 and d <=1 THEN
c="Red"
ELSE
c="Blue"
END IF
Return c
End Function
works well now today is red tomorrow is green and everything older than today is blue :)
Is there a quick way to sum the numbers for this so:
sum(=Code.fnGetConditionalColor(Fields!Agreed_Solved_Date___Time.Value "Red"))
so I can add the number of tickets for each color below a colour key text field?
?Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/04/2013 : 06:42:32
|
someone like this even though this wont work:
Public Function fnGetCountColor(pDate as datetime) AS String
Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
IF d<0 THEN
count(c="Green")
ELSE IF d >=0 and d <=1 THEN
count(c="Red")
ELSE
count(c="Blue")
END IF
Return c
End Function
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
Alan Schofield
Starting Member
United Kingdom
23 Posts |
Posted - 03/04/2013 : 07:00:58
|
It really depends on what you current dataset looks like, but to be honest if you want to showe this as a seperate section of the report (eg. after all the detail) then it's probably as easy to do this in another dataset.
So, aggregatethe data into your three groups and just return those three values.
Assuming you are using SQL Server try something like.
SELECT
SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) <0 THEN 1 ELSE 0 END) AS Blue,
SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) IN (0,1) THEN 1 ELSE 0 END) AS Red,
SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) >1 THEN 1 ELSE 0 END) AS Green
FROM MyTable
This will return a simple 3 column single row result something like.
Blue Red Green
123 456 789
Note the datediff function works slightly differently in T-SQL, it just compares the date portions and ignores the time part (which is what you want).
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/04/2013 : 09:29:18
|
Hi
I created this and its returning ok but the numbers dont add up?
code:
SELECT /*c.OCCURED_DT, AGREED_SOLVED_DT, c.STATUS_NAME, ASSIGNED_GRP_NAME,*/
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) <0 THEN 1 ELSE 0 END) AS Blue,
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) IN (0,1) THEN 1 ELSE 0 END) AS Red,
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) >1 THEN 1 ELSE 0 END) AS Green
FROM DIM_CALL c
Where c.STATUS_NAME Not IN('Closed','Resolved')
And c.TYPE = 'Incident'
And ASSIGNED_GRP_NAME IN ('L1','L2','L3')
--Group by OCCURED_DT,
--AGREED_SOLVED_DT,
--STATUS_NAME,
--ASSIGNED_GRP_NAME
Result:
Blue Red Green
81 116 49908
There is only 289 records, too many reds and the greens is counting too many even though I'm only asking for open records...
The actual ticket total is 289
15 red
10 green
264 blue
Any idea?
Thanks
SZ1
Learning and development is the driving force in the universe...! |
Edited by - sz1 on 03/04/2013 09:47:57 |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/04/2013 : 11:32:28
|
I got around it another way by adding a datediff column and working it out from there based on number of days...
Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
Alan Schofield
Starting Member
United Kingdom
23 Posts |
Posted - 03/04/2013 : 11:51:20
|
Glad you got it working, not sure why previus query didn't work as it's really simple, it just add 1's up where there is a match and 0's where there is no match.
if you did SELECT COUNT(*) FROM Dim.Call WHERE {your previous WHERE clause} that should match the number of records that my previous query listed in total.
Anyway, as 'they' say, if it ain;t broke, don't fix it... Or if you're a SOftware Engineer, "If it ain't broke, it ain't got enough features yet!"
:)
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/04/2013 : 12:06:44
|
lol mate cheers
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/05/2013 : 10:30:32
|
Just wondering is it possible to group the code by the colour?
Public Function fnGetConditionalColor(pDate as datetime) AS String
Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
IF d<0 THEN
c="Green"
ELSE IF d =0 and d <=1 THEN
c="Red"
ELSE
c="Blue"
END IF
Return c
End Function
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
Topic |
|
percentage formatting
