I have a problem in sql

Hi,

Do you want to find the vat sum of a particular no or get the list of sum for each no.

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Try below query

 Select f_no,sum(f_vat) as f_vat,max(f_capital) as f_capital,max(f_interest) as f_interest from 

 (

	select 'LC001' as f_no,'AA' f_capital,100 as f_interest,4 as f_vat,'02/06/2012' f_duedate union all

	select 'LC001' as f_no,'AA' f_capital,100 as f_interest,4 as f_vat,'02/06/2012' f_duedate

 ) t

  where   

	(DATEPART(month, F_DUEDATE) = DATEPART(month, GETDATE())) AND 

	(DATEPART(year, F_DUEDATE) = DATEPART(year,GETDATE())) 	AND 

	f_no='LC001' AND F_DUEDATE<=getdate() 

 group by t.f_no

Change your table name accordingly..

Regards,
Sunil

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Dear Blessyjees,

I want to get the list of sum for each no.

Thank You....

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Try Sksingh query. this is not correct for your requirment, let us know.

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Dear Blessyjees,

I use that query, Bt not full fill my requirement,
Please help me.

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Can you explain the format of data with sample data. so i can easily give a solution.

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Dear Blessy,

This is my MS SQL Table and data

f_no f_capital f_interest f_vat f_duedate
LC001 1000.00 200.00 10.00 10/11/2011
LC001 500.00 100.00 20.00 10/12/2011
LC001 500.00 200.00 30.00 10/01/2012
LC001 700.00 300.00 25.00 10/02/2012
LC001 1000.00 100.00 15.00 10/03/2012


i want to get result is

f_no f_capital f_interest f_vat
LC001 700.00 300.00 85.00

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Hi try this

Select f_no,f_capital,f_interest,(select sum(f_vat) from @temp where f_no='LC001' and f_duedate <= GETDATE() group by f_no)as f_vat from tableName
where
(DATEPART(month, f_duedate) = DATEPART(month, GETDATE())) AND
(DATEPART(year, f_duedate) = DATEPART(year,GETDATE())) AND
f_no='LC001'

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Thanks for the reply dear, But it get a sql error,

Must declare the table variable "@temp".

how i fixed it,

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
you can go ahead with the query given by blessys with little modification .
replace @temp and teble name with your table name
I have attached the modified query below.
Try this and let us know

------------------------------------------------------------------Table
LC001 1111 100 10 2011-10-10 00:00:00.000
LC001 2222 200 20 2011-10-11 00:00:00.000
LC001 2222 200 20 2012-02-02 00:00:00.000
LC001 1000 100 10 2012-04-04 00:00:00.000
---------------------Query
Select f_no,f_capital,f_interest,(select sum(f_vat) from FORCAST_02 where f_no='LC001' and f_duedate <= GETDATE() group by f_no)as f_vat from FORCAST_02
where
(DATEPART(month, f_duedate) = DATEPART(month, GETDATE())) AND
(DATEPART(year, f_duedate) = DATEPART(year,GETDATE())) AND
f_no='LC001'


--------------------output
LC001 2222 200 50

Thanks,
Sanjay

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Thanks All for your Reply,

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator