Post reply

SQL Balance brought forward on a day basis

  • October 17, 2013 at 5:45 am

    #299319

    I am stuck on a query on how to bring data forward to the next day if criteria are met.

    Date: DataType CASEID

    01/01/2013 14:30:00 Open 1

    05/01/2013 16:30:00 Closed 1

    The I wanted to then show

    Date: DataType CASEID

    01/01/2013 14:30:00 Open 1

    02/01/2013 Open 1

    03/01/2013 Updated 1

    04/01/2013 Updated 1

    05/01/2013 16:30:00 Closed 1

    The critera would be carry into the next date if not 'closed'

    Is this possible with SQL?

    Thanks

    Jon

  • October 17, 2013 at 5:52 am

    #1659256

    First, I recommend you create a calendar table. Search this site or the internet for how to do that.

    Second, how do you decide whether the next date is "Open" or "Updated"?

    John

  • October 17, 2013 at 6:07 am

    #1659263

    Calander Table ok thanks will look at that.

    OK my posting is wrong

    Date: DataType CASEID

    01/01/2013 14:30:00 Open 1

    03/01/2013 Updated 1

    05/01/2013 Updated 1

    07/01/2013 16:30:00 Closed 1

    Then I wanted to then show

    Date: DataType CASEID

    01/01/2013 14:30:00 Open 1

    02/01/2013 Open 1

    03/01/2013 Updated 1

    04/01/2013 Updated 1

    05/01/2013 Updated 1

    06/01/2013 Updated 1

    07/01/2013 16:30:00 Closed 1

    I assume I can use a case statement with a calender to change all updated to Open and stop bringing the balance brought forward once a Closed record appears

  • October 17, 2013 at 7:10 am

    #1659304

    Where does the balance brought forward come from?

    From your sample data, it looks as if you need to use the "gaps and islands" technique. Here's[/url] one article; I think there are more out there.

    John

  • October 17, 2013 at 8:20 am

    #1659341

    The balance will start when the case opens, this will be in an audit tab, the DB will auto fill with Open.

    The Case will close when Close appears in the data type field.

    Then its recording everything inbetween as open.

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

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