translate MS Access query to SQL server View 1

[robertsquestion]

Hi,

I'm using a MS Access 2003 database. One table in there contains almost one million records. Performance is quite bad as there are a lot of complicated queries running on this huge table. I've copied a test-version of this database to SQL-server. Now copying the tables to SQL-server was not a problem, but I'm struggling a bit with translating an important MS Access Query to a SQL-server view.

So let's say the table name is: T_Sales
The table contains the following fields:
Customernr
Orderdate
Amount

Now the goal of the (access) query is to define a period and the year of this period for each record. The period should be defined as follows:
If the Orderdate is before the 30th of June, period should be period one (so 30-04-2009 will be 200901).
If the Orderdate is on or after the 30th of June, period should be period two (so 30-09-2009 will be 200902).
One exception: if the orderdate is between 25th of December and the 31th of December, the period should be the first period of the following year (so 28-12-2009 will be 201001).

With the following (MS Access) SQL this is working fine:

SELECT T_Sales.Customernr, T_Sales.Orderdate, T_Sales.Amount, "30-06-" & Year([Orderdate]) AS calc_date, IIf([Orderdate]<[calc_date],Right([Orderdate],4) & "01",IIf((DateDiff("d",[calc_date],[Orderdate]))>=178,Right([Orderdate],4)+1 & "01",Right([Orderdate],4) & "02")) AS Periode, Left([Periode],4) AS [Year]
FROM T_Sales
ORDER BY T_Sales.Orderdate;

Now my question is: how do I translate this to an SQL server View ? (or: what's the SQL-syntax on SQL server for this one?)

I hope someone can help me out, any help would be appreciated!

Thanks,
Robert
The Netherlands

 

[gmmastros]

When converting from access to sql server, a couple of things are different.

In Access, you use & to concatenate strings. In SQL Server, use + instead.

In Access, you use quote " for string literals. IN SQL Server, you should use single-quote ' instead.

In Access, you use IIF, in SQL Server use use case/when.

Ex:

IIf([OrderDate]<[calc_date],"X","Y")

Case When [OrderDate]<[calc_date] Then "X" Else "Y" End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Can you try the following code to see if it correctly calculates your periods?

SELECT T_Sales.Customernr, 
       T_Sales.Orderdate, 
       T_Sales.Amount,
       Case When Month(OrderDate) * 100 + Day(OrderDate) < 630
                 Then Year(OrderDate) * 100 + 1
            When Month(OrderDate) * 100 + Day(OrderDate) < 1225 
                 Then Year(OrderDate) * 100 + 2
            Else (Year(OrderDate) + 1) * 100 + 1
            End As Period
FROM   T_Sales
ORDER BY T_Sales.Orderdate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertsquestion]

George,

Thanks a lot for your advice! I will check the code, would really be great if this works.

Regards,
Robert

 

[robertsquestion]

George,

Just wanted to confirm that your code is working excellent. I really like your compact and clear solution!

Thanks again,
Robert

 

[gmmastros]

I'm glad it helped.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom