Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert Access sql statement to T-SQL for SQL server

Posted on 2006-07-21
7
Medium Priority
?
548 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

604 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