Getting the total of 2 queries

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

Getting the total of 2 queries

Author

Topic

chris82
Starting Member

1 Post

Posted - 2011-11-16 : 10:09:18

Sorry I am new to sql so I aplogise in advance if I am not completely clear with what I am trying to achieve.

Currently I have the to queries below which are giving me a count grouped by a date. What I would like to achieve is being able to group the two so that I have a total for each day. For example if on 01/11/2011 the total for query 1 is 5 and query 2 6 it will show as 11.

Query 1:

SELECT
(SELECT
datepart(day, WF_COMPLETE_ON),COUNT(Wflw.WF_RECORD) as Total
FROM
WORKFLOW as Wflw
INNER JOIN LRUC_WORKFLOW_EXTENSIONS as Wfex
ON Wflw.WF_RECORD = Wfex.LRUCW_WF_RECORD

WHERE
Wflw.WF_CATEGORY IN (33, 34, 35,36,37,38,62,64,65,66,94)
AND Wfex.LRUCW_CHANNEL IN ('ScanPartner','Fax')
AND WF_COMPLETE_ON is not NULL
AND WF_COMPLETE_ON between ’01-11-2011’ and ’30-11-2011’ )

Query 2:

(SELECT
datepart(day, TL_SCHEDULED_AT),COUNT(*) as Total
FROM TICKET_LETTER VTL
WHERE
VTL.TL_CODE IN (10031,10033,10106,10107,10291,10292,10293,10294,10295,10296,10599,10600,10601,10602,10603,10604,
10873,10875,10876,10877,10878,10879,10880,10881,10885,10886,10887,10888,10890,10891,10892,10893,
10894,10895,10896,10900,10901,10902,10903,10905,10906,10907,10908,10909,10910,10911,10915,10916,
10917,10918,10920,10921,10922,10923,10924,10925,10926,10930,10931,10932,10933,10935,10936,10937,
10938,10939,10940,10941,10945,10946,10947,10948,10950,10951,10952,10953,10954,10955,10956,10960,
10961,10962,10993,10995,10996,10997,10998,10999,11000,11001,11005,11006,11007,11008,11010,11011,
11012,11013,11014,11015,11016,11020,11021,11022,11177,11178,11179,11180,11181,11182,11183,11184,
871557
)
AND TL_SCHEDULED_AT between ’01-11-2011’ and ’30-11-2011’)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 10:24:28

[code]
SELECT dateval,SUM(Total) AS Total
FROM
(Query1
UNION ALL
Query2
)t
GROUP BY dateval
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 10:25:59

select dy, sum(total)
from
(
SELECT
dy = datepart(day, WF_COMPLETE_ON),
COUNT(Wflw.WF_RECORD) as Total
FROM
WORKFLOW as Wflw
...
union all -- make sure to have all here
SELECT
datepart(day, TL_SCHEDULED_AT),COUNT(*) as Total
FROM TICKET_LETTER VTL
...
) a
group by dy

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 03:02:56

Also make sure to input dates in unambigious formats
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:11:13

one more thing you can do is instead of below large set of hardcoded value comparison using IN


..
VTL.TL_CODE IN (10031,10033,10106,10107,10291,10292,10293,10294,10295,10296,10599,10600,10601,10602,10603,10604,
10873,10875,10876,10877,10878,10879,10880,10881,10885,10886,10887,10888,10890,10891,10892,10893,
10894,10895,10896,10900,10901,10902,10903,10905,10906,10907,10908,10909,10910,10911,10915,10916,
10917,10918,10920,10921,10922,10923,10924,10925,10926,10930,10931,10932,10933,10935,10936,10937,
10938,10939,10940,10941,10945,10946,10947,10948,10950,10951,10952,10953,10954,10955,10956,10960,
10961,10962,10993,10995,10996,10997,10998,10999,11000,11001,11005,11006,11007,11008,11010,11011,
11012,11013,11014,11015,11016,11020,11021,11022,11177,11178,11179,11180,11181,11182,11183,11184,
871557
...

it would be much better if you can populate the values onto a table and use a join to it in query. this will have flexibility of adding new values when required to table or remove them without touching main query. Also if number of values are really large, you can even add an index to table to speed up value search

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources