sub-query in a main query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sub-query in a main query

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-01 : 20:38:25

hi,
what do i have to change in sub-query (ped_dtfatura = '04/01/2004' )
to check day by day between the date ranger of main query
(ped_dtfatura between '04/01/2004' and '04/30/2004') ?

in other words i need change this line (ped_dtfatura = '04/01/2004' )
changing '04/01/2004' for something (variable) that must have
all dates one by one in the ranger from main query.

tks
Carlos Lages

SELECT
(select SUM(ped1_quantp2) from pedido1
WHERE (ped1_codigo = ped_codigo) ) as Qttot,

(select fre_valor from tabfrete
WHERE (ped_tabfrete = fre_codigo) and

(select SUM(ped1_quantp2) from pedido1
INNER JOIN PEDIDO ON PED_CODIGO = PED1_CODIGO
WHERE PED_CODIGOCLIENTE = clientes.cli_codigo AND
(ped_codigo = ped1_codigo) and
ped_dtfatura = '04/01/2004' )
between fre_fxinicial and fre_fxfinal) as Vlfrete ,

ped_codigo

FROM pedido WITH (NOLOCK)
INNER JOIN transpor WITH (NOLOCK) ON pedido.ped_despacho = transpor.tra_codigo
INNER JOIN Clientes WITH (NOLOCK) ON pedido.ped_codigocliente = Clientes.cli_codigo
WHERE
(ped_despacho = tra_codigo and tra_gerafrete = 'S' ) and
ped_dtfatura between '04/01/2004' and '04/30/2004' and
(ped_emissaonota = 4)

ORDER BY cli_fantasia

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-04-01 : 20:53:09

Why not just change it to

ped_dtfatura between '04/01/2004' and '04/30/2004'

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-05 : 13:57:23

because if i do that i will get SUM of all record in this ranger. and
i need the SUM of the records DAY by DAY.

this is my problems

tks anyway

carlos Lages

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-05 : 14:15:09

If you were to provide DDL (CREATE TABLE statements) for your tables, DML (INSERT INTO statements) for sample data, and the expected result set using the sample data, then you might get an answer to your question.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-05 : 14:22:04

Use this in the where clause:

quote:
Originally posted by ValterBorges

ped_dtfatura between '04/01/2004' and '04/30/2004'

and try this:

SELECT	CONVERT(varchar(10), ped_dtfatura, 101),...

...the rest of your query...

GROUP BY CONVERT(varchar(10), ped_dtfatura, 101)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources