Convert Access query to sql

how to convert this access query to sql query

IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name

please......

case

when [Total Bunches] > 0 then Production * 1000 / [Total Bunches]

else 0

end

as Name2,

SUM(

case

when BlockInYield = -1 then [SIZE]

else 0

end

)

as Name1,

case when BlockInYield = 1 then (case when TC_M > 0 then TC_M else TC_DENS *[SIZE] end)

else 0

end

as Name

Thanks

Naras

|||There is no IFF operator in TSQL; as Naras is indicating the way to approach converting an IFF statement is to use an abstraction using CASE statements.sqlsql

Convert Access CROSSTAB query to SQL Table or View

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert the access query above to sql server 2000

In SQL Server 2000 you have't have predefined operator to get the PIVOT table..

Here you have to manually write the query to get the pivot result...

(Example)

Code Snippet

Create Table #BikeSales
(
Year int,
Product Varchar(100),
Sales Int
)

Insert Into #BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into #BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into #BikeSales Values ('2007', 'HONDA F1', 7000)

Insert Into #BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into #BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into #BikeSales Values ('2007', 'HONDA IRL', 1000)

Insert Into #BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into #BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into #BikeSales Values ('2007', 'HONDA MotoGP', 132)

Insert Into #BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into #BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into #BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
[Main].Product
,Sum([2005].Sales) as [2005]
,Sum([2006].Sales) as [2006]
,Sum([2007].Sales) as [2007]
From (Select Distinct Product From #BikeSales) as [Main]
Left Outer Join (Select * From #BikeSales Where Year=2005) as [2005] On [2005].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2006) as [2006] On [2006].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2007) as [2007] On [2007].Product=[Main].Product
Group By [Main].Product

You can generate the above query dynamically using the following script..

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join (Select * From #BikeSales Where Year=?) as [?] On [?].Product=[Main].Product '
Select @.PreparedSelectQuery =',Sum([?].Sales) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From #BikeSales Group By Year

Exec ('Select [Main].Product' + @.SelectQuery + ' From (Select Distinct Product From #BikeSales) as [Main]' + @.JoinQuery + ' Group By [Main].Product')

|||

Using Manivannan's data, this method of creating a 'pivot' table in SQL 2005 is quite a bit more efficient. (Single Pass, No JOINS, NO Sub-Queries, No Dynamic SQL.)

Code Snippet

DECLARE @.BikeSales table
( [Year] int,
Product varchar(25),
Sales int
)

Insert Into @.BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into @.BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into @.BikeSales Values ('2007', 'HONDA F1', 7000)
Insert Into @.BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into @.BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into @.BikeSales Values ('2007', 'HONDA IRL', 1000)
Insert Into @.BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into @.BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into @.BikeSales Values ('2007', 'HONDA MotoGP', 132)
Insert Into @.BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into @.BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into @.BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
Product,
[2005] = sum( CASE [Year] WHEN 2005 THEN Sales END ),
[2006] = sum( CASE [Year] WHEN 2006 THEN Sales END ),
[2007] = sum( CASE [Year] WHEN 2007 THEN Sales END )
FROM @.BikeSales
GROUP BY Product
ORDER BY Product

Constant Errors on Simple Sums... Why?

There seems to be a concept I'm not grasping. I don't understand why
I can't get a simple sum...
I have a singe table report based on a single dataset and all I want
to do is summarize some financials at 4 group levels.
I get both of these errors for every Sum expression in a report:
"A value expression used for the report parameter
'=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
function. Aggregate functions cannot be used in report parameter
expressions."
"The field expression for the data set ?DatasetName' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set."
I have only 2 parameters in the report, fiscalyear(int) and
fiscalperiod(int).
Half of my Sums aggregate YTD figures that do not reference either of
the parameters.
The other half aggregate MTD figures that are dependent on the
fiscalperiod parameter.
I've tried putting the expression directly into the appropriate text
field, and I have tried making the Sum expressions their own fields
and dropping those fields into the table. Nothing works.
Can anyone explain?
Thanks,
JodyHave you added groups to the table on the form? You need groups. Go to the
footer for the group. Use the expression builder to put the appropriate
values. If you are approaching it correctly it should be very straight
forward.
HTH,
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> There seems to be a concept I'm not grasping. I don't understand why
> I can't get a simple sum...
> I have a singe table report based on a single dataset and all I want
> to do is summarize some financials at 4 group levels.
> I get both of these errors for every Sum expression in a report:
> "A value expression used for the report parameter
> '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> function. Aggregate functions cannot be used in report parameter
> expressions."
> "The field expression for the data set 'DatasetName' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either the
> name of a containing group, the name of a containing data region, or
> the name of a data set."
> I have only 2 parameters in the report, fiscalyear(int) and
> fiscalperiod(int).
> Half of my Sums aggregate YTD figures that do not reference either of
> the parameters.
> The other half aggregate MTD figures that are dependent on the
> fiscalperiod parameter.
> I've tried putting the expression directly into the appropriate text
> field, and I have tried making the Sum expressions their own fields
> and dropping those fields into the table. Nothing works.
> Can anyone explain?
> Thanks,
> Jody|||All of the groups are there..
After some experimentation, I found that you don't actually have to
specify the scope in an aggregate in a table, and that helped.
Another part of the problem is that I'm getting inconsistent results
from my Preview pane and from the Debug preview window. The Debug is
generally right and the Preview if often wrong, even after I do a
rebuild.
Things are going better, but still far from what I had hoped for.
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. Tha helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. That helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody