Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Question Unanswered: Select declared variable with case statements

    I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email.
    I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.

    I have declared all variables appropriately but I will leave that section of the code out.


    Select @ifiledate = iFileDate from tblTelemark where
    iFileDate = CASE
    WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN

    Select @countfri1 = Count(*) from tbl1
    Select @countfri2 = Count(*) from tbl2
    Select @countfri3 = Count(*) from tbl3
    Select @countfri4 = Count(*) from tbl4


    WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN
    Select @countsat1 = Count(*) from tbl1
    Select @countsat2 = Count(*) from tbl2
    Select @countsat3 = Count(*) from tbl3
    Select @countsat4 = Count(*) from tbl4

    WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN
    Select @countsun1 = Count(*) from tbl1
    Select @countsun2 = Count(*) from tbl2
    Select @countsun3 = Count(*) from tbl3
    Select @countsun4 = Count(*) from tbl4


    END

    Is there a way to do what this that works???

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This will explain why it fails:
    http://weblogs.sqlteam.com/jeffs/arc.../03/60195.aspx

    The values will be the same irrespective of the date right, you are just assigning them to variables of different names?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Yes, the set of queries are exactly the same except for the date changing for each ...the values from each set of queries will be assigned to different values depending on if its for friday, saturday or sunday.

    Thanks.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to perform an assignment of the value to @ifiledate variable, and then build a IF...THEN block to assign values to your counter variables.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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