Click here to Skip to main content
14,550,260 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi -

I have two queries that I'm looking to rewrite into one but not that good with sql.

Ideally what I am looking for would be an out put like

pastDueRepl | pastDueInsp
46----------- 65

here are the two queries

SELECT 
	count(*) as pastDueRepl	
FROM TBLPTS_APPDATA 
WHERE APPV_PTSSTATUS = '2' 		
	AND (APPD_NEXTREPLDATE IS NOT NULL) 	
	AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()
	
SELECT 
	count(*) as pastDueInsp	
FROM TBLPTS_APPDATA 
WHERE APPV_PTSSTATUS = '2' 		
	AND (APPD_NEXTINSPDATE IS NOT NULL) 
	AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()	


Thanks
Posted
Comments
Richard Deeming 27-Aug-15 16:07pm
   
Both queries have a APPD_NEXTINSPDATE between ... clause. Is that deliberate, or should the first one be comparing NEXTREPLDATE instead?
Maciej Los 27-Aug-15 16:26pm
   
I do not see any different between queries...
Richard Deeming 28-Aug-15 8:18am
   
The first one checks whether APPD_NEXTREPLDATE is not null; the second checks APPD_NEXTINSPDATE.

Otherwise, they're identical.
Maciej Los 28-Aug-15 8:26am
   
Got it. Thank you, Richard. As old man says: you can watch but you can't see...
Rate this:
Please Sign up or sign in to vote.

Solution 2

I would do that this way:
SELECT SUM(CASE WHEN APPD_NEXTREPLDATE IS NOT NULL THEN 1 ELSE 0 END) AS pastDueRepl,
        SUM(CASE WHEN APPD_NEXTINSPDATE IS NOT NULL THEN 1 ELSE 0 END) AS pastDueInsp
FROM TBLPTS_APPDATA
WHERE APPV_PTSSTATUS = '2' AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()


Note: it should be much quicker then method provided in solution 1, because a dataset is filtered only once.
   
v2
Comments
Andy Lanng 28-Aug-15 9:53am
   
Actually, it doesn't get searched twice (more like 1.1 times). CTE's do clever things to optimize the query before they are actually executed. That being said, this is still about 20% more efficient (and a little neater :P)
5*
Maciej Los 28-Aug-15 10:22am
   
Thank you, Andy.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Might not be the most effective way but this works
WITH CTE1 AS
(
    SELECT
        count(star) as pastDueRepl -- asterisk is messing up format
    FROM TBLPTS_APPDATA
    WHERE APPV_PTSSTATUS = '2'
        AND (APPD_NEXTREPLDATE IS NOT NULL)
        AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()
), CTE2 AS
(
    SELECT
        count(star) as pastDueInsp  -- asterisk is messing up format
    FROM TBLPTS_APPDATA
    WHERE APPV_PTSSTATUS = '2'
        AND (APPD_NEXTINSPDATE IS NOT NULL)
        AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()
)
SELECT * FROM CTE1, CTE2
   
v4
Comments
Troy Bryant 27-Aug-15 14:17pm
   
originally had thought of using a cte but this is only 2 selects I have about 3 more select statements that will populate a quick stats table. I just wondering the performance issues using cte. But yes thanks for the idea if all else fails this will be what i'm left with
CHill60 27-Aug-15 18:40pm
   
Another alternative may be to put the results into a temporary table and do some sort of PIVOT maybe
Rate this:
Please Sign up or sign in to vote.

Solution 3

More compact and faster SQL. Even we don't need CASE statement because in the given scenario because when you specify column name in count function then it automatically ignore null values.


Select COUNT(APPD_NEXTREPLDATE) AS pastDueRepl,
COUNT(APPD_NEXTINSPDATE) AS pastDueInsp
FROM  TBLPTS_APPDATA
WHERE APPV_PTSSTATUS = '2' AND APPD_NEXTINSPDATE between DATEADD(Day,-30,GETDATE()) and GETDATE()
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100