SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 opening balance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  07:50:07  Show Profile  Reply with Quote
select APOBL.IDVEND,APOBL.AMTINVCHC,APOBL.IDINVC,APOBL.DATEINVC,
(select sum (APOBL.AMTINVCHC)from APOBL where APOBL.DATEINVC < 20200309 group by apobl.idvend)openingbalance,
APVEN.VENDNAME
from APOBL
join APVEN on APOBL.IDVEND=APVEN.VENDORID where DATEINVC between 20190309 and 20200309
and VENDNAME like 'e%'




--------------------------
BY
SRISHA

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  07:59:09  Show Profile  Reply with Quote
error is came

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:03:51  Show Profile  Reply with Quote
--May be this?
select A.IDVEND
	,A.AMTINVCHC
	,A.IDINVC
	,A.DATEINVC
	,B.openingbalance
	,C.VENDNAME
from APOBL A
JOIN (select sum (AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < '20200309' group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID 
where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'


--
Chandu

Edited by - bandi on 06/20/2013 08:06:43
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  08:04:07  Show Profile  Reply with Quote
select APOBL.DATEINVC,APOBL.IDVEND,APOBL.DESCINVC,(select sum(APOBL.AMTINVCHC)over (partition by apobl.idvend)from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND where CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06')as amt,(select sum(APOBL.AMTINVCHC)over (partition by apobl.idvend) from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND where CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06' and CONVERT(date,CONVERT(varchar(8),apobl.dateinvc))<'2019-06-06' ) as amt1,APVEN.VENDNAME from APOBL join APVEN on APVEN.VENDORID=APOBL.IDVEND


this also same error


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:08:09  Show Profile  Reply with Quote
--You can do like this also
select DISTINCT A.IDVEND
	,A.AMTINVCHC
	,A.IDINVC
	,A.DATEINVC
	,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor
	,C.VENDNAME
from APOBL A
join APVEN c on A.IDVEND= C.VENDORID 
where (A.DATEINVC between '20190309' and '20200309' )and VENDNAME like 'e%'


--
Chandu
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  08:10:00  Show Profile  Reply with Quote
May be this?
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (APOBL.AMTINVCHC)openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'



this query using crystal report
but
error is
Arithmetic overflow error

--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:12:12  Show Profile  Reply with Quote
Look at the red mark
quote:
Originally posted by srisha

May be this?
select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < 20200309 group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between 20190309 and 20200309 )and VENDNAME like 'e%'



this query using crystal report
but
error is
Arithmetic overflow error

--------------------------
BY
SRISHA



--
Chandu
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  08:19:05  Show Profile  Reply with Quote
same thing

arithmetic overflow error converting expression to data type date time


select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (CAST(APOBL.AMTINVCHC AS BIGINT))openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:22:39  Show Profile  Reply with Quote
That is the error caused by your DATE input.
In which format you had date value for column DATEINVC

quote:
Originally posted by srisha

same thing

arithmetic overflow error converting expression to data type date time


select A.IDVEND
,A.AMTINVCHC
,A.IDINVC
,A.DATEINVC
,B.openingbalance
,C.VENDNAME
from APOBL A
JOIN (select sum (APOBL.AMTINVCHC )openingbalance, idvend from APOBL where DATEINVC < {?from} group by idvend) B on A.idvend = B.idvend
join APVEN c on A.IDVEND= C.VENDORID
where (A.DATEINVC between {?from} and {?to} )and VENDNAME like '{?vendor}%'


--------------------------
BY
SRISHA



--
Chandu
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  08:27:34  Show Profile  Reply with Quote
integer


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:32:11  Show Profile  Reply with Quote
quote:
Originally posted by srisha

integer


--------------------------
BY
SRISHA



How you represent date value as integer ?
Use proper data type
20190309 means 2019-03-09 ?


--
Chandu
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  08:35:23  Show Profile  Reply with Quote
k but i given only value passing parameter only


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/20/2013 :  08:55:17  Show Profile  Reply with Quote
DECLARE @FromDate DATE = '20190309', @ToDate DATE = '20190310' -- These are DATE type params
select DISTINCT A.IDVEND
	,A.AMTINVCHC
	,A.IDINVC
	,A.DATEINVC
	,SUM( AMTINVCHC) OVER(PARTITION BY idvend) OpeningBalAsPerVendor
	,C.VENDNAME
from APOBL A
join APVEN c on A.IDVEND= C.VENDORID 
where (CAST(A.DATEINVC AS VARCHAR(8))between @FromDate and @ToDate)and VENDNAME like 'e%'


NOTE: Use exact type data types for DATE Time values......
Alter your column (DATEINVC) type to DATE if you have permissions

--
Chandu

Edited by - bandi on 06/20/2013 09:32:56
Go to Top of Page

srisha
Starting Member

India
38 Posts

Posted - 06/20/2013 :  09:12:10  Show Profile  Reply with Quote
Msg 206, Level 16, State 2, Line 22
Operand type clash: date is incompatible with decimal


--------------------------
BY
SRISHA
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/21/2013 :  05:56:39  Show Profile  Reply with Quote
quote:
Originally posted by srisha

Msg 206, Level 16, State 2, Line 22
Operand type clash: date is incompatible with decimal
--------------------------
BY
SRISHA



Have you seen my reply (Posted - 06/20/2013 : 08:55:17)

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/21/2013 :  06:01:20  Show Profile  Reply with Quote
so how many threads will you open for the same problem?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186302

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/21/2013 06:01:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000