Where Issue - 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

Where Issue

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-07-30 : 22:50:42

Any idea why the following where clause does not pick up the the client_mnemonic? It ignores the restriction and still prints out everything for all clients in table.

Also, the calculations in this query work when there is only 1 unique client in our table, but as soon as we add in multiple clients it throws the numbers off.

Any thoughts?

SELECT Client_Mnemonic, round((avg(duration) *count(duration)/count(duration)),1) As "Meds Rec - Average Elapsed Time",
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) AS "Work Week", Group_Type
FROM workflow_data
WHERE
client_mnemonic = 'Abc_AA' and
definition = 'Meds Rec - Admission' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Transfer' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
definition = 'Meds Rec - Discharge' and datepart(dw, workflow_data.Start_Time) not in (1,7)
GROUP BY client_mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type
ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101), Group_Type ;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-30 : 22:55:36

this ?


WHERE client_mnemonic = 'Abc_AA' 
and  (
     definition = 'Meds Rec - Admission' and datepart(dw, workflow_data.Start_Time) not in (1,7) or
     definition = 'Meds Rec - Transfer'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
     definition = 'Meds Rec - Discharge' and datepart(dw, workflow_data.Start_Time) not in (1,7)
     )

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

stwp86
Starting Member

42 Posts

Posted - 2012-07-31 : 10:29:55

I believe that worked . . . thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 10:38:34

why not simply put it as


definition in('Meds Rec - Admission' ,'Meds Rec - Transfer','Meds Rec - Discharge' )
and datepart(dw, workflow_data.Start_Time) not in (1,7)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources