[Closed] Any SQL language experts in the house?

Fire away - I'm sure there are a few SQL geeks on here (not that good myself mind!).

Not without the question we can't.

depends what the question is!

Just fishing first!

Well, er. I suppose I'll have to paste the thing to explain it. I'm making an SMS collection

I've got:
[b]from SMS_R_System where SystemOUName like "%thcg.net/EU/Gosport Data Center/%Workstations" and (NetbiosName like "%-1%" [/b]..etc

with me so far?

sorry thats just a section of the query, in case you were wondering!

I have one if you want to help

this is my query

SELECT Patient.Provider, Count(Patient.[Date entered]) AS [Patients Assigned], [b]DatePart("y",DateAdd("d",+1,Now()))[/b] AS Expr1
FROM Patient
WHERE (((Right(Left(Patient.[Date entered],5),2))=DatePart("m",(DateAdd("m",-5,Now())))))
GROUP BY Patient.Provider;

The part in bold is where i want to display just the year?

Cheers,
Nezbo

I have tried

DatePart("Y", now())

with no joy...

all I want to do is select from more SystemOUNames.

so.. [b]where SystemOUName like "%thcg.net/EU/Gosport Data Center/%Workstations" or "%thcg.net/EU/Gosport Data Center/%Laptops" and (NetbiosName... [/b] etc.

Can I do that with wildcard values?
Apologies if this is really badly explained!! So you get me drift?

Bugger off Nezbo!

I think I need to do this - [b]where SystemOUName like "%thcg.net/EU/Gosport Data Center/%Workstations" or SystemOUName like "%thcg.net/EU/Gosport Data Center/%Laptops" and (NetbiosName... [/b]

Do I need brackets somewhere?
This is making me sweat.

Brackets round the Or clauses if I were you.

if you past the whole statement we will ahve a better understanding?

🙂

where SystemOUName like [b]([/b]"%thcg.net/EU/Gosport Data Center/%Workstations" or SystemOUName like "%thcg.net/EU/Gosport Data Center/%Laptops"[b])[/b] and (NetbiosName...

just that?

[i]if you past the whole statement we will ahve a better understanding?[/i]

OK. After what mogrips said, will this baby work?

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where (SystemOUName like "%thcg.net/EU/Gosport Data Center/%Workstations" or SystemOUName like "%thcg.net/EU/Gosport Data Center/%Sales laptops" or SystemOUName like "%thcg.net/EU/Gosport Data Center/%Laptops") and (NetbiosName like "%-1%" or NetbiosName like "%-2%" or NetbiosName like "%-3%")

(SystemOUName like "%thcg.net/EU/Gosport Data Center/%Workstations" or SystemOUName like "%thcg.net/EU/Gosport Data Center/%Laptops")

I would start with the brcket movement...

That looks the same as mine? (Which returns nothing)

EDIT: Hang on, looks like it's worked!

Thanks folks!

Nezbo,
[code]DatePart("y", now())[/code] gives you "day of year".

For the year use:
[code]DatePart("yy", now())[/code]
or
[code]DatePart("yyyy", now())[/code]

Assuming this is [url= http://msdn.microsoft.com/en-us/library/ms174420.aspx ]DatePart in Microsoft Transact SQL[/url]

Pretty ugly and inefficient query performing wildcard searches on character fields. But if you're stuck with a poorly designed database schema then what can you do 😀

[i]andywhit - Member
Pretty ugly and inefficient query performing wildcard searches on character fields. But if you're stuck with a poorly designed database schema then what can you do
[/i]

Ugly as hell, but that's Microsoft Active Directory for ya. [/geek=off]

I am useing MS access 😳 yes i know...

but my code is now

SELECT Patient.Provider, Count(Patient.[Date entered]) AS [Patients Assigned]
FROM Patient
WHERE Right(Left(Patient.[Date entered],5),2)=DatePart("m",(DateAdd("m",-5,Now()))) [b]And Right(Patient.[Date entered],2)=FORMAT(Now(),'YY')[/b]
GROUP BY Patient.Provider;

It will just not display any results 🙁

The bolded part is where i am having the problem...

What's with all the Left, Right stuff??

Is Patient.[Date entered] a text string? If so, why isn't it a Date??

I am not to sure how to convert it to a date, because MS Access does not use CONVERT ?

Patient.[Date entered] is a text string.

Right, there is your first problem. Why is something called "[u]Date[/u] entered" being stored as text??

That rather points to a larger problem with your system design. What if "Date entered" is "25/12/2007", "12/25/2007", "2007/12/25", "Monday", "last week" etc etc etc

The application should be dealing with what the user enters. By the time it gets to the database it should be in a sane format.

The problem is that I have inherited the database and there is around 30,000 records so I guess the date format in MS Access Database is going to stop at DD/MM/YYYY 🙁

I know it is not the best database design practised.

I had all sorts of problems with a column called "date" - boy did it screw up SQL, it got its knickers in a twist due to it conflicting with a function.

UPDATE EMP
SET SALARY = '1000000'
WHERE EMP_NAME = 'tommo';

Job Done.

A column called date?! I'm an Oracle developer and had to develop using SQL Server once; was an upsetting experience for me....

You could change the database design pretty easily and migrate the data - it would just depend how much other code expected that column to be text.

But if you're stuck with it..

The easiest way to figure out what's going on here is to try another query:

[code]select Right(Patient.[Date entered],2), FORMAT(Now(),'YY')
from Patient[/code]

..will show you what you are actually trying to compare. The most obvious thing I can see it that I think it should be [code]'yy'[/code] and not [code]'YY'[/code]

SELECT * FROM EMP WHERE EMP_NAME = 'tommo';

EMP_NAME SALARY SALARY_CURRENCY_CODE
tommo 1000000 ZWL

SELECT * FROM CURRENCY WHERE CURRENCY_CODE = 'ZWL';

CURRENCY_CODE DESCRIPTION
ZWL ZIMBABWEAN DOLLAR

Pwned!

Nezbo, if its an Access query you could prolly use CDate(String) in the statement. Then use date functions on the value. Thou probably best to check if the CDate is creating the right 'Date' first.

WAhooooooooooooo got it working with the cDate 🙂 Cheers damitamit 🙂

SELECT Patient.Provider, Count(Patient.[Date entered]) AS [Patients Assigned]
FROM Patient
WHERE DatePart("m",cDate(Patient.[Date entered]))=DatePart("m",(DateAdd("m",-1,Now()))) AND DatePart("yyyy",cDate(Patient.[Date entered]))=DatePart("yyyy",Now())
GROUP BY Patient.Provider;

Cheers,
Nezbo

So is now a good time to point out that this query won't work in January? 🙄

lol, indeed