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],

Answer 1

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

Answer 2

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.

 

 

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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...

Answer 7

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

Answer 8

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'.

Answer 9

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

Answer 10

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. 

Answer 11

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

Answer 12

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

Answer 13

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.

Answer 14

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

Answer 15

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

Answer 16

>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