Calculation Help - 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

Calculation Help

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-08-07 : 09:24:32

Hello again everyone . . .

I have the following query that I am trying to run and attain some utilization numbers. When I run each of the queries individually they return the correct numbers, but when I combine via inner join and try to calculate, I only recieve 0's, any thoughts?

Select n.Client_Mnemonic, n.Numerator/d.Denominator, d.[Work Week], d.Group_Type 
From
(
SELECT count(duration) As "Numerator", w.Client_Mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) as "Work Week", w.Group_Type 
FROM workflow_data as w
WHERE
w.client_mnemonic = 'ABC' and
(
definition = 'Orders - Individual Order Favorites-Folders'  and datepart(dw, w.Start_Time) not in (1,7) or
definition = 'Orders - CareSet Favorites-Folders'  and datepart(dw, w.Start_Time) not in (1,7) or
definition = 'Orders - PowerPlan Favorites-Folders Initiate'  and datepart(dw, w.Start_Time) not in (1,7)
)
GROUP BY client_mnemonic, group_type, 
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)), 101)
)N
Inner Join
(
Select  count(duration) as Denominator,client_mnemonic, 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' and
( 
 definition = 'Orders - Individual Order Search'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order MySearch'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order Favorites-Folders'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order Dx Association'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - CareSet Search'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - CareSet Favorites-Folders'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - PowerPlan Favorites-Folders Initiate'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - PowerPlan Search Initiate'  and datepart(dw, workflow_data.Start_Time) not in (1,7)
) 
 group by client_mnemonic, group_type, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101)
 )D
 On n.[Work Week]=d.[Work Week] and n.Group_Type=d.Group_Type 
 order by n.client_mnemonic, n.group_type, n.[Work Week]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 09:44:17

you should have client_mnemonic also on the join. Also you need small modification as below


Select n.Client_Mnemonic, n.Numerator*1.0/d.Denominator, d.[Work Week], d.Group_Type 
From
(
SELECT count(duration) As "Numerator", w.Client_Mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) as "Work Week", w.Group_Type 
FROM workflow_data as w
WHERE
w.client_mnemonic = 'ABC' and
(
definition = 'Orders - Individual Order Favorites-Folders'  and datepart(dw, w.Start_Time) not in (1,7) or
definition = 'Orders - CareSet Favorites-Folders'  and datepart(dw, w.Start_Time) not in (1,7) or
definition = 'Orders - PowerPlan Favorites-Folders Initiate'  and datepart(dw, w.Start_Time) not in (1,7)
)
GROUP BY client_mnemonic, group_type, 
CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)), 101)
)N
Inner Join
(
Select  count(duration) as Denominator,client_mnemonic, 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' and
( 
 definition = 'Orders - Individual Order Search'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order MySearch'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order Favorites-Folders'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - Individual Order Dx Association'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - CareSet Search'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - CareSet Favorites-Folders'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - PowerPlan Favorites-Folders Initiate'  and datepart(dw, workflow_data.Start_Time) not in (1,7) or
 definition = 'Orders - PowerPlan Search Initiate'  and datepart(dw, workflow_data.Start_Time) not in (1,7)
) 
 group by client_mnemonic, group_type, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101)
 )D
 On n.[Work Week]=d.[Work Week] and n.Group_Type=d.Group_Type and n.client_mnemonic = d.client_mnemonic
 order by n.client_mnemonic, n.group_type, n.[Work Week]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 09:46:24

I am probably repeating what you may already know but, if you are getting no rows in the combined query, that means there are no rows that satisfy the condition:

n.[Work Week] = d.[Work Week] AND n.Group_Type = d.Group_Type

So when you run the individual queries, examine if there are rows that have the same work week and group type combination in both result sets.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 09:47:30

Visakh, in general you are right. But his inner queries both limit the client mnemonic to 'ABC', so I don't know if adding that would fix the problem for this (test?) case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 10:07:16

quote:
Originally posted by sunitabeck

Visakh, in general you are right. But his inner queries both limit the client mnemonic to 'ABC', so I don't know if adding that would fix the problem for this (test?) case.

i saw that
i was not suggesting that will fix the problem

the problem I guess is due to implicit conversion in divsion

that suggestion was to make sure query runs fine when he runs for multiple mnemonics. I'm sure the given query is just what he used for testing one instance of mnemonic

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

stwp86
Starting Member

42 Posts

Posted - 2012-08-07 : 10:59:49

Thanks visakh, the conversion was the issue . . .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:35:48

yep...i know that

make sure you add extra condition on join too if you want this to be run for multiple clients

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources