dcsimg
Results 1 to 8 of 8

Thread: I have two tables: JobsAndTasks and JobsAndTasksHistory

  1. #1

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    I have two tables: JobsAndTasks and JobsAndTasksHistory

    I have two tables: JobsAndTasks and JobsAndTasksHistory

    The idea is that scheduled tasks and SQL jobs will be defined in a table called JobsAndTasks (this has already been completed). I want to select from that table Jobs and Tasks that should be checked within the next 4 hours by a human, but I'm running into a snag.

    Table: JobsAndTasks
    Code:
    (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[ServerName] [varchar](50) NOT NULL,
    	[JobName] [varchar](max) NOT NULL,
    	[IsSql] [bit] NULL,
    	[RunsSunday] [bit] NULL,
    	[RunsMonday] [bit] NULL,
    	[RunsTuesday] [bit] NULL,
    	[RunsWednesday] [bit] NULL,
    	[RunsThursday] [bit] NULL,
    	[RunsFriday] [bit] NULL,
    	[RunsSaturday] [bit] NULL,
    	[ScheduledTime] [time](7) NULL
    )
    Table JobsAndTasksHistory:
    Code:
    (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[JobAndTaskId] [int] NOT NULL,
    	[ServerName] [varchar](50) NOT NULL,
    	[JobName] [varchar](max) NOT NULL,
    	[IsSqlJob] [bit] NULL,
    	[DateTime] [datetime] NOT NULL,
    	[Analyst] [varchar](50) NOT NULL,
    	[JobSuccessful] [bit] NULL,
    	[Notes] [varchar](max) NULL
    )
    I had to know the day of the week the job ran. Some jobs run every day, while others only run on certain days. I'm trying to get the jobs that will occur in the next 4 hours, but I'm getting an error telling me that I can't convert the variable to int. Here's what I'm trying to do:

    Code:
    declare @dow varchar(50)
    set @dow = CONCAT('Runs', datename(dw,getdate()))
    select ServerName, JobName, IsSql, ScheduledTime from JobsAndTasks
    where (select @dow)=1
    and scheduledtime in (DATEADD(hour,-4,getdate()),dateadd(hour,4,getdate()))
    You will notice that I'm declaring a variable called @dow for Day Of Week to get the name of the current day. I then have to pre-pend the word "Runs" to that. I'm trying to use it as a column name in my where clause to see if RunsThursday=1 or RunsFriday=1. Anybody have any ideas on how to solve this issue?
    Last edited by mbutler755; May 29th, 2014 at 08:51 PM. Reason: Half my post was cut
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  2. #2

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    What this is doing is not using a field name = 1, but "RunsThursday"=1 as a string. That inner Select is returning a value, not a field name.

    Any time I had to use dynamic field names, I always constructed an SQL statment in code, not as an SP, such as this in VB:

    SQLWHERE = 'WHERE [Runs" & Weekday & "]=1"
    Last edited by Jrwakefield; May 30th, 2014 at 08:58 AM.

  3. #3

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    Try this:

    @Weekday as varchar(25)

    select ServerName, JobName, IsSql, ScheduledTime from JobsAndTasks
    where (([RunsSunday]=1 AND @Weekday='Sunday')
    OR ([RunsMonday]=1 AND @Weekday='Monday')
    OR ([RunsTuesday]=1 AND @Weekday='Tuesday')
    OR ([RunsWednesday]=1 AND @Weekday='Wednesday')
    OR ([RunsThursday]=1 AND @Weekday='Thursday')
    OR ([RunsFriday]=1 AND @Weekday='Friday')
    OR [RunsSaturday]=1 AND @Weekday='Saturday'))
    and scheduledtime in (DATEADD(hour,-4,getdate()),dateadd(hour,4,getdate()))

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,453

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    nope... won't work... because the list will only contain two times... four hours ago and four hours from now... the list used in an IN clause is dicrete and doesn't include a range... if you want a range... then use the between...

    and you'll want between now and 4 hours from now:

    Code:
    and (scheduledtime between getdate() and dateadd(hour,4,getdate()))
    I think I got the parenthesis right... you may want to re-format to pair them up correctly..

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    I guess I can do it in VB and just set the date as a variable. I'll think more about this today and see if I can get it resolved. I'll post here how I end up doing it.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  6. #6

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    Quote Originally Posted by mbutler755 View Post
    I guess I can do it in VB and just set the date as a variable. I'll think more about this today and see if I can get it resolved. I'll post here how I end up doing it.
    Just copy my where clause and try that, it should work fine, and the date as well you can pass as a parameter.

  7. #7

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    I tried. You cannot declare a variable in a dataset query (xsd)
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  8. #8

    Re: I have two tables: JobsAndTasks and JobsAndTasksHistory

    Quote Originally Posted by mbutler755 View Post
    I tried. You cannot declare a variable in a dataset query (xsd)
    Hmm, I didnt post the entire SP code. @Weekday as varchar(25) is a parameter you pass to the sp from your VB program, gets declared just after the SP name

    Something like

    ALTER PROCEDURE [dbo].[SP-Name]
    (@Weekday as varchar(25), @DateVariable as Date)
    AS
    select ServerName, JobName, IsSql, ScheduledTime
    from JobsAndTasks
    where (([RunsSunday]=1 AND @Weekday='Sunday')
    OR ([RunsMonday]=1 AND @Weekday='Monday')
    OR ([RunsTuesday]=1 AND @Weekday='Tuesday')
    OR ([RunsWednesday]=1 AND @Weekday='Wednesday')
    OR ([RunsThursday]=1 AND @Weekday='Thursday')
    OR ([RunsFriday]=1 AND @Weekday='Friday')
    OR [RunsSaturday]=1 AND @Weekday='Saturday'))
    [your time range criteria]

    You pass @Weekday from your VB when you call the sp. That value would be the weekday of the current date as in one of those in the single quotes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width