on 01-16-2015 2:19 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.