Solved

Convert Access sql statement to T-SQL for SQL server

Posted on 2006-07-21
7
536 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

20 Experts available now in Live!

Get 1:1 Help Now