?
Solved

Convert Access sql statement to T-SQL for SQL server

Posted on 2006-07-21
7
Medium Priority
?
547 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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