| Author |
 Topic  |
|
|
stwp86
Starting Member
USA
41 Posts |
 Posted - 08/07/2012 : 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
India
47023 Posts |
Posted - 08/07/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/07/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/07/2012 : 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
India
47023 Posts |
Posted - 08/07/2012 : 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
USA
41 Posts |
Posted - 08/07/2012 : 10:59:49
|
| Thanks visakh, the conversion was the issue . . . |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/07/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Calculation Help
