I have a problem in sql

Posted by Nuwan under Sql Server on 2/6/2012 | Points: 10 | Views : 1492 | Status : [Member] | Replies : 11
Dear All,

I have a SQL Table it have five main field f_no,f_capital,f_interest,f_vat and f_duedate.
I use below query
(1).Select f_capital,f_interest from FORCAST_02 where
(DATEPART(month, FORCAST_02.F_DUEDATE) = DATEPART(month, GETDATE())) AND (DATEPART(year, FORCAST_02.F_DUEDATE) = DATEPART(year,GETDATE())) and f_no='LC001'

now i want to get a f_vat sum in this table with above query.

(2).Select f_no,sum(f_vat) from FORCAST_02 where f_no='LC001' and FORCAST_02.F_DUEDATE<=getdate()

manually properly work this query, but i want to combined that two query,

PLEASE HELP ME....

Rathnayake


Responses

Posted by: Blessyjees on: 2/6/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Sksingh on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Nuwan on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Blessyjees on: 2/6/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Nuwan on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Blessyjees on: 2/6/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Nuwan on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Dotnetguy on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Nuwan on: 2/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sksamantaray on: 2/6/2012 [Member] Silver | Points: 25

Up
0
Down
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

Posted by: Nuwan on: 2/8/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks All for your Reply,

Rathnayake

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

Login to post response