x

Access query to sql stp

SELECT IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2)) AS Expr1, IIf([manufacturer]<>'',[manufacturer],'Other') AS mymfr, Sum(tblMain.AdCost) AS SumOfAdSize INTO ytdAll
FROM tblUpdate, tblMain INNER JOIN tblBrand ON tblMain.Mfr = tblBrand.Manufacturer
WHERE (((Year([AdDate]))=[tblupdate].[year]))
GROUP BY IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2)), IIf([manufacturer]<>'',[manufacturer],'Other')
ORDER BY IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2));

I haven't coded in a long time and i have to get this done but this is one of only 100. I'm stalled on converting the above code to sql can anyone help.

more ▼

asked Dec 07, 2012 at 07:04 PM in Default

artistlover gravatar image

artistlover
616 17 47 53

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

OK, so the thing to remember here is the CASE expression. Use that to take out all your Access IIFs.

SELECT CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1,
       CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS mymfr,
       SUM(tblMain.AdCost) AS SumOfAdSize
INTO ytdAll
FROM tblMain 
    LEFT JOIN tblUpdate ON Year(tblMain.AdDate) = tblUpdate.Year
    LEFT JOIN tblBrand ON tblMain.mfr = tblBrand.Manufacturer
GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END ,
       CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END 
ORDER BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END

A few things to bear in mind...

  1. I moved the WHERE Clause to a JOIN criterion
  2. That JOIN criterion may not be so performant - you might want to consider changing it in the future. You'll be wanting to eliminate the call to the YEAR function to improve speed of processing.
  3. The CASE statement in the ORDER BY clause can be replaced with a 1 to tell SQL Server to sort by the first column in the result set.
more ▼

answered Dec 10, 2012 at 01:28 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

I pretty much came up with something simliar but i get error there is already object named 'ytdall' in the database

Of course there is

Thank you so much for the help
Dec 11, 2012 at 02:41 PM artistlover
:-) No problem.
Dec 11, 2012 at 05:22 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

i have one other problem related its similar to the one you helped me with but it has a pivot

SELECT CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1, CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS mymfr

FROM tblMain LEFT JOIN tblUpdate ON Year(tblMain.AdDate) = tblUpdate.[Year] LEFT JOIN tblBrand ON tblMain.mfr = tblBrand.Manufacturer and tblmain.media not like '%tv%'

PIVOT  

( datepart(Month,addate) for addate in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END , CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END ORDER BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END ENd

more ▼

answered Dec 11, 2012 at 05:40 PM

artistlover gravatar image

artistlover
616 17 47 53

You might be better off asking this as a separate question...
Dec 11, 2012 at 06:34 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x280

asked: Dec 07, 2012 at 07:04 PM

Seen: 505 times

Last Updated: Dec 11, 2012 at 06:34 PM