Merge queries
-
Dear all,
I have a simple table like below:
DECLARE @tblTemp TABLE
(
Place_ID int,
ReceivedDate SMALLDATETIME
);
INSERT @tblTemp
SELECT 1, '2011-07-15' UNION ALL
SELECT 1, '2011-07-19' UNION ALL
SELECT 1, '2011-07-20' UNION ALL
SELECT 2, '2011-07-18' UNION ALL
SELECT 2, '2011-07-19' UNION ALL
SELECT 3, '2011-07-14'UNION ALL
SELECT 4,NULL
--SELECT * FROM @tblTemp
--All available tests
SELECT Place_ID, COUNT(Place_ID) AS 'AllAvai',ReceivedDate
FROM @tblTemp
WHERE ReceivedDate - GETDATE() >'1899-12-31'
GROUP BY Place_ID,ReceivedDate
--All today available tests
SELECT Place_ID, COUNT(Place_ID) AS 'TodayAvai',ReceivedDate
FROM @tblTemp
WHERE (ReceivedDate - GETDATE() > '1899-12-31' AND ReceivedDate - GETDATE() < '1900-01-01')
GROUP BY Place_ID,ReceivedDate
--All tomorrow available tests
SELECT Place_ID, COUNT(Place_ID) AS 'TomorrowAvai',ReceivedDate
FROM @tblTemp
WHERE (ReceivedDate - GETDATE() > '1900-01-01' AND ReceivedDate - GETDATE() < '1900-01-02')
GROUP BY Place_ID,ReceivedDate
The requirement with me is to show out 3 information that I also included in SQL code:
- All available tests
- Today available tests
- Tomorrow available tests
I got this by using 3 queries, but it definitely should be better if all the information is put into a View.
There has the statistic for each Place_ID like:
Place_ID | AllAvai | Today | Tomorrow
I appreciate for any help.
-
SELECT Place_ID,SUM(CASE WHEN ReceivedDate - GETDATE() >'1899-12-31' THEN 1 ELSE 0 END) AS 'AllAvai',
SUM(CASE WHEN ReceivedDate - GETDATE() > '1899-12-31' AND ReceivedDate - GETDATE() < '1900-01-01' THEN 1 ELSE 0 END) AS 'Today',
SUM(CASE WHEN ReceivedDate - GETDATE() > '1900-01-01' AND ReceivedDate - GETDATE() < '1900-01-02' THEN 1 ELSE 0 END) AS 'Tomorrow',
ReceivedDate
FROM @tblTemp
GROUP BY Place_ID,ReceivedDate
ORDER BY Place_ID,ReceivedDate;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
How about this:
SELECT Place_ID
, ReceivedDate
, COUNT( case when ReceivedDate >= DATEADD(DD, DATEDIFF(dd,0, GETDATE()) ,0)
AND ReceivedDate < DATEADD(DD, DATEDIFF(dd,0, GETDATE()) + 1,0) then Place_ID else NULL end ) AS 'TodayAvai'
, COUNT( case when ReceivedDate >= DATEADD(DD, DATEDIFF(dd,0, GETDATE()) + 1,0)
AND ReceivedDate < DATEADD(DD, DATEDIFF(dd,0, GETDATE()) + 2,0) then Place_ID else NULL end ) AS 'TomorrowAvai'
, COUNT(Place_ID) AS 'AllAvai'
FROM @tblTemp
WHERE ReceivedDate >= DATEADD(DD, DATEDIFF(dd,0, GETDATE()),0) -- > today 00:00:00.000
GROUP BY Place_ID
, ReceivedDate
ORDER BY ReceivedDate
, Place_ID;
btw don't put functions on columns in where clauses so indexes can be used optimally.
I know operator folding some times can do nice things, but anyway.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes, thanks Mark. Its what I need, just remove the parameter 'ReceivedDate'.
Place_ID AllAvai Today Tomorrow1201
2211
3000
4000
Hi Alzdba,
I'm taking time on functions DATEADD and DATEDIFF. Yes, really I also feel my subtraction way looks ridiculous.
Sorry for lack of my knowledge but could you explain me more about your comment:
don't put functions on columns in where clauses so indexes can be used optimally.
Finally, I just little wonder that your Where condition prevents the result from showing all PlaceIDs.
-
hoanglong88i (7/18/2011)
Yes, thanks Mark. Its what I need, just remove the parameter 'ReceivedDate'.Place_ID AllAvai Today Tomorrow1201
2211
3000
4000
Hi Alzdba,
I'm taking time on functions DATEADD and DATEDIFF. Yes, really I also feel my subtraction way looks ridiculous.
Sorry for lack of my knowledge but could you explain me more about your comment:
don't put functions on columns in where clauses so indexes can be used optimally.
Finally, I just little wonder that your Where condition prevents the result from showing all PlaceIDs.
No problem.
The whole hassle with the datediff/dateadd is to get to your date with time 00:00:00.000.
That should be way faster than the convert-alternatives.
What I mean with avoid functions on your columns in a where clause is :
You were doing a :
ReceivedDate - GETDATE() > '1899-12-31'
AND ReceivedDate - GETDATE() < '1900-01-01'
Chances are the engine cannot fold this condition, so it needs to actually perform the substract operation to your column ReceivedDate.
Because of that, if there were an index on your column ReceivedDate, that index isn't going to be used in the most optimal way ( i.e. index seek ), but if used at all it would be an index scan.
When altering the where clause as I showed, chances are this index would be used in the most optimal way.
So, as a general rule of thumb, don't put any functions on the column itself in a where clause, unless you really have no alternative.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks Al for your explaination 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply