Columns not calculating - 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

Columns not calculating

Author

Topic

stwp86
Starting Member

42 Posts

Posted - 2012-12-18 : 16:05:32

Hey Everyone . . .

Any idea why I am only receiving 0 for all of the % Transaction columns in the following query?


SELECT 
   A.Client_Mnemonic,
   A.User_Name,
   A.Group_Type,
   A.BaselineAvgTime ,
   A.TrendingAvgTime,
   ((A.BaselineAvgTime - A.TrendingAvgTime)/A.BaselineAvgTime) as "PercentImprovement",
   (B.BaselineTotalCount/D.BaselineDenominator) AS "BaselineTransaction%",
   (C.PostEngagementTotalCount/E.PostDenominator) AS "PostTransaction%",
   ((B.BaselineTotalCount+C.PostEngagementTotalCount)/(D.BaselineDenominator+E.PostDenominator)) AS "TotalTransaction%",
   B.BaselineTotalCount+C.PostEngagementTotalCount AS "TransactionTotalCount",
   B.BaselineTotalCount,
   C.PostEngagementTotalCount,
   D.BaselineDenominator,
   E.PostDenominator
FROM
   (
      SELECT Distinct
         Client_Mnemonic,
         User_Name,
         Group_Type, 
         Round(SUM(CASE WHEN (WorkWeek >= '06/18/2012' AND WorkWeek <= '07/13/2012') THEN TotalTime ELSE NULL END) OVER (PARTITION BY Client_Mnemonic, User_Name, Group_Type)/SUM(CASE WHEN (WorkWeek >= '06/18/2012' AND WorkWeek <= '07/13/2012') THEN TotalCount ELSE NULL END) OVER (PARTITION BY Client_Mnemonic, User_Name, Group_Type),2) AS 'BaselineAvgTime',
         Round(SUM(CASE WHEN (WorkWeek >= '11/19/2012' AND WorkWeek <= '12/07/2012') THEN TotalTime ELSE NULL END) OVER (PARTITION BY Client_Mnemonic, User_Name, Group_Type)/SUM(CASE WHEN (WorkWeek >= '11/19/2012' AND WorkWeek <= '12/07/2012') THEN TotalCount ELSE NULL END) OVER (PARTITION BY Client_Mnemonic, User_Name, Group_Type),2) AS 'TrendingAvgTime'
      FROM
         (
            SELECT 
               *
            FROM
               (
                  SELECT 
                     wo.Client_Mnemonic,
                     wo.User_Name,
                     Group_Type,
                     SUM(wo.Duration) AS 'TotalTime',
                     COUNT(wo.Duration) AS 'TotalCount', 
                     CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) AS "WorkWeek"
                  FROM 
   		           Workflow_Data AS wo
		        WHERE
   			      (wo.Client_Mnemonic = 'XXXX_XX') AND
   			      (wo.Group_Type = 'Pilot 1') AND
   			      (wo.Duration <= 300) AND
   			      (wo.Definition IN 
                                 (
                                    'Orders - Search',
                                    'Orders - TEST',
                                    'Orders - Favorites',
                                    'Orders - Folders'
                                 )) AND
   			      datepart(dw, wo.Start_Time) not in (1,7) 
		        GROUP BY 
   		          wo.Client_Mnemonic, 
   			      wo.User_Name,
   			      Group_Type,
   			      CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101)
	          )q
         )r
   )A
INNER JOIN
   (
      SELECT 
         wo.Client_Mnemonic,
         wo.User_Name,
         wo.Group_Type,
         COUNT(wo.Duration) AS 'BaselineTotalCount'
      FROM 
   	    Workflow_Data AS wo
      WHERE
   	    (wo.Client_Mnemonic = 'XXXX_XX') AND
   	    (wo.Group_Type = 'Pilot 1') AND
   	    (wo.Duration <= 300) AND
   	    (wo.Definition IN
               (
                  'Orders - Search',
                  'Orders - TEST',
                  'Orders - Favorites',
                  'Orders - Folders'
               )) AND
   	    datepart(dw, wo.Start_Time) not in (1,7) AND
		CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) BETWEEN '06/18/2012' AND '07/13/2012'
	 GROUP BY 
   	    wo.Client_Mnemonic, 
   	    wo.User_Name,
   	    wo.Group_Type
   )B    
      ON 
         A.Client_Mnemonic = B.Client_Mnemonic and A.User_Name = B.User_Name and A.Group_Type = B.Group_Type  
INNER JOIN
   (
      SELECT 
         wo.Client_Mnemonic,
         wo.User_Name,
         wo.Group_Type,
         COUNT(wo.Duration) AS 'PostEngagementTotalCount'
      FROM 
   	    Workflow_Data AS wo
      WHERE
   	    (wo.Client_Mnemonic = 'XXXX_XX') AND
   	    (wo.Group_Type = 'Pilot 1') AND
   	    (wo.Duration <= 300) AND
   	    (wo.Definition IN
               (
                  'Orders - Search',
                  'Orders - TEST',
                  'Orders - Favorites',
                  'Orders - Folders'
               )) AND
   	    datepart(dw, wo.Start_Time) not in (1,7) AND
		CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) BETWEEN '11/19/2012' AND '12/07/2012'
	 GROUP BY 
   	    wo.Client_Mnemonic, 
   	    wo.User_Name,
   	    wo.Group_Type
   )C   
      ON 
         B.Client_Mnemonic = C.Client_Mnemonic and B.User_Name = C.User_Name and B.Group_Type = C.Group_Type 
INNER JOIN
   (
      SELECT 
         wo.Client_Mnemonic,
         wo.Group_Type,
         COUNT(wo.Duration) AS 'BaselineDenominator'
      FROM 
   	    Workflow_Data AS wo
      WHERE
   	    (wo.Client_Mnemonic = 'XXXX_XX') AND
   	    (wo.Group_Type = 'Pilot 1') AND
   	    (wo.Duration <= 300) AND
   	    (wo.Definition IN
               (
                  'Orders - Search',
                  'Orders - TEST',
                  'Orders - Favorites',
                  'Orders - Folders'
               )) AND
   	    datepart(dw, wo.Start_Time) not in (1,7) AND
		CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) BETWEEN '06/18/2012' AND '07/13/2012'
	 GROUP BY 
   	    wo.Client_Mnemonic, 
   	    wo.Group_Type
   )D  
      ON 
         C.Client_Mnemonic = D.Client_Mnemonic and C.Group_Type = D.Group_Type
INNER JOIN
   (
      SELECT 
         wo.Client_Mnemonic,
         wo.Group_Type,
         COUNT(wo.Duration) AS 'PostDenominator'
      FROM 
   	    Workflow_Data AS wo
      WHERE
   	    (wo.Client_Mnemonic = 'XXXX_XX') AND
   	    (wo.Group_Type = 'Pilot 1') AND
   	    (wo.Duration <= 300) AND
   	    (wo.Definition IN
               (
                  'Orders - Search',
                  'Orders - TEST',
                  'Orders - Favorites',
                  'Orders - Folders'
               )) AND
   	    datepart(dw, wo.Start_Time) not in (1,7) AND
		CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)), 101) BETWEEN '11/19/2012' AND '12/07/2012'
	 GROUP BY 
   	    wo.Client_Mnemonic, 
   	    wo.Group_Type
   )E  
      ON 
         D.Client_Mnemonic = E.Client_Mnemonic and D.Group_Type = E.Group_Type
WHERE
  ((A.BaselineAvgTime - A.TrendingAvgTime)/A.BaselineAvgTime) IS NOT NULL
Order By 
   PercentImprovement desc

Here are some of the results:

BaseTrans% PostTrans% TotalTrans% TransTotalCount BaseTotalCount EngTotalCount BaseDenom PostDenomi
0 0 0 57 1 56 979 1341
0 0 0 239 129 110 979 1341

Is it some kind of conversion that I am missing?

Thanks!

Travis

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-18 : 16:17:44

This may be because of integer division. Change it as follows - I am showing one example


....
(B.BaselineTotalCount*1.0/D.BaselineDenominator) AS "BaselineTransaction%",
...

stwp86
Starting Member

42 Posts

Posted - 2012-12-18 : 16:23:22

Man . . . I thought about that and when I did it I put it on the denominator instead of the numerator. Thanks, you pointed me to the correct location and solved my problem.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-18 : 16:31:33

I see so many inner joins just because you want it for different date range. Can't it be done in one join with case logic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources