October 21, 2015 at 10:24 am
I have a query I'm using in a report. It runs fine until I add the code with a declared variable called @Total. I'm thinking it has something to do with how Im trying to use3 the variable in the query before the UNION ALL.
Any help is appreciated, Thinking it has to do with using the variable in the query at the bottom before the UNION ALL
[highlight=#ffff11]Declare @Total as int[/highlight]
;with LastSevenDays as (
SELECT [GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) >= convert(varchar(30),getdate()-7,101)
),
EightToFourteenDays as
(
SELECT
[GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) BETWEEN convert(varchar(30),getdate()-14,101) and convert(varchar(30),getdate()-8,101)
),
FifteenTwentyOne as
(
SELECT
[GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) BETWEEN convert(varchar(30),getdate()-21,101) and convert(varchar(30),getdate()-15,101)
),
TwentyTwoToTwentyEight as
(
SELECT
[GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) BETWEEN convert(varchar(30),getdate()-28,101) and convert(varchar(30),getdate()-22,101)
),
GTE_29 as
(
SELECT
[GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) <= convert(varchar(30),getdate()-29,101)
),
ClientTotals as
(
SELECT
[GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and (convert(varchar(30),sfh.date_time_Started,101) <= convert(varchar(30),getdate()-29,101)
or convert(varchar(30),sfh.date_time_Started,101) >= convert(varchar(30),getdate()-7,101))
)
[highlight=#ffff11]Select @Total = (Select count(*) from ClientTotals)[/highlight]
SELECT
[GCM_CLIENT_CD] as Client,
HP_CD,
(Select count(*) from LastSevenDays where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as '0-7 days'
, (Select count(*) from EightToFourteenDays where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as '8-14 days'
, (Select count(*) from FifteenTwentyOne where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as '15-21 days'
, (Select count(*) from TwentyTwoToTwentyEight where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as '22-28 days'
, (Select count(*) from GTE_29 where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as '29 days +'
, (Select count(*) from ClientTotals where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD) as 'Client Totals'
, (Select (Select count(*) from ClientTotals where [GCM_CLIENT_CD] = mcr.[GCM_CLIENT_CD] and HP_CD = mcr.HP_CD)/[highlight=#ffff11](Select @Total)[/highlight] ) as '% of Total'
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
where sfh.Submission_File_Type_ID = 17
and [QA_SUBMITTED] is not null
group by [GCM_CLIENT_CD], HP_CD
UNION ALL
Select ''
,'Totals'
,(Select count(*) from LastSevenDays) as '0-7 days'
, (Select count(*) from EightToFourteenDays) as '8-14 days'
, (Select count(*) from FifteenTwentyOne) as '15-21 days'
, (Select count(*) from TwentyTwoToTwentyEight) as '22-28 days'
, (Select count(*) from GTE_29) as '29 days +'
, (Select count(*) from ClientTotals) as 'Client Totals'
, '' as '% of Total'
October 21, 2015 at 10:29 am
Is this a query between your CTEs and your main select? That would make it so that your CTEs don't exist by the time your query is run.
[highlight=#ffff11]Select @Total = (Select count(*) from ClientTotals)[/highlight]
October 21, 2015 at 10:29 am
Looks to me like this would be simpler, faster and easier to maintain as one query, rather than a bunch of UNIONs. Are you open to a rewrite?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2015 at 10:31 am
And why are you converting the dates to VARCHARs?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2015 at 10:32 am
Does this work:
;with LastSevenDays as (
SELECT [GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
inner join [dbo].[G_SUBMISSION_FILES_HEADERS] (NOLOCK) sfh
on mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
inner join [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] (NOLOCK) g
on g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
inner join G_WORKITEM_BATCH_RESULT_EDPS (NOLOCK) wb
on wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
where sfh.Submission_File_Type_ID = 17
and mcr.NPI_SENT is not null
and Isnull(wb.[status],'open') = 'open'
and convert(varchar(30),sfh.date_time_Started,101) >= convert(varchar(30),getdate()-7,101)
)
select count(*) from LastSevenDays
Also, please get rid of NOLOCK
Gerald Britton, Pluralsight courses
October 21, 2015 at 10:33 am
Yeah its between the CTE's and main query. Need that value for some math
October 21, 2015 at 10:38 am
dndaughtery (10/21/2015)
Yeah its between the CTE's and main query. Need that value for some math
Then you need to load that value separately. Your CTEs all cease to exist as soon as the next query is run.
In this case, they exist until you
Select @Total = (Select count(*) from ClientTotals)
And then they are gone. Hence the Error: Invalid object name 'LastSevenDays'.
October 21, 2015 at 10:39 am
dndaughtery (10/21/2015)
Yeah its between the CTE's and main query. Need that value for some math
Then that's the cause of the error. A CTE is only valid for the (single) query after it. That query is your count, so for the next query, the CTE doesn't exist.
Move the count inside the main query, then the main query will be the one which follows the CTE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 1:42 pm
Without actual sample data, I can't ensure that this will work, but it should give you an idea. It reduce the code and the work done.
SELECT [GCM_CLIENT_CD],
HP_CD,
COUNT( CASE WHEN sfh.date_time_Started >= CONVERT(DATE, GETDATE() - 7) THEN 1 END ) AS '0-7 days',
COUNT( CASE WHEN sfh.date_time_Started < CONVERT(DATE, GETDATE() - 7)
AND sfh.date_time_Started >= CONVERT(DATE, GETDATE() - 14) THEN 1 END ) AS '8-14 days',
COUNT( CASE WHEN sfh.date_time_Started < CONVERT(DATE, GETDATE() - 14)
AND sfh.date_time_Started >= CONVERT(DATE, GETDATE() - 21) THEN 1 END ) AS '15-21 days',
COUNT( CASE WHEN sfh.date_time_Started < CONVERT(DATE, GETDATE() - 21)
AND sfh.date_time_Started >= CONVERT(DATE, GETDATE() - 28) THEN 1 END ) AS '22-28 days',
COUNT( CASE WHEN sfh.date_time_Started < CONVERT(DATE, GETDATE() - 28) THEN 1 END ) AS '29 days +',
COUNT( CASE WHEN sfh.date_time_Started < CONVERT(DATE, GETDATE() - 28)
AND sfh.date_time_Started >= CONVERT(DATE, GETDATE() - 7) THEN 1 END ) AS 'Client Totals'
FROM [dbo].[G_MASTER_CODING_RESULTS] AS mcr
INNER JOIN [dbo].[G_SUBMISSION_FILES_HEADERS] AS sfh ON mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
INNER JOIN [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] AS g ON g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
INNER JOIN G_WORKITEM_BATCH_RESULT_EDPS AS wb ON wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
WHERE sfh.Submission_File_Type_ID = 17
AND mcr.NPI_SENT IS NOT NULL
AND ISNULL(wb.[status], 'open') = 'open'
You should also try to remove all those NOLOCK hints that might cause more harm than good. More information in here:
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
October 21, 2015 at 2:42 pm
I would actually rewrite Luis' query even further. You're obscuring the fact that you're calculating the number of days since started. I like to make that as explicit as possible, and simultaneously simplify the code.
SELECT [GCM_CLIENT_CD],
HP_CD,
COUNT( CASE WHEN calc.days_since_started <= 7 THEN 1 END ) AS '0-7 days',
COUNT( CASE WHEN calc.days_since_started > 7
AND calc.days_since_started <= 14 THEN 1 END ) AS '8-14 days',
COUNT( CASE WHEN calc.days_since_started > 14
AND calc.days_since_started <= 21 THEN 1 END ) AS '15-21 days',
COUNT( CASE WHEN calc.days_since_started > 21
AND calc.days_since_started <= 28 THEN 1 END ) AS '22-28 days',
COUNT( CASE WHEN calc.days_since_started > 28 THEN 1 END ) AS '29 days +',
COUNT( CASE WHEN calc.days_since_started > 28
OR calc.days_since_started <= 7 THEN 1 END ) AS 'Client Totals'
FROM [dbo].[G_MASTER_CODING_RESULTS] AS mcr
INNER JOIN [dbo].[G_SUBMISSION_FILES_HEADERS] AS sfh ON mcr.NPI_SENT = sfh.[SUBMISSION_FILES_HEADER_ID]
INNER JOIN [dbo].[temp_GammaWorkItem_Batch_EDPS_RESULTS] AS g ON g.[REFERENCE_ID] = mcr.MASTER_CODING_RESULTS_ID
INNER JOIN G_WORKITEM_BATCH_RESULT_EDPS AS wb ON wb.REFERENCE_ID = mcr.MASTER_CODING_RESULTS_ID
CROSS APPLY (VALUES(DATEDIFF(DAY, sfh.date_time_Started, GETDATE()))) calc(days_since_started)
WHERE sfh.Submission_File_Type_ID = 17
AND mcr.NPI_SENT IS NOT NULL
AND ISNULL(wb.[status], 'open') = 'open'
I also noticed that Luis replaced an OR with an AND, I've changed that back.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply