Solved

Convert Access sql statement to T-SQL for SQL server

Posted on 2006-07-21
7
538 Views
Last Modified: 2008-01-09
Could someone please convert the following sql statement to T-SQL for me?

SELECT  CDate(Format([date/time of entry],"mm/dd/yyyy")) AS date, IIf(DatePart("h",[Date/Time of Entry])>20,"Shift 1",IIf(DatePart("h",[Date/Time of Entry])<9,"Shift 1","Shift 2")) AS Shift, "FF Grade A Packed" AS Title, Sum(IIf([iltrum]="cs",([lbs/cse]*[iltrqt]),[iltrqt])) AS lbs, 1 AS PrintOrder

FROM vwF4101 RIGHT JOIN (vwF4111Last6Months LEFT JOIN qryLbsPerCase ON vwF4111Last6Months.ILLITM = qryLbsPerCase.UPCODE) ON vwF4101.[2nd Item Number] = vwF4111Last6Months.ILLITM

WHERE (((vwF4111Last6Months.ILDCT)="ov") AND ((vwF4111Last6Months.ILRCD)="L1") AND ((IIf([iltrum]="cs",[iltrqt],0))>0) AND ((vwF4101.[Sub Section])="FF") AND ((IIf([Sales Category Code 3] Like "FCY","A","B"))="A") AND ((vwF4111Last6Months.[Date/Time of Entry]) Between [Forms]![frmMain]![txtStartDate] And [Forms]![frmMain]![txtEndDate]))

GROUP BY CDate(Forms!frmMain!txtDate.value), CDate(Format([date/time of entry],"mm/dd/yyyy")), IIf(DatePart("h",[Date/Time of Entry])>20,"Shift 1",IIf(DatePart("h",[Date/Time of Entry])<9,"Shift 1","Shift 2")), "FF Grade A Packed", 1;
0
Comment
Question by:schlepuetz
  • 5
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17156145
I gave up when I got as far as this:
IIf([iltrum]="cs",[iltrqt],0))>0)

In my mind that is the same as:
[iltrum] = 'cs' And iltrqt > 0  

But in view of the rest of the query, that does not make sense to only include the case when [iltrum] = 'cs'

Also, what does the following do for you:
IIf([Sales Category Code 3] Like "FCY","A","B"))="A"

Again, that does not make a lot of sense in a WHERE clause.  What am I missing.

Finally, you may be able to convert this query to T-SQL, however you may want to expect sub-optimal performance in comparison as it really needs to be re-designed for SQL Server rather than a simple port.
0
 
LVL 3

Author Comment

by:schlepuetz
ID: 17188323
The query was originally in an Access database.  

 IIF([iltrum]="cs",[iltrqt],0))>0)

if iltrum = "cs" then use [iltrqt] else 0 this is the value returned for that column but only use the records where this column is greater than 0

Same kindof deal for this one IIf([Sales Category Code 3] Like "FCY","A","B"))="A"

Does this help.

I was thinking of just splitting the query into one that has all of the data and then another one that will do the sorting and grouping for me.

I could use case statements in the queries but I do not know how to do the sorting and grouping with a case statement.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17189783
>>if iltrum = "cs" then use [iltrqt] else 0 this is the value returned for that column but only use the records where this column is greater than 0<<
But that means that no data will be selected when iltrum <> "cs".  as 0 is never > 0
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 3

Author Comment

by:schlepuetz
ID: 17193131
You are correct.

Same thing for the Sales Category Code 3.

Thanks for pointing that out for me.

What about the part of the SQL statement where the shift is set.  How would that part be handled in the group by?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17197540
Here is the query with line by line translations:

SELECT      -- CDate(Format([date/time of entry],"mm/dd/yyyy")) AS date
      CONVERT(char(10), [date/time of entry],101) [date],
      -- IIf(DatePart("h",[Date/Time of Entry])>20,"Shift 1",IIf(DatePart("h",[Date/Time of Entry])<9,"Shift 1","Shift 2")) AS Shift,
      CASE
            WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
            ELSE 'Shift 1'
      END Shift,
      -- "FF Grade A Packed" AS Title,
      'FF Grade A Packed' Title,
      --Sum(IIf([iltrum]="cs",([lbs/cse]*[iltrqt]),[iltrqt])) AS lbs,
      Sum(
      CASE
            WHEN [iltrum] = 'cs' THEN [lbs/cse]*[iltrqt]
            ELSE [iltrqt]
      END) lbs,
--      1 AS PrintOrder
      1 PrintOrder
FROM      -- vwF4101
      -- RIGHT JOIN (vwF4111Last6Months
      -- LEFT JOIN qryLbsPerCase ON vwF4111Last6Months.ILLITM = qryLbsPerCase.UPCODE) ON vwF4101.[2nd Item Number] = vwF4111Last6Months.ILLITM
      vwF4101
      RIGHT JOIN vwF4111Last6Months ON vwF4101.[2nd Item Number] = vwF4111Last6Months.ILLITM
      LEFT JOIN qryLbsPerCase ON vwF4111Last6Months.ILLITM = qryLbsPerCase.UPCODE
WHERE      -- (((vwF4111Last6Months.ILDCT)="ov")
      vwF4111Last6Months.ILDCT = 'ov'
      -- AND ((vwF4111Last6Months.ILRCD)="L1")
      AND vwF4111Last6Months.ILRCD = 'L1'
      -- AND ((IIf([iltrum]="cs",[iltrqt],0))>0)
      -- AND ((vwF4101.[Sub Section])="FF")
      AND vwF4101.[Sub Section] = 'FF'
      -- AND ((IIf([Sales Category Code 3] Like "FCY","A","B"))="A")
      -- AND ((vwF4111Last6Months.[Date/Time of Entry]) Between [Forms]![frmMain]![txtStartDate] And [Forms]![frmMain]![txtEndDate]))
      AND vwF4111Last6Months.[Date/Time of Entry] Between @StartDate And @EndDate
GROUP BY
      -- CDate(Forms!frmMain!txtDate.value),
      -- Since this is a constant there is no need to include in the GROUP BY (even in MS Access)
      -- CDate(Format([date/time of entry],"mm/dd/yyyy")),
      CONVERT(char(10), [date/time of entry],101),
      -- IIf(DatePart("h",[Date/Time of Entry])>20,"Shift 1",IIf(DatePart("h",[Date/Time of Entry])<9,"Shift 1","Shift 2")),
      CASE
            WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
            ELSE 'Shift 1'
      END
      -- "FF Grade A Packed",
      -- Since this is a constant there is no need to include in the GROUP BY (even in MS Access)
      -- 1
      -- Since this is a constant there is no need to include in the GROUP BY (even in MS Access)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 17197560
And for clarity here is the same query without the comment:

SELECT      CONVERT(char(10), [date/time of entry],101) [date],
      CASE
            WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
            ELSE 'Shift 1'
      END Shift,
      'FF Grade A Packed' Title,
      Sum(
      CASE
            WHEN [iltrum] = 'cs' THEN [lbs/cse]*[iltrqt]
            ELSE [iltrqt]
      END) lbs,
      1 PrintOrder
FROM      vwF4101
      RIGHT JOIN vwF4111Last6Months ON vwF4101.[2nd Item Number] = vwF4111Last6Months.ILLITM
      LEFT JOIN qryLbsPerCase ON vwF4111Last6Months.ILLITM = qryLbsPerCase.UPCODE
WHERE      vwF4111Last6Months.ILDCT = 'ov'
      AND vwF4111Last6Months.ILRCD = 'L1'
      AND (([iltrum] = 'cs' And [iltrqt] > 0) Or (0 > 0))
      AND vwF4101.[Sub Section] = 'FF'
      AND vwF4111Last6Months.[Date/Time of Entry] Between @StartDate And @EndDate
GROUP BY
      CONVERT(char(10), [date/time of entry],101),
      CASE
            WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
            ELSE 'Shift 1'
      END

This query is syntatically correct, however you should note the following:
1. It is very rarely a good idea to do this type of conversion, as you end up with sub-par performance.
2. There is no concept of [Forms]![frmMain]![txtStartDate] in T-SQL so I have replaced them with Local variables.
3. While it may be syntactically correct you may still get the wrong results.  Test carefully.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17201063
It looks like I left in a condition that should have been commented out:
SELECT     CONVERT(char(10), [date/time of entry],101) [date],
     CASE
          WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
          ELSE 'Shift 1'
     END Shift,
     'FF Grade A Packed' Title,
     Sum(
     CASE
          WHEN [iltrum] = 'cs' THEN [lbs/cse]*[iltrqt]
          ELSE [iltrqt]
     END) lbs,
     1 PrintOrder
FROM     vwF4101
     RIGHT JOIN vwF4111Last6Months ON vwF4101.[2nd Item Number] = vwF4111Last6Months.ILLITM
     LEFT JOIN qryLbsPerCase ON vwF4111Last6Months.ILLITM = qryLbsPerCase.UPCODE
WHERE     vwF4111Last6Months.ILDCT = 'ov'
     AND vwF4111Last6Months.ILRCD = 'L1'
     -- AND (([iltrum] = 'cs' And [iltrqt] > 0) Or (0 > 0))
     AND vwF4101.[Sub Section] = 'FF'
     AND vwF4111Last6Months.[Date/Time of Entry] Between @StartDate And @EndDate
GROUP BY
     CONVERT(char(10), [date/time of entry],101),
     CASE
          WHEN DATEPART(hour,[Date/Time of Entry]) BETWEEN 9 AND 20 THEN 'Shift 2'
          ELSE 'Shift 1'
     END
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question