cancel
Showing results for 
Search instead for 
Did you mean: 

Could not Pass Year as Parameter

Former Member
0 Kudos

Hi all,

I need an output where i need the sum of Quantity(Grouped by Item Group)  that was sold for a particular year which is the parameter . Here is the Query :

Declare @Date DATE

SET @Date =/*Select T5.DocDate From OINV T5 */'01-01-2014'

Select T0.ItmsGrpNam,

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='04' AND YEAR(A.DocDate)=@Date)'April',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='05' AND YEAR(A.DocDate)=@Date)'May',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='06' AND YEAR(A.DocDate)=@Date)'June',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='07' AND YEAR(A.DocDate)=@Date)'July'

From OITB T0

Condition : I don't want to straight-a-way give the 2014 as Input.

                 I just want it to fetch from the parameter that i give as date

Error : Operand type clash: date is incompatible with int

Accepted Solutions (1)

Accepted Solutions (1)

former_member184146
Active Contributor
0 Kudos

Hi,

     Try this

Declare @Date DATEtime

SET @Date =/*Select T5.DocDate From OINV T5 */YEAR([%0])

Select T0.ItmsGrpNam,

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='04' AND YEAR(A.DocDate)=@Date)'April',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='05' AND YEAR(A.DocDate)=@Date)'May',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='06' AND YEAR(A.DocDate)=@Date)'June',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='07' AND YEAR(A.DocDate)=@Date)'July'

From OITB T0

Regards,

Manish

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

Declare @Date as DATEtime

SET @Date = ( select t5.docdate from OINV T5 where T5.docdate = [%0])

Select T0.ItmsGrpNam,

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='04' AND YEAR(A.DocDate)=@Date)'April',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='05' AND YEAR(A.DocDate)=@Date)'May',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='06' AND YEAR(A.DocDate)=@Date)'June',

  (Select SUM(B.Quantity) From OINV A INNER JOIN INV1 B ON A.DocEntry=B.DocEntry INNER JOIN OITM C ON C.ItemCode=B.ItemCode

  Where C.ItmsGrpCod=T0.ItmsGrpCod AND Month(A.DocDate)='07' AND YEAR(A.DocDate)=@Date)'July'

From OITB T0

Thanks & Regards,

Nagarajan