Post reply

help with the query

  • August 5, 2011 at 5:25 am

    #255315

    hi

    i'm developing a SSRS report and my report has a group: heres the sample of my data

    DurationDateDownDowntimeLossCodeAssetTypeCodeCodeCodeTimeFromTimeToLocationCode

    152011-04-08 10:45:55.860SER053DA6095P305661160117523

    202011-04-08 10:45:55.860BD053DA6095P305661180120023

    802011-04-08 10:45:55.860BD053DA6095P305664012023

    252011-04-09 06:47:46.477SER053DA6095P213031140116523

    352011-04-09 06:47:46.477BD053DA6095P213031210124523

    1202011-04-09 06:47:46.477BD053DA6095P2130313503023

    if you look into these transactions you'll see that it happened in one day at defferent times, the report is grouped by AssetTypeCode which is DA6095.

    what i need to do is check the last transaction of the day on the TimeTo(30) field compare it with the static value which will be 16:30:00 if it's greater than the startic value it should say 'Down' else 'up'.

    ofcause i'll convert the TimeFrom and TimeTo field to Time format.

    the output of the report should be like:

    AssetType Status

    DA6095 up

    thanks in Advance

  • August 5, 2011 at 6:59 am

    #1364317

    Each date time stamp for each day is the same, how do you know which one is the last transaction of the day?

  • August 5, 2011 at 7:02 am

    #1364321

    I think it's the TimeTo field that determines which is the last for the day, but I'm not sure what "30" means as a value in that column.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 5, 2011 at 7:04 am

    #1364322

    I'm not sure if the data you are showing is already grouped or if you need the query to do the grouping for you. Here are two options that might work.

    If the data is already grouped:

    SELECT

    AssetType,

    CASE WHEN Status > 1630 THEN 'Down' ELSE 'Up' END AS Status

    FROM table1

    WHERE TimeTo =

    (SELECT MAX(TimeTo) FROM Table1)

    If you need the query to do the grouping for you:

    SELECT

    AssetType,

    CASE WHEN MAX(Status) > 1630 THEN 'Down' ELSE 'Up' END AS Status

    FROM table1

    GROUP BY AssetType

    Let us know if it helps, Steve

  • August 7, 2011 at 11:21 am

    #1364899

    hi guys

    thanks for the response, i realized that i won't be able to get the Max(TimeTo) since the transactions happened on the same date and same time.

    i have another query though. i'm creating a line graph and the formula on the series properties should be 12-(field)/12*100 but it not giving me the correct values, i get weird numbers.

  • August 7, 2011 at 3:04 pm

    #1364930

    Nomvula (8/7/2011)

    hi guys

    thanks for the response, i realized that i won't be able to get the Max(TimeTo) since the transactions happened on the same date and same time.

    i have another query though. i'm creating a line graph and the formula on the series properties should be 12-(field)/12*100 but it not giving me the correct values, i get weird numbers.

    You really should start another post but, the following should do it for you... 12-(field)/12.0*100

    The problem you're probably running into is INTEGER math where something like 1/3 produces 0 instead of .33333333337.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply