SELECT Problem..... - 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 2005 Forums

Transact-SQL (2005)

SELECT Problem.....

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-05-11 : 08:29:44

Hi all i am having problems with the query below, basically i need to return result for the current year and month
for certain users.

the query is fine if i only select one user (Name) but if i select two or more then the month and year clause is
ignored.

Any Ideas??

thanks in advance for looking.

--*****************************************************************
--*****************************************************************
--BEGIN
--*****************************************************************
--*****************************************************************

SELECT dbo.TableA.*

FROM dbo.TableA INNER JOIN
dbo.TableB ON dbo.TableA.id = dbo.TableB.id
WHERE Month(TableB.date)= DATEPART(Month, GETDATE())
AND Year (TableB.date)= DATEPART(Year, GETDATE())

AND dbo.TableB.Name LIKE '%A%' OR dbo.TableB.Name LIKE '%Z%'
OR dbo.TableB.Name LIKE '%B%' OR dbo.TableB.Name LIKE '%1%'
OR dbo.TableB.Name LIKE '%C%' OR dbo.TableB.Name LIKE '%2%'
OR dbo.TableB.Name LIKE '%D%' OR dbo.TableB.Name LIKE '%3%'
OR dbo.TableB.Name LIKE '%E%' OR dbo.TableB.Name LIKE '%4%'
OR dbo.TableB.Name LIKE '%F%' OR dbo.TableB.Name LIKE '%5%'
OR dbo.TableB.Name LIKE '%G%' OR dbo.TableB.Name LIKE '%6%'
OR dbo.TableB.Name LIKE '%H%' OR dbo.TableB.Name LIKE '%7%'
OR dbo.TableB.Name LIKE '%I%' OR dbo.TableB.Name LIKE '%8%'
OR dbo.TableB.Name LIKE '%J%' OR dbo.TableB.Name LIKE '%9%'
OR dbo.TableB.Name LIKE '%K%' OR dbo.TableB.Name LIKE '%10%'
OR dbo.TableB.Name LIKE '%L%' OR dbo.TableB.Name LIKE '%11%'
OR dbo.TableB.Name LIKE '%M%' OR dbo.TableB.Name LIKE '%12%'
OR dbo.TableB.Name LIKE '%N%' OR dbo.TableB.Name LIKE '%13%'
OR dbo.TableB.Name LIKE '%O%' OR dbo.TableB.Name LIKE '%14%'
OR dbo.TableB.Name LIKE '%P%' OR dbo.TableB.Name LIKE '%15%'
OR dbo.TableB.Name LIKE '%Q%' OR dbo.TableB.Name LIKE '%16%'
OR dbo.TableB.Name LIKE '%R%' OR dbo.TableB.Name LIKE '%17%'
OR dbo.TableB.Name LIKE '%S%' OR dbo.TableB.Name LIKE '%18%'
OR dbo.TableB.Name LIKE '%T%' OR dbo.TableB.Name LIKE '%19%'
OR dbo.TableB.Name LIKE '%U%' OR dbo.TableB.Name LIKE '%20%'
OR dbo.TableB.Name LIKE '%W%' OR dbo.TableB.Name LIKE '%21%'
OR dbo.TableB.Name LIKE '%X%' OR dbo.TableB.Name LIKE '%22%'
OR dbo.TableB.Name LIKE '%Y%'

--*****************************************************************
--*****************************************************************
--END
--*****************************************************************
--*****************************************************************

Kind Regards

Pete.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 08:32:33

[code]
AND
(
dbo.TableB.Name LIKE '%A%' OR dbo.TableB.Name LIKE '%Z%'
OR dbo.TableB.Name LIKE '%B%' OR dbo.TableB.Name LIKE '%1%'
OR . . .
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-11 : 08:33:03

Try

SELECT dbo.TableA.*

FROM dbo.TableA INNER JOIN
dbo.TableB ON dbo.TableA.id = dbo.TableB.id
WHERE Month(TableB.date)= DATEPART(Month, GETDATE())
AND Year (TableB.date)= DATEPART(Year, GETDATE())

AND (dbo.TableB.Name LIKE '%A%' OR dbo.TableB.Name LIKE '%Z%'
OR dbo.TableB.Name LIKE '%B%' OR dbo.TableB.Name LIKE '%1%'
OR dbo.TableB.Name LIKE '%C%' OR dbo.TableB.Name LIKE '%2%'
OR dbo.TableB.Name LIKE '%D%' OR dbo.TableB.Name LIKE '%3%'
OR dbo.TableB.Name LIKE '%E%' OR dbo.TableB.Name LIKE '%4%'
OR dbo.TableB.Name LIKE '%F%' OR dbo.TableB.Name LIKE '%5%'
OR dbo.TableB.Name LIKE '%G%' OR dbo.TableB.Name LIKE '%6%'
OR dbo.TableB.Name LIKE '%H%' OR dbo.TableB.Name LIKE '%7%'
OR dbo.TableB.Name LIKE '%I%' OR dbo.TableB.Name LIKE '%8%'
OR dbo.TableB.Name LIKE '%J%' OR dbo.TableB.Name LIKE '%9%'
OR dbo.TableB.Name LIKE '%K%' OR dbo.TableB.Name LIKE '%10%'
OR dbo.TableB.Name LIKE '%L%' OR dbo.TableB.Name LIKE '%11%'
OR dbo.TableB.Name LIKE '%M%' OR dbo.TableB.Name LIKE '%12%'
OR dbo.TableB.Name LIKE '%N%' OR dbo.TableB.Name LIKE '%13%'
OR dbo.TableB.Name LIKE '%O%' OR dbo.TableB.Name LIKE '%14%'
OR dbo.TableB.Name LIKE '%P%' OR dbo.TableB.Name LIKE '%15%'
OR dbo.TableB.Name LIKE '%Q%' OR dbo.TableB.Name LIKE '%16%'
OR dbo.TableB.Name LIKE '%R%' OR dbo.TableB.Name LIKE '%17%'
OR dbo.TableB.Name LIKE '%S%' OR dbo.TableB.Name LIKE '%18%'
OR dbo.TableB.Name LIKE '%T%' OR dbo.TableB.Name LIKE '%19%'
OR dbo.TableB.Name LIKE '%U%' OR dbo.TableB.Name LIKE '%20%'
OR dbo.TableB.Name LIKE '%W%' OR dbo.TableB.Name LIKE '%21%'
OR dbo.TableB.Name LIKE '%X%' OR dbo.TableB.Name LIKE '%22%'
OR dbo.TableB.Name LIKE '%Y%'
)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-11 : 08:33:54

Tan is always fast

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 08:34:35

for better performance, the following


Month(TableB.date)= DATEPART(Month, GETDATE())
AND Year (TableB.date)= DATEPART(Year, GETDATE())

can be changed to


TableB.date >= dateadd(month, datediff(month, 0, getdate()), 0),
AND TableB.date <= dateadd(month, datediff(month, 0, getdate()) + 1, -1)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 08:35:23

quote:
Originally posted by madhivanan

Tan is always fast

Madhivanan

Failing to plan is Planning to fail

Just slightly

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-11 : 08:38:48

More accurately

TableB.date >= dateadd(month, datediff(month, 0, getdate()), 0),
AND TableB.date < dateadd(month, datediff(month, 0, getdate()) + 1, 0)

Madhivanan

Failing to plan is Planning to fail

petek
Posting Yak Master

192 Posts

Posted - 2009-05-11 : 08:55:19

Hi All,

Thank you soooooooo much for your help, the code worled a treat.

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources