| Author |
 Topic  |
|
|
nbritton
Starting Member
22 Posts |
 Posted - 03/16/2012 : 13:06:11
|
How can i return the counts of zero as well. I am trying to see what sym's have not been referenced in a period of time.
The prob_ctg table holds all the syms defined. its about 1400 and i am currently returning 1052 rows.
SELECT prob_ctg.sym, COUNT(*) AS [Number of Records]
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category
WHERE (call_req.open_date > '1318777793')
GROUP BY prob_ctg.sym
ORDER BY [Number of Records] |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 03/16/2012 : 13:14:37
|
You might be able to change move WHERE clause to part of the join clause. If not, they it'd help to post sample data and expected output in a consumable format:SELECT prob_ctg.sym, COUNT(*) AS [Number of Records]
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category
AND (call_req.open_date > '1318777793')
GROUP BY prob_ctg.sym
ORDER BY [Number of Records] |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 14:38:43
|
Thanks for the help, but neither of the above came me the expected results.
call_req:
pcat:66442413
pcat:66443296
pcat:66442877
pcat:66443138
pcat:66443357
prob_ctg:
Software pcat:5100 1
old.Hardware pcat:5101 1
Networks pcat:5102 1
Applications pcat:5103 1
Software.Environment pcat:5104 1
looking to have :
sym count
software 0
old.hardware 2 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 03/16/2012 : 14:50:27
|
can you explain how you got those end values?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 15:03:39
|
| the end value should come from the count(*) followed by the group by on sym |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 03/16/2012 : 15:10:38
|
nope. I understood that. What I was asking is how you got those counts from posted data? how did softcare count came as 0 when you've an occurance for software in prob_ctg
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 15:25:55
|
| ohh i was just giving an example of what a zero would be expected to look like. Your right from the data it would not be zero. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 15:59:02
|
nope that did not work. It did not show the zero counts and i checked a pcat that should have had 6 records and it did not.
i used to find a test case:
SELECT *
FROM [mdb].[dbo].[call_req]
where category = 'pcat:66442378' and open_date > '1318777793'
order by open_Date
This has gotten me the closest, but my counts are still wrong:
SELECT prob_ctg.sym, COUNT(call_req.ref_num) AS [Number of Records], prob_ctg.del
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category and open_date > '1318777793'
GROUP BY prob_ctg.sym, prob_ctg.del
HAVING (prob_ctg.del = 0)
ORDER BY [Number of Records] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 03/16/2012 : 16:03:53
|
unless you clearly show us the data from table and explain us how you want counts to come i dont think anybody will be able to help you out. Check the link in Bretts signature and post data in required format
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 16:20:50
|
call_req:
category open_date
pcat:5100 1318777790
pcat:5100 1318777743
pcat:5100 1318777791
pcat:5101 1318777791
pcat:5102 1318777794
pcat:5103 1318777791
prob_ctg:
sym del
Software pcat:5100 0
old.Hardware pcat:5101 0
Networks pcat:5102 0
Applications pcat:5103 0
Software.Environment pcat:5104 0
Senerio where del =0 and open_date is less than 1318777793 show count of records for each sym value
software.enviornment 0
applications 1
Software 3
old.Hardware 1
Networks 0 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 03/16/2012 : 16:34:43
|
SELECT pc.sym,
COUNT(cr.category) AS Cnt
FROM prob_ctg pc
LEFT JOIN call_req cr
ON pc.pcat = cr.pcat
AND cr.open_date < 1318777793
WHERE pc.del=0
GROUP BY pc.sym
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 03/16/2012 : 16:36:51
|
I thought it was this
CREATE TABLE #call_req (
category varchar(25)
, open_date int --??? what kind o date is this?
)
CREATE TABLE #prob_ctg (
sym varchar(25)
, del varchar(25)
)
GO
INSERT INTO #call_req (category, open_date)
SELECT 'pcat:5100','1318777790' UNION ALL
SELECT 'pcat:5100','1318777743' UNION ALL
SELECT 'pcat:5100','1318777791' UNION ALL
SELECT 'pcat:5101','1318777791' UNION ALL
SELECT 'pcat:5102','1318777794' UNION ALL
SELECT 'pcat:5103','1318777791'
INSERT INTO #prob_ctg (sym, del)
SELECT 'Software', 'pcat:5100' UNION ALL -- 0
SELECT 'old.Hardware', 'pcat:5101' UNION ALL -- 0
SELECT 'Networks', 'pcat:5102' UNION ALL -- 0
SELECT 'Applications', 'pcat:5103' UNION ALL -- 0
SELECT 'Software.Environment', 'pcat:5104' -- 0
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Want to help yourself?
http://msdn.microsoft.com/en-us/library/ms130214.aspx
http://weblogs.sqlteam.com/brettk/
http://brettkaiser.blogspot.com/
|
|
|
|
nbritton
Starting Member
22 Posts |
Posted - 03/16/2012 : 16:45:53
|
Yes that was one field. The date value is en epoch format. I was able to get it with the following statement.
SELECT prob_ctg.sym, COUNT(call_req.ref_num) AS [Number of Records], prob_ctg.del
FROM call_req RIGHT OUTER JOIN
prob_ctg ON prob_ctg.persid = call_req.category and (open_date > '1318777793')
GROUP BY prob_ctg.sym, prob_ctg.del
HAVING (prob_ctg.del = 0)
ORDER BY [Number of Records]
Thanks to both of you. I really appricate the help. Your posts help me get to the end result. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DeanT
Starting Member
USA
13 Posts |
Posted - 03/16/2012 : 16:57:21
|
| default it to zero prior to sending back the other values |
|
|
| |
Topic |
|
return counts of zero as well
