none
MS

    Question

  • I got another ACCESS QUERY and struggling to re-write in sql 2008

    SELECT DISTINCT
    [HTI - AM].Status,
    [HTI - AM].[SDate],
    [HTI - AM].[SType],

    • Edited by Zain2 Monday, February 21, 2011 2:37 PM test
    • Changed type Zain2 Monday, February 21, 2011 2:38 PM Just remove it please confidential purpose
    Wednesday, December 29, 2010 10:31 PM

Answers

  • Hi Salman,

    The Access function DateSerial(2009,[Month],[Day]) creates a date from
    the three components year (2009), month and day (from columns). SQL Server has no simple equivalent, the closest is

    DATEADD(day, DATEADD(month, '20080101', [Month]-1), [Day]-1)

    The Access IIf function evaluates the first expression (which has to
    be a condition), the returns either the second or third expression if
    the evaluation result is true or false. You can rewrite this as a CASE expression:

    CASE WHEN (expression) THEN (result when true) ELSE (result when
    false) END

    Note that unlike IIf, CASE can use multiple WHEN ... THEN pairs.

    The Access LIKE is similar to the SQL Server LIKE, except that SQL
    Server uses different wildcards. Access uses * for zero or more
    characters; SQL Server uses % for that purpose. Your query has some
    patterns that use LIKE "**H*" - I don't understand these; since *
    matches any length, what is the point in using it twice?
    I also don't understand the LIKE where no wildcard is used at all;
    using = (or <> for NOT LIKE) is more efficient and easier to
    understand.

    Anyway, here's my stab at a rewrite. Please do test it thoroughly. (And note that I changed all " to ' - if I didn't miss any).

    WITH PreCalc
    AS (SELECT h.Status, h.SDate, h.SType, h.[Auth Status]
          DATEADD(day,
              DATEADD(month, '20080101', [Month]-1),
             [Day]-1) AS ds,
          DATEADD(day,
              DATEADD(month, '20080101', [Month]-1),
             [Day]-1) - SDate AS [Days Late]
      FROM    [CLT TSIH] AS c
      RIGHT JOIN [HTI - Am] AS h
         ON  c.TLC = h.SDate)
    SELECT DISTINCT
        [L No], [H No], SName, FName, Status, SDate, SType,
        [Days Late],
        CASE WHEN [Days Late] <= 14
          THEN '1' ELSE '0' END AS [0 - 2 Week Marker],
        CASE WHEN [Days Late] > 91
          THEN '1' ELSE '0' END AS [13 Week Marker]
    FROM  PreCalc
    WHERE [L No] Not Like '%H%'
    AND  [L No] Not Like '%F%'
    AND  [L No] Not Like '%G%')
    AND  Status IN ('I' , 'C')
    AND  [S Type] Not Like 'PM%'
    AND  [S Type] NOT IN ('P1', 'P2' , 'P3', 'PL')
    AND  [Days Late] > 14
    AND  TLC NOT IN ('P', 'PM1')
    ORDER BY [Days Late] DESC;

    There were a lot of nested parentheses in the WHERE that I tried to
    remove - hopefully I did not mess up. I also removed some conditions
    that were duplicated.

    And I suggest getting rid of column and table names that are reserved
    words or contain spaces, so that you don't have to type the [brackets]
    all the time.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Edited by Hugo KornelisMVP Sunday, January 02, 2011 9:50 PM
    • Marked as answer by Zain2 Tuesday, March 01, 2011 7:20 PM
    Wednesday, December 29, 2010 11:36 PM

All replies

  • Hi Salman,

    The Access function DateSerial(2009,[Month],[Day]) creates a date from
    the three components year (2009), month and day (from columns). SQL Server has no simple equivalent, the closest is

    DATEADD(day, DATEADD(month, '20080101', [Month]-1), [Day]-1)

    The Access IIf function evaluates the first expression (which has to
    be a condition), the returns either the second or third expression if
    the evaluation result is true or false. You can rewrite this as a CASE expression:

    CASE WHEN (expression) THEN (result when true) ELSE (result when
    false) END

    Note that unlike IIf, CASE can use multiple WHEN ... THEN pairs.

    The Access LIKE is similar to the SQL Server LIKE, except that SQL
    Server uses different wildcards. Access uses * for zero or more
    characters; SQL Server uses % for that purpose. Your query has some
    patterns that use LIKE "**H*" - I don't understand these; since *
    matches any length, what is the point in using it twice?
    I also don't understand the LIKE where no wildcard is used at all;
    using = (or <> for NOT LIKE) is more efficient and easier to
    understand.

    Anyway, here's my stab at a rewrite. Please do test it thoroughly. (And note that I changed all " to ' - if I didn't miss any).

    WITH PreCalc
    AS (SELECT h.Status, h.SDate, h.SType, h.[Auth Status]
          DATEADD(day,
              DATEADD(month, '20080101', [Month]-1),
             [Day]-1) AS ds,
          DATEADD(day,
              DATEADD(month, '20080101', [Month]-1),
             [Day]-1) - SDate AS [Days Late]
      FROM    [CLT TSIH] AS c
      RIGHT JOIN [HTI - Am] AS h
         ON  c.TLC = h.SDate)
    SELECT DISTINCT
        [L No], [H No], SName, FName, Status, SDate, SType,
        [Days Late],
        CASE WHEN [Days Late] <= 14
          THEN '1' ELSE '0' END AS [0 - 2 Week Marker],
        CASE WHEN [Days Late] > 91
          THEN '1' ELSE '0' END AS [13 Week Marker]
    FROM  PreCalc
    WHERE [L No] Not Like '%H%'
    AND  [L No] Not Like '%F%'
    AND  [L No] Not Like '%G%')
    AND  Status IN ('I' , 'C')
    AND  [S Type] Not Like 'PM%'
    AND  [S Type] NOT IN ('P1', 'P2' , 'P3', 'PL')
    AND  [Days Late] > 14
    AND  TLC NOT IN ('P', 'PM1')
    ORDER BY [Days Late] DESC;

    There were a lot of nested parentheses in the WHERE that I tried to
    remove - hopefully I did not mess up. I also removed some conditions
    that were duplicated.

    And I suggest getting rid of column and table names that are reserved
    words or contain spaces, so that you don't have to type the [brackets]
    all the time.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Edited by Hugo KornelisMVP Sunday, January 02, 2011 9:50 PM
    • Marked as answer by Zain2 Tuesday, March 01, 2011 7:20 PM
    Wednesday, December 29, 2010 11:36 PM
  • I am getting the following errors:

    1. "Multi part identifer h.lno could not be bound and the rest of fields are same.

    2. You may have notice month and day are parameters, which user have to enter, when query is executed? this are dynamic.

    If i comment DATEADD function it works:

    /**DATEADD(day,DATEADD(month, '20090101', [Month]-1),[Day]-1) AS ds,

    DATEADD(day,DATEADD(month, '20090101', [Month]-1),[Day]-1) [S Date] AS [Days Late]

    */

    Any suggestions would be greatly appreciated.

     

     

    Thursday, December 30, 2010 11:30 PM
  • Hi Salman,

    I am getting the following errors:

    1. "Multi part identifer h.lno could not be bound and the rest of fields are same.

    I don't see any "h.lno" in my query. I do see "h.[L No]" in one place.
    If that causes this error, then the corresponding "[HTI - AM].[L No]"
    in your query should have caused the same error.

    As much a I'd love to help you figure out the problem, I really can't
    based on the information you provided. If you want me to post a tested
    solution, then please post the following:

    - The design of the tables involved, as CREATE TABLE statements. Make
    sure to include all constraints and indexes - you may omit irrelevant
    columns, though.

    - Some well-chosen rows of sample data, as INSERT statements.

    - Expected output for the sample data posted.

    Please make sure to test your code in an empty database before posting
    here, and please use the "Insert Code Block" button when adding the
    code to your psot - otherwise, the forum software might decide to do
    some funny reformatting.

    2. You may have notice month and day are parameters, which user have to enter, when query is executed? this are dynamic.

    Actaully, I had not noticed that. Your original query uses [Month] and
    [Day] as columns from one of the two tables involved. If they are
    parameters, then you have to code them as @Month and @Day.

    Asking the user to enter data for these parameters when the query is
    executed has to be handled by your front end. SQL Server itself is a
    back end process that never interacts directly with the user.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Friday, December 31, 2010 12:33 AM
  • Sorry it worked fine except the date function, as you said I modified the query as follows:

    I am getting the following errors:

    1. "Multi part identifer h.[l no] could not be bound and the rest of fields are same. (Is RESOLVED)

     

    I declared the parameters and uncommented the DATEADD function, same error.

    My new query Looks like this:

    declare

     

    @Month int

    declare

     

    @Day int

    SELECT h.[L No], h.[H No], h.SName, h.FName,
               h.Status, h.SDate, h.SType, h.[Auth Status]
               DATEADD(day,
                       DATEADD(month, '20080101', [Month]-1),
                      [Day]-1) AS ds,
               DATEADD(day,
                       DATEADD(month, '20080101', [Month]-1),
                      [Day]-1) - SDate AS [Days Late]
       FROM       [CLT TSIH] AS c
       RIGHT JOIN [HTI - Am] AS h
             ON   c.TLC = h.SDate

     

     

    If i comment DATEADD function it works:

    /**DATEADD(day,DATEADD(month, '20090101', [Month]-1),[Day]-1) AS ds,

    DATEADD(day,DATEADD(month, '20090101', [Month]-1),[Day]-1) [SDate] AS [Days Late]

    */

    Any suggestions would be greatly appreciated.

    Friday, December 31, 2010 12:47 AM
  • The DATEADD function takes 3 parameters. BOL reference

    DATEADD (datepart , number , date )

    Now, take a look at what did you put there.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, December 31, 2010 12:51 AM
    Moderator
  • Sorry for being dump, I am not sure how i can add parameters in t-SQL query.  The original access query when executed prompts user to type twice month and day. and than it just brings the results....  I sound bit daft...
    Friday, December 31, 2010 12:57 AM
  • If you can do what Hugo asked you to, e.g. in SSMS right click on your tables that you used in a query and select Script tables as Select to ... and then paste the results here. If you can also provide some insert statements and desired output, that will be great.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, December 31, 2010 1:03 AM
    Moderator
  • declare @Month int,@Day int
    
    select...... everything works
    
    except this part
    ========
    DATEADD(day,DATEADD(month, '20090101', @Month-1),@Day-1) AS ds,
    DATEADD(day,DATEADD(month, '20090101', @Month-1),@Day-1) – [SDate] AS [Days Late]
    ===========
    
    and the error is
    
    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near 'day'.
    Friday, December 31, 2010 1:15 AM
  • It is not going to work as DateAdd function has number as the second parameter and not a date.

    What do you want to achieve with these strange date manipulations? In other words, can you explain in English how ds and Days Late should be calculated based on the Month and Date parameters?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, December 31, 2010 1:21 AM
    Moderator
  • Yes you are absolutely right, Month and Day is an integer parameter.

    The existing access query when executed, prompts user to enter month number and day number. and than it brings the results. 

    they are calculated based on the user input for month and day. 

    Friday, December 31, 2010 1:30 AM
  • Salman,

    All I can answer on this statement is LOL (laughing out loud). You stated this few times already but you never gave the DDL for tables, insert statements and expected output or at least explained what results based on Month and Day you were getting.

    What is ds and what is Days Late. If you can at least answer on these questions, I'll help you with the query.

    Although I'm going out right now and will be back in ~2+ hours.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, December 31, 2010 1:41 AM
    Moderator
  • Hi Salman,

    It seems I had the order of some arguments to the DATEADD function
    mixed up. That's one of the things I do quite often - and hence one of
    the reasons I specifically asked for you to provide CREATE TABLE and
    INSERT statements, so that I could actually test what I posted. I
    can't force you to provide that information, of course, but wihtout
    it, you can't expect me (or anyone) to give you good help.

    Here is my next untried attempt:

           DATEADD(day,@Day-1,DATEADD(month, @Month-1, '20090101')) AS ds,
           DATEDIFF(day, '20091010', DATEADD(day,@Day-1,DATEADD(month,
    @Month-1, SDate))) AS [Days Late];

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Saturday, January 01, 2011 1:58 PM
  • Hugo million and billion thanks for your help.

    This is exactly i wanted to do, just count number of days  in a variable from the @Month,  passed from 1st of January 2009.

    I am very new to sql 2008 and i would be grateful if you please explain what is happening within the following statement/function:

    DATEADD(day,@Day-1,DATEADD(month, @Month-1, '20090101')) AS ds,
     DATEDIFF(day, '20091010', DATEADD(day,@Day-1,DATEADD(month,
    @Month-1, SDate))) AS [Days Late];
    

     I am assuming ds are number of days between @Month - 20090101.

    and Days Late are number of days between a given day - SDate.

    I tried the following script and it returns row of data in two columns ds and Days Left:

    DS: value is 2009-04-01 00:00:00:00

    Days Left: value is 538.

    DECLARE @Day AS Integer, @Month as Integer 
    DECLARE @SDate AS datetime = GETDATE() 
    
    SET @Day =1
    SET @Month=04
    
    select 
    DATEADD(day,@Day-1,DATEADD(month, @Month-1, '20090101')) AS ds,
    DATEDIFF(day, '20091010', DATEADD(day,@Day-1,DATEADD(month,
    @Month-1, @SDate))) AS [Days Late];
    

    Thanks in anticipation if you elaborate what is happening in this function.

    • Edited by Zain2 Saturday, January 01, 2011 8:26 PM corrections
    Saturday, January 01, 2011 8:14 PM
  • Hi Salman,

    I am very new to sql 2008 and i would be grateful if you please explain what is happening within the following statement/function:

    DATEADD(day,@Day-1,DATEADD(month, @Month-1, '20090101')) AS ds,
    DATEDIFF(day, '20091010', DATEADD(day,@Day-1,DATEADD(month,
    @Month-1, SDate))) AS [Days Late];
    

    I'll try to explain, but I also suggest reading up on DATEDIFF and
    DATEADD in Books Online.

    The first one is the most complicated:

    DATEADD(day,@Day-1,DATEADD(month, @Month-1, '20090101')) AS ds,

    This is a nested function call, so let's first look at the nested
    function:

    DATEADD(month, @Month-1, '20090101')

    This takes the date January 1st, 2009 (as character string, but since
    DATEADD expects a date or datetime parameter, it will be implicitly
    converted, then adds the specified month number minus 1 to it. So if
    you pass @Month = 1, it will add nothing; if you pass @Month = 7, it
    will add 6 months, arriving at July 1st, 2009. The result is the first
    day of the specified month.

    This result (let's call it M for now) is then nested in a similar
    function:

    DATEADD(day,@Day-1,M) AS ds,

    Here, the same happens, but now for the @Day parameter. Pass for
    instance @Day = 15, and 15 - 1 = 14 days will be added to the first
    day of the specified month, arriviing at the 15th day of that month.

    The "AS ds" at the end merely serves to give this computed column in
    the result set a column name. Do note, though, that I don't do any
    validity checks on the passed arguments - pass in @Month = 15 and @Day
    = 42, and you still get a valid day as result (probably somewhere in
    April 2010). There are other ways to construct a date value out of
    2009, @Month, and @Day, that will give you an error if the arguments
    passed are invalid - let me know if you prefer that.

    For [Days Late], the entire expression to calculate ds is repeated
    inside yet another function - I'll repeat that function here, after
    replacing the expression with the name ds:

    DATEDIFF(day, '20091010', ds) AS [Days Late];

    And this merely calculates the number of days between October 10th,
    2009, and ds (the day calculated from @Day and @Month). Why October
    10th, you may ask? Entirely my fault - in spite of what I wrote, I
    tried to test my code anyway, but since I have no table that holds an
    SDate column, I simply replaced it with a constant value. And then I
    decided not to test, but forgot to change this back. So for your code,
    you'll have to use SDate instead of '20091010'.

    I tried the following script
    (snip)

    You replaced '20090101' with SDate. You should have left '20090101'
    intact (that is the base date for constructing a date in 20009 from
    @Month and @Day), and replaced '20091010' (my silly placeholder date)
    with SDate instead


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by KJian_ Tuesday, January 04, 2011 8:40 AM
    Saturday, January 01, 2011 9:59 PM
  • Hugo,

    I don't know how to thank you but once again many thanks.

    May i please request you to remove part of the query from your post just for confidential purpose.  However, i do wish to share with other visitors to see the core point we manage to resolve with your solution.

    Once again Many thanks


    PMP Project Manager
    Sunday, January 02, 2011 8:03 PM
  • >May i please request you to remove part of the query from your post just for confidential purpose.  However, i do wish to share with other visitors to see the core point we manage to resolve with your solution.

    I checked how you edited your original post and tried to do the same
    modifications in my reply. Let me know if I missed any key elements
    that need to be removed.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, January 02, 2011 9:53 PM