Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Report for every Monday and Frida

    Hi all I have a strange question and I hope I'm asking this correctly. But I have a report that automatically generate every month for classes that are running every Monday and Friday of the month. I am unsure as to how to have the report automatiacally generate every Monday and Friday. I have to have the Date and the Name of the Workshop, the Location and the number of students attending. This is what I have so far. The Fields I need to use to track the date with is the EventDate. For example on Monday 06/07/2010 I need the Location, the date and the number of students attending that day. and Friday 06/11/2010 I need the number of students and location and the date again. How do I get it to automitacally populate every monday and friday only. Can anyone help pls

    Code:
    SELECT     COUNT(*) AS TotalRows, COUNT(CASE WHEN (TanfActivity_tbl.NameofWorkshop LIKE N'WorldofWork%') THEN 1 END) AS TotalComputerWrkshps, 
                          dbo.TanfActivity_tbl.[Services Covered], dbo.TanfActivity_tbl.[Catagory for hours], dbo.TanfActivity_tbl.[Earned hours], 
                          dbo.TanfActivity_tbl.[Purpose of Contact], dbo.TanfActivity_tbl.NameofWorkshop, dbo.TanfActivity_tbl.EventDate, dbo.TanfActivity_tbl.[Parent ID], 
                          dbo.People_tbl.ServiceArea
    FROM         dbo.TanfActivity_tbl LEFT OUTER JOIN
                          dbo.People_tbl ON dbo.TanfActivity_tbl.[Parent ID] = dbo.People_tbl.[Parent ID]
    GROUP BY dbo.TanfActivity_tbl.[Services Covered], dbo.TanfActivity_tbl.[Catagory for hours], dbo.TanfActivity_tbl.[Earned hours], 
                          dbo.TanfActivity_tbl.[Purpose of Contact], dbo.TanfActivity_tbl.NameofWorkshop, dbo.TanfActivity_tbl.EventDate, dbo.TanfActivity_tbl.[Parent ID], 
                          dbo.People_tbl.ServiceArea
    HAVING      (dbo.TanfActivity_tbl.NameofWorkshop = N'World of Work')

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    USE model
    GO
    
    CREATE TABLE dbo.numbers (
       number int NOT NULL
    )
    
    ALTER TABLE dbo.numbers
    ADD
       CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (number)
         WITH FILLFACTOR = 100
    GO
    
    INSERT INTO dbo.numbers (number)
    SELECT (a.number * 256) + b.number As number
    FROM 	 (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND    number <= 255
           ) As a
     CROSS
      JOIN (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND    number <= 255
           ) As b
    GO
    
    CREATE TABLE dbo.calendar (
       the_date     datetime NOT NULL
     , is_monday    bit      NOT NULL
     , is_tuesday   bit      NOT NULL
     , is_wednesday bit      NOT NULL
     , is_thursday  bit      NOT NULL
     , is_friday    bit      NOT NULL
     , is_saturday  bit      NOT NULL
     , is_sunday    bit      NOT NULL
     , is_weekend As (is_saturday ^ is_sunday)
     , is_holiday   bit
     , holiday_desc varchar(50)
    )
    GO
    
    ALTER TABLE dbo.calendar
    ADD
       CONSTRAINT pk_calendar PRIMARY KEY CLUSTERED (the_date)
         WITH FILLFACTOR = 100
    GO
    
    INSERT INTO dbo.calendar (the_date, is_monday, is_tuesday, is_wednesday, is_thursday, is_friday, is_saturday, is_sunday)
    SELECT the_date
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 0 THEN 1 ELSE 0 END As is_monday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 1 THEN 1 ELSE 0 END As is_tuesday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 2 THEN 1 ELSE 0 END As is_wednesday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 3 THEN 1 ELSE 0 END As is_thursday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 4 THEN 1 ELSE 0 END As is_friday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 5 THEN 1 ELSE 0 END As is_saturday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 6 THEN 1 ELSE 0 END As is_sunday
    FROM   (
            SELECT DateAdd(dd, number, 0) As the_date
            FROM   dbo.numbers
           ) As dates
    Code:
    ...
     INNER
      JOIN dbo.calendar
        ON calendar.the_date = your_table.EventDate
    WHERE  1 IN (calendar.is_monday, calendar.is_friday)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I take it this worked out alright for yous?
    George
    Home | Blog

Posting Permissions

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