Need a count statement to include 0 1

[bloko]

I have count statement but for graph purposes I need to show the count number when it is 0. ATM it only shows if it has counted 1 or greater. Below is my code.

select

count(*) as Total_Resolved ,
cast(year(HE.Entry_date)as char(4)) + cast(CASE WHEN LEN(rtrim(cast(datepart(wk, HE.Entry_date) as char(10)))) < 2 THEN cast(0 as char(1)) + rtrim(cast(datepart(wk,HE.Entry_date) as char(10))) ELSE rtrim(cast(datepart(wk, HE.Entry_date)as char(10))) END as char(2)) as Yr_wk,
C.first_name + ' ' + C.Last_name as Tech
from tech C,
job_ticket J,
History_Entry HE
where C.client_id = J.assigned_tech_id
and J.job_ticket_id = HE.JOB_TICKET_ID
and assigned_tech_id in (2, 5, 7, 16,20,22)
and status_type_id in (3, 4, 5)
AND HE.ENTRY_TEXT Like '%to Resolved%'

 

[Jamfool]

The yr_wk can be done a little easier than you have it there.

Are you sure you have posted all the sql?

It is kind of hard without the table structure so Iam guessing a little, and you are using the old non-ansi joins.

if you come back with a little more detail with regards to table structure and some example data with what output you get&expect, we can see if we can resolve it

DROP TABLE job_ticket
DROP TABLE tech
DROP TABLE History_Entry


CREATE TABLE job_ticket(job_ticket_id int,assigned_tech_id int,status_type_id int)
CREATE TABLE tech(client_id int,first_name varchar(50),Last_name varchar(50))
CREATE TABLE History_Entry(Entry_date DATETIME,JOB_TICKET_ID int,ENTRY_TEXT varchar(50))

INSERT INTO job_ticket VALUES (1,2,3)
INSERT INTO tech VALUES (2,'AAA','BBB')
INSERT INTO History_Entry VALUES (GETDATE(),1,'to Resolved')
INSERT INTO History_Entry VALUES (GETDATE()+1,1,'BLAH')

SELECT     
COUNT(*) AS Total_Resolved, 
CONVERT(varchar(6),HE.Entry_date,112) AS Yr_wk, 
C.first_name + ' ' + C.Last_name AS Tech
FROM         dbo.tech AS C INNER JOIN
                      dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN
                      dbo.History_Entry AS HE ON J.job_ticket_id = HE.JOB_TICKET_ID
WHERE     
(J.assigned_tech_id IN (2, 5, 7, 16, 20, 22)) AND (J.status_type_id IN (3, 4, 5)) 
AND (HE.ENTRY_TEXT LIKE '%to Resolved%')
GROUP BY Entry_date,C.first_name, C.Last_name

 

[bloko]

Sorry, should have mentioned its a stored proceedure

The whole code is

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[Project_Work_Split_Resolved_Jobs] as
select

count(*) as Total_Resolved ,
cast(year(HE.Entry_date)as char(4)) + cast(CASE WHEN LEN(rtrim(cast(datepart(wk, HE.Entry_date) as char(10)))) < 2 THEN cast(0 as char(1)) + rtrim(cast(datepart(wk,HE.Entry_date) as char(10))) ELSE rtrim(cast(datepart(wk, HE.Entry_date)as char(10))) END as char(2)) as Yr_wk,
C.first_name + ' ' + C.Last_name as Tech
from tech C,
job_ticket J,
History_Entry HE
where C.client_id = J.assigned_tech_id
and J.job_ticket_id = HE.JOB_TICKET_ID
and assigned_tech_id in (2, 5, 7, 16,20,22)
and status_type_id in (3, 4, 5)
AND HE.ENTRY_TEXT Like '%to Resolved%'


GROUP BY
cast(year(HE.Entry_date)as char(4)) + cast(CASE WHEN LEN(rtrim(cast(datepart(wk, HE.Entry_date) as char(10)))) < 2 THEN cast(0 as char(1)) + rtrim(cast(datepart(wk,HE.Entry_date) as char(10))) ELSE rtrim(cast(datepart(wk, HE.Entry_date)as char(10))) END as char(2)),
C.first_name + ' ' + C.Last_name
order by
cast(year(HE.Entry_date)as char(4)) + cast(CASE WHEN LEN(rtrim(cast(datepart(wk, HE.Entry_date) as char(10)))) < 2 THEN cast(0 as char(1)) + rtrim(cast(datepart(wk,HE.Entry_date) as char(10))) ELSE rtrim(cast(datepart(wk, HE.Entry_date)as char(10))) END as char(2)),
C.first_name + ' ' + C.Last_name

Total Resolved Yr_Wk Tech
2 201008 Joe Bloggs
33 201008 Pete Best
30 201008 Paul Reddy
1 201008 Frank Gill
10 201009 Joe Bloggs
1 201009 Pete Best
17 201009 Paul Reddy
9 201009 Frank Gill



I didnt write any of this and the person who did is long gone. Thanks for you help, I really appreiciate it

 

[Jamfool]

ok, try:

DROP TABLE job_ticket
DROP TABLE tech
DROP TABLE History_Entry

CREATE TABLE job_ticket(job_ticket_id int,assigned_tech_id int,status_type_id int)
CREATE TABLE tech(client_id int,first_name varchar(50),Last_name varchar(50))
CREATE TABLE History_Entry(Entry_date DATETIME,JOB_TICKET_ID int,ENTRY_TEXT varchar(50))

INSERT INTO job_ticket VALUES (1,2,3)
INSERT INTO tech VALUES (2,'AAA','BBB')
INSERT INTO History_Entry VALUES (GETDATE(),1,'to Resolved')
INSERT INTO History_Entry VALUES (GETDATE()+1,1,'BLAH')

INSERT INTO tech VALUES (5,'Joe','Bloggs')

SELECT DISTINCT 
	(
	SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN
						  dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN
						  dbo.History_Entry AS HE ON J.job_ticket_id = HE.JOB_TICKET_ID
						  WHERE C.client_id = et.client_id
						  AND  CONVERT(VARCHAR(6),HE.Entry_date,112) = CONVERT(VARCHAR(6),ehe.Entry_date,112)
						  AND (J.status_type_id IN (3, 4, 5)) 
						  AND (HE.ENTRY_TEXT LIKE '%to Resolved%')
	) [Total Resolved],
CONVERT(VARCHAR(6),Entry_date,112) Yr_wk,
first_name + ' ' + Last_name Tech
FROM History_Entry ehe,tech et
WHERE et.client_id IN (2, 5, 7, 16, 20, 22)

 

[bloko]

The code above wouldnt run. The tables already exist which I think is the issue.

I did use some of your above code to try and clean up

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[Project_Work_Split_Resolved_Jobs] as
select

count(*) as Total_Resolved ,
CONVERT(varchar(6),HE.Entry_date,112) AS Yr_wk,
C.first_name + ' ' + C.Last_name as Tech
from tech C,
job_ticket J,
History_Entry HE
where C.client_id = J.assigned_tech_id
and J.job_ticket_id = HE.JOB_TICKET_ID
and assigned_tech_id in (2, 5, 7, 16,20,22)
and status_type_id in (3, 4, 5)
AND HE.ENTRY_TEXT Like '%to Resolved%'


GROUP BY C.first_name, C.Last_name, Entry_Date

The issue with this code is that it shows the total resolved as 1's instead of the total of the 1's. if that makes sense

 

[Jamfool]

The only bit that you should need is at the end, and should group the totals by client and entry date.

Your group by should be:

GROUP BY C.first_name, C.Last_name, Entry_CONVERT(varchar(6),HE.Entry_date,112)

otherwise it will group based on the date time part as well.

SELECT DISTINCT 
    (
    SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN
                          dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN
                          dbo.History_Entry AS HE ON J.job_ticket_id = HE.JOB_TICKET_ID
                          WHERE C.client_id = et.client_id
                          AND  CONVERT(VARCHAR(6),HE.Entry_date,112) = CONVERT(VARCHAR(6),ehe.Entry_date,112)
                          AND (J.status_type_id IN (3, 4, 5)) 
                          AND (HE.ENTRY_TEXT LIKE '%to Resolved%')
    ) [Total Resolved],
CONVERT(VARCHAR(6),Entry_date,112) Yr_wk,
first_name + ' ' + Last_name Tech
FROM History_Entry ehe,tech et
WHERE et.client_id IN (2, 5, 7, 16, 20, 22)

 

[bloko]

I tried the code with the Select Distinct and I tried to run the stored proceedure and i it was executing for 3 minutes, then I cancelled it.

I then tried it with just select and it ran but was pulling out duplicate records.

Also I noticed that the date that you suggested isn't showing yyyyww its showing yyyymm. The graph I am amending looks at resolved jobs per week.

I think it may be easier to stick to the code which exists as it seems to work, but im still stuck on getting the count to work when a user doesnt have a resolved job

Thanks for you help so far.

 

[bloko]

I found this link, im going to see if i can decipher it and use it. Will keep you posted with my results. If you can figure it out your more than welcome to help me again Thanks

http://lichao.net/eblog/how-to-disp...p-by-report-in-count-sql-query-200808164.html

 

[Jamfool]

ok looks like the cartesian product is a bit large for the history_entry and tech so best to filter them down before hand. I guess you have loads of techs and history entries.

I have added a WHERE Entry_date >= '20100101' but you may be able to remove this depending on speed.

try below, and also see what type of duplication you getting.

SELECT DISTINCT 
    (
    SELECT COUNT(*) FROM dbo.tech AS C INNER JOIN
                          dbo.job_ticket AS J ON C.client_id = J.assigned_tech_id INNER JOIN
                          dbo.History_Entry AS HE ON J.job_ticket_id = HE.JOB_TICKET_ID
                          WHERE C.client_id = et.client_id
                          AND 
                          CAST(YEAR(HE.Entry_date) AS CHAR(4))+ CAST(RIGHT('0'+CAST(DATEPART(wk,HE.Entry_date) AS VARCHAR(2)),2) AS CHAR(2))
                          =ehe.Yr_wk
                          
                          
                          
                          AND (J.status_type_id IN (3, 4, 5)) 
                          AND (HE.ENTRY_TEXT LIKE '%to Resolved%')
    ) [Total Resolved],
Yr_wk,
first_name + ' ' + Last_name Tech
FROM 
(
SELECT DISTINCT CAST(YEAR(Entry_date) AS CHAR(4))+ CAST(RIGHT('0'+CAST(DATEPART(wk,Entry_date) AS VARCHAR(2)),2) AS CHAR(2)) Yr_wk
FROM History_Entry
WHERE Entry_date >= '20100101'
)
ehe,(SELECT * FROM tech WHERE client_id IN (2, 5, 7, 16, 20, 22)) et

 

[bloko]

Thanks very much, the code does work, but a problem has occured with Crystal.

I get a "Failed to retreive data from the database"

"Invalid Argument Provided"

I dont understand why its not retrieving the data as it runs fine in SQL server. Dont suppose your a crystal expert as well

 

[Jamfool]

sorry havent used it for a long time. its looks quite a generic error. have you tried creating a new report and trying to pull back the data? just to check its not a binding issue... at least if that works you can narrow it down ;p

 

[bloko]

Hey Jamfool, managed to get the crystal report working as well. It seemed that the stored proccedure needs to be created first and then the report, not visa versa. Thanks for your help and effort, it is appreciated.

Kind Regards

 

[Jamfool]

glad you got it sorted