Help with query

[leite333]

Hi i want some help to create a little more complicate query.

SELECT
row_number() OVER (ORDER BY KWDPWLITI, CITY_1) AS Row_No ,

E1_Emp051_3.FULLNAME,
E1_Emp001_3.CITY_1,
E1_Emp001_3.KWDPWLITI,
E1_Emp001_3.TEL_1, E1_Emp001_3.TEL_2,


E1_Emp001_3.COMPANY,
E1_Emp001_3.YPOL,
E1_Emp001_3.SINTZIROU,
DATEDIFF(DAY,DATEPAR,GETDATE()) AS MESOS,

E1_Emp016_3.KWDPar,
E1_Emp016_3.TimePar

FROM E1_Emp001_3, E1_Emp051_3,E1_Emp016_3


WHERE E1_Emp051_3 .KWD = E1_Emp001_3.KWDPWLITI
AND E1_EMP016_3.kWDPEL=E1_Emp001_3.KWD
AND E1_Emp001_3.YPOL<>0
AND E1_Emp016_3.KWDPAR=1 OR E1_Emp016_3.KWDPAR=3

This is my query but in the last and i wanna take the datediff between datepar and today when the E1_Emp016_3.KWDPAR=1 OR E1_Emp016_3.KWDPAR=3 the query run almost right expect i get more records that i need. For example i get for every customer 16 records and not one..... I wanna say here that in the datepar i need latest day for every customer.....How i can add that;;;;;; i think i need max but i dont know how to use it;;;;;;;;;

[AccessPonderer]

To be honest its really not clear what you're trying to do.

I think you need to look into subqueries

This is an example of what you might want

SELECT row_number() OVER (ORDER BY KWDPWLITI, CITY_1) AS Row_No , E1_Emp051_3.FULLNAME, E1_Emp001_3.CITY_1, E1_Emp001_3.KWDPWLITI, E1_Emp001_3.TEL_1, E1_Emp001_3.TEL_2, E1_Emp001_3.COMPANY, E1_Emp001_3.YPOL, E1_Emp001_3.SINTZIROU, 
DATEDIFF(DAY,DATEPAR,GETDATE()) AS MESOS, E1_Emp016_3.KWDPar, E1_Emp016_3.TimePar FROM E1_Emp001_3, E1_Emp051_3,E1_Emp016_3 WHERE E1_Emp051_3 .KWD = E1_Emp001_3.KWDPWLITI AND E1_EMP016_3.kWDPEL=E1_Emp001_3.KWD AND E1_Emp001_3.YPOL<>0 AND E1_Emp016_3.KWDPAR=1 OR E1_Emp016_3.KWDPAR=3

WHERE E1_Emp016_3.Id =
(SELECT TOP 1 ID
 FROM   E1_Emp016_3 AS E1_Emp016_3_SUB
 WHERE E1_Emp016_3.CustomerId = E1_Emp016_3_SUB.CustomerId
 ORDER BY DatePar DESC
)

It's a bit clumsy and doesn't really match your style and might not match your schema, but for each customerId it only displays the maximum datepar.

Let us know if that's any help.

[leite333]

[IMG]file:///C:/DOCUME%7E1/leite/LOCALS%7E1/Temp/moz-screenshot.png[/IMG]
Ok my query is now
SELECT
row_number() OVER (ORDER BY KWDPWLITI, CITY_1) AS Row_No ,

E1_Emp051_3.FULLNAME,
E1_Emp001_3.CITY_1,
E1_Emp001_3.KWDPWLITI,
E1_Emp001_3.TEL_1, E1_Emp001_3.TEL_2,
E1_Emp001_3.COMPANY,
E1_Emp001_3.YPOL,
E1_Emp001_3.SINTZIROU,
DATEDIFF(DAY,DATEPAR,GETDATE()) AS MESOS,

E1_Emp016_3.KWDPar,
E1_Emp016_3.TimePar

FROM E1_Emp001_3, E1_Emp051_3,E1_Emp016_3


WHERE
E1_Emp001_3.YPOL<>0
AND E1_Emp051_3 .KWD = E1_Emp001_3.KWDPWLITI AND E1_EMP016_3.kWDPEL=E1_Emp001_3.KWD

but as you see in the picture the results i get more records. Now missing where kwdpar=1 or kwdpar=3 but when i put it i get thousands of results and where ipoloipo=0.......I think i need a sub select as you told me but how i can continue to get the max date for every record where kwdpar=1 or kwdpar=3

[AccessPonderer]

I'm on my phone and because of the formatting and the ridiculous names of your tables and columns its near enough impossible to see what you're trying to do.

Perhaps you could put more where clauses within the subquery.

Have a play around with the subquery and see whether you can make it work.

You can also use subqueries in the SELECT and FROM statements too.

[AccessPonderer]

I've just noticed that you're not actually joining your tables together.

Have you done this for a reason?

[leite333]

Hi.... no do i need to join??????????? I didnt gave the names its from a comercial program. How i can do the query with join????
Now i have done that but still i have problems

SELECT
row_number() OVER (ORDER BY KWDPWLITI, CITY_1) AS Row_No ,

E1_Emp001_3.ONOMAPWLITI,
E1_Emp001_3.CITY_1,
E1_Emp001_3.KWDPWLITI,
E1_Emp001_3.TEL_1, E1_Emp001_3.TEL_2,
E1_Emp001_3.COMPANY,
E1_Emp001_3.YPOL,
E1_Emp001_3.SINTZIROU,
DATEDIFF(DAY,DATEPAR,GETDATE()) AS MESOS,

E1_Emp016_3.KWDPar,
E1_Emp016_3.TimePar,
E1_Emp016_3.DATEPAR
FROM E1_Emp001_3,E1_Emp016_3

WHERE
E1_Emp001_3.YPOL<>0
AND E1_EMP016_3.kWDPEL=E1_Emp001_3.KWD

AND DATEPAR=(SELECT TOP 1 DATEPAR

FROM E1_EMP016_3 AS E1_Emp016_3_SUB
WHERE E1_Emp016_3.DATEPAR= E1_Emp016_3_SUB.DATEPAR
AND E1_Emp016_3.KWDPAR=1 OR
E1_Emp016_3.KWDPAR=3
ORDER BY DatePar DESC)

I am new in sql queries thats why maybe seem simple to you
As the result of above i get
* 2 same records with different days but i want max
* i dont get the records where kwdpar=3

[AccessPonderer]

Can you tell me what these two tables contain: E1_Emp001_3 and E1_Emp016_3?

You join tables together on common fields. If I had a table called username and a table called person:

Username:
Id ¦ loginName ¦ personId
-------------------------------------
1 ¦ smithj ¦ 2
2 ¦ Jonesa ¦ 1


Person:
Id ¦ forename ¦ surname
-------------------------------------
1 ¦ Andrea ¦ Jones
2 ¦ John ¦ Smith


Try settings up those two tables and look at the difference between
SELECT username.*, person.* FROM person, username

SELECT username.*, person.* FROM person JOIN username ON username.personId = person.Id


Is the software you bought built in access?

[leite333]

The tables contain many fields but these i am using

E1_Emp001_3 (Company,City_1,Address_1,Address_2,Tel_1,Tel_2,Kw dPwliti,Kwd,OnomaPwliti,KwdPoliti,Ipol,SinTzirou)
E1_Emp016_3
(KwdPar,DatePar,TimePar,KwdPel=E1_Emp001_3.Kwd)


Well yes the database is in access..... i just wanna make a crystal report but i need first to fix the query.
There is another table E1_Emp051_3 (Kwd=Emp1_003.KwdPwliti,FullName=OnomaPwliti) but i tried not use it because the first table already has the fields i need.
The result i wanna get must be ordered by city_1 and OnomaPwliti and take first ofcourse city_1 and OnomaPwliti and
Company,Tel_1,Tel_2,Ipol,SinTzirou and finally a new field Mesos for every record wich will count the difference between today and most recently day that kwdpar is 1 or 3.
The idea is to take from every city and every seller the customers(same city have 2-3 or more different sellers) that owe me money. The field Mesos is used to see when they pay me last time(days count).

[AccessPonderer]

I'm sorry if this is no use, try entering the from and where statements

Select city_1, OnomaPwliti, Company,Tel_1,Tel_2,Ipol,SinTzirou, min(DATEDIFF(DAY,DATEPAR,GETDATE())) AS MESOS
From
where
group by city_1, OnomaPwliti, Company,Tel_1,Tel_2,Ipol,SinTzirou

[leite333]

Ok i think it work the finally query is:

Select city_1, OnomaPwliti, Company,Tel_1,Tel_2,ypol,SinTzirou, min(DATEDIFF(DAY,DATEPAR,GETDATE())) AS MESOS

From
E1_Emp001_3 join E1_Emp016_3 on E1_EMP016_3.kWDPEL=E1_Emp001_3.KWD
WHERE
E1_Emp001_3.YPOL<>0
AND
E1_Emp016_3.KWDPar=1 OR E1_Emp016_3.KWDPar=3

group by city_1, OnomaPwliti, Company,Tel_1,Tel_2,ypol,SinTzirou

I will make some tests and see if its all right.... Something more i wanna ask but its not so important.. I would like to get a new field in start A/A wich will count the customers for every city and seller.
* But when a customer of seller has ipoloipo 0 then count it too but leave a gap at numbers

for example: City:New York, Seller: Al Pacino
A/A Tel1 Tel2 Company Mesos Ipoloipo SinTzirou
1. 1234568, 2939283, Tommy Lee Jones, 10, 100, 300
2. 1234562, 2936753, Maria Jones, 0, 20, 200
4. 1234563, 2939253, Maria Ted Jones, 2, 40, 200

If you see is missing 3. For this seller and that city there is a customer that has ipoloipo 0 (doesnt owe) and we dont need it.

When the city or the seller will change the A/A will start again.

This i am asking its not so important and i think its meanless but i am curius how will be something like that if can be

[AccessPonderer]

The easiest way to do this is to join the output of two queries or a table and a query

If you had a query like

SELECT companyId, COUNT(OrderId) AS OrderCount
FROM CompanyOrder
GROUP BY companyId

showing the count of orders for each company


And a table of all companies regardless of whether they have ordered or not:
SELECT Id
From company


You could do this:
SELECT Company.*, iif(isnull(OrderCount), 0, OrderCount)
FROM company
LEFT JOIN
(SELECT companyId, COUNT(OrderId) AS OrderCount
FROM CompanyOrder
GROUP BY companyId
) AS orders on orders.CompanyId = company.Id


This joins the company table onto the output of the query, if there isn't a result for the query then 0 is shown. These are known as correlated subqueries.

With your other problem, if it doesn't work exactly as I said then you might need a different subquery. Google correlated subqueries or ask some further questions on here. pm me if you want I'm happy to help.

[leite333]

I am little confused...
So you say under my first query i will make a new query??????? that will get the number i want and join it with the previus results??????

[AccessPonderer]

Exactly that!

[AccessPonderer]

Actually. I don't think it is called a 'correlated subquery', just a 'subquery'

[leite333]

Yes but with that i get the result from the firstquery??? I think i miss something.. First i made
Select
Kwd,count(PelId) AS Code
from E1_Emp001_3
group by kwd and then

Select
KwdPel
from E1_Emp016_3

After i put my big query. but first i get always the result of the first query............. and also iif(isnull(Code), 0, Code) this says need 2 arguments