Problem with 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

General SQL Server Forums

New to SQL Server Programming

Problem with query

Author

Topic

Dennes
Starting Member

4 Posts

Posted - 2006-06-15 : 08:44:54

Hi,
I have a question about a query i'm trying to get right.
Please bear with me, i'm just getting started with this sql stuff :-)
here's the thing:
I have 2 tables, Akties and Klanten.
Klanten contains info about clients (name, address etc.), Akties contains info about on-site calls to those clients (time worked, date, consultant etc.)

Now i want to select all the actions (Akties) per client (Klanten.Klantnaam) and cumulate the worked hours (Akties.eenheden) per client, even for those who didn't have an action, per month

This works:

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) FROM Klanten LEFT OUTER JOIN akties ON klanten.klantnaam=akties.klant GROUP BY klantnaam

It shows all the akties for all the clients, cumulates the eenheden (worked hours) and shows NULL for the clients without akties. Great.

Now i want to be able to perform the same thing per month. So i want to see all the clients' actions for a specific month.
If i use this (all akties for month may):

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) FROM Klanten LEFT OUTER JOIN akties ON klanten.klantnaam=akties.klant WHERE DATEPART(m, Datum) = 5 GROUP BY klantnaam

It also works, but i don't get to see the clients without actions (NULL) anymore. And i also want to see those.
How do i achieve this? I read about CROSS JOIN, but really don't seem to understand the 10+ line examples i googled :-)

Thanks,
Dennes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 08:53:55

You could use

WHERE (MONTH(Datum) = 5 OR Datum IS NULL)

But I don't recommend it.

Peter Larsson
Helsingborg, Sweden

Dennes
Starting Member

4 Posts

Posted - 2006-06-15 : 08:59:42

Why do you recommend against this?

Dennes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 10:52:10

quote:
SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) FROM Klanten LEFT OUTER JOIN akties ON klanten.klantnaam=akties.klant WHERE DATEPART(m, Datum) = 5 GROUP BY klantnaam

It also works, but i don't get to see the clients without actions (NULL) anymore. And i also want to see those.

Try this

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) 
FROM   Klanten LEFT OUTER JOIN akties 
ON     Klanten.klantnaam = akties.klant 
WHERE AND  DATEPART(m, Datum) = 5 
GROUP BY Klanten.klantnaam

"DATEPART(m, Datum) = 5 "
Do you need data for month of May for any year or a particular year.
If it is for month of May 2006, then you should do this

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) 
FROM   Klanten LEFT OUTER JOIN akties 
ON     Klanten.klantnaam = akties.klant 
AND    Datum >= '20060501'
AND    Datum <= '20060531' 
GROUP BY Klanten.klantnaam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 00:48:48

quote:
Originally posted by Dennes

Why do you recommend against this?

Dennes

Depending on table layout and schema, you could end up with unexpected result.
However, most ot the times the query will produce the desired result.

Test, try and go on.

Peter Larsson
Helsingborg, Sweden

Dennes
Starting Member

4 Posts

Posted - 2006-06-16 : 06:25:03

Could i also use this to select a month in a particular year?

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) 
FROM   Klanten LEFT OUTER JOIN akties 
ON     Klanten.klantnaam = akties.klant 
AND  DATEPART(m, Datum) = 5 AND DATEPART(y, Datum) = 2006
GROUP BY Klanten.klantnaam

<edit>
Why don't i just try that before i post.. Duh! :-)

quote:
Originally posted by khtan

quote:
SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) FROM Klanten LEFT OUTER JOIN akties ON klanten.klantnaam=akties.klant WHERE DATEPART(m, Datum) = 5 GROUP BY klantnaam

It also works, but i don't get to see the clients without actions (NULL) anymore. And i also want to see those.

Try this

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden))
FROM Klanten LEFT OUTER JOIN akties
ON Klanten.klantnaam = akties.klant
~~WHERE~~ AND DATEPART(m, Datum) = 5
GROUP BY Klanten.klantnaam

"DATEPART(m, Datum) = 5 "
Do you need data for month of May for any year or a particular year.
If it is for month of May 2006, then you should do this

SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden))
FROM Klanten LEFT OUTER JOIN akties
ON Klanten.klantnaam = akties.klant
AND Datum >= '20060501'
AND Datum <= '20060531'
GROUP BY Klanten.klantnaam

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 08:23:56

quote:

Could i also use this to select a month in a particular year?


SELECT Klanten.klantnaam, SUM(Convert(int,Akties.eenheden)) 
FROM   Klanten LEFT OUTER JOIN akties 
ON     Klanten.klantnaam = akties.klant 
AND  DATEPART(m, Datum) = 5 AND DATEPART(y, Datum) = 2006
GROUP BY Klanten.klantnaam

Yes. But it is not efficient compare to the method i use. As it discourage the use of index on column Datum.

Dennes
Starting Member

4 Posts

Posted - 2006-06-16 : 08:41:35

OK,
So selecting on a column creates an index, which you shouldn't have on a date field?
Is this a performance issue thing? I don't worry about that too much right now, since it's only a small db/project and i'm really not knowledgeable enough to know or see the difference (but willing to learn).
I could also use your way, but that requires a little editing on the variables i use to pass the date to the select query.

Thanks!

Dennes

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-06-16 : 13:28:03

quote:
Originally posted by Dennes

OK,
So selecting on a column creates an index, which you shouldn't have on a date field?

No, that's not it. Selecting on a column will use an index if it exists; it doesn't create one.

khtan was saying that using the DATEPART() function in the WHERE clause will keep SQL Server from using any existing index on the column.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources