Solved

Convert Access sql statement to T-SQL for SQL server

Posted on 2006-07-21
7
532 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 3

Author Comment

by:schlepuetz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now