May 3, 2012 at 3:21 am
Hiya,
I have a table that contains the name type of a user, and a table that contains the user;
NAMES table:
NAMENO | NAME | FIRSTNAME
----------|----------|-------------
0000001 | Bloggs | Joe
0000002 | Smith | John
0000003 | Doe | Jon
NAMETYPE table;
NAMENO | NAMETYPE
---------|--------------
0000001 | EMP
0000001 | SIG
0000002 | EMP
0000003 | EMP
CASES table
NAMENO | CASEID | CASEDESC
-------------------------------
0000001 | 1 | Case 1
0000001 | 2 | Case 2
0000002 | 3 | Case 3
0000003 | 4 | Case 4
0000003 | 5 | Case 5
Is is possible in T-SQL to say join on the EMP NAMETYPE unles the NAMENO is a specific value, and if this specific value join in the SIG NAMETYPE?
Thanks
May 3, 2012 at 3:32 am
lanky_doodle (5/3/2012)
Hiya,I have a table that contains the name type of a user, and a table that contains the user;
NAMES table:
NAMENO | NAME | FIRSTNAME
----------|----------|-------------
0000001 | Bloggs | Joe
0000002 | Smith | John
0000003 | Doe | Jon
NAMETYPE table;
NAMENO | NAMETYPE
---------|--------------
0000001 | EMP
0000001 | SIG
0000002 | EMP
0000003 | EMP
Is is possible in T-SQL to say join on the EMP NAMETYPE unles the NAMENO is a specific value, and if this specific value join in the SIG NAMETYPE?
Thanks
Given the example above what would be the desired results of the query?
Fitz
May 3, 2012 at 3:41 am
Let me know the intended result of your query...
May 3, 2012 at 3:43 am
lanky_doodle (5/3/2012)
Hiya,I have a table that contains the name type of a user, and a table that contains the user;
NAMES table:
NAMENO | NAME | FIRSTNAME
----------|----------|-------------
0000001 | Bloggs | Joe
0000002 | Smith | John
0000003 | Doe | Jon
NAMETYPE table;
NAMENO | NAMETYPE
---------|--------------
0000001 | EMP
0000001 | SIG
0000002 | EMP
0000003 | EMP
Is is possible in T-SQL to say join on the EMP NAMETYPE unles the NAMENO is a specific value, and if this specific value join in the SIG NAMETYPE?
Thanks
Sample data should be laid out like this, so that it is readily consumable for anyone wanting to help.
--Build #NAMES sample data
SELECT NAMENO, NAME, FIRSTNAME
INTO #NAMES
FROM (VALUES('0000001' /*This is bad, should instead be an INT or BIGINT*/, 'Bloggs', 'Joe'),
('0000002', 'Smith', 'John'),
('0000003', 'Doe', 'Jon'))a(NAMENO, NAME, FIRSTNAME);
--Build #NAMETYPE sample data
SELECT NAMENO, NAMETYPE
INTO #NAMETYPE
FROM (VALUES('0000001', /*This is bad, should instead be an INT or BIGINT*/ 'EMP'),
('0000001', 'SIG'),
('0000002', 'EMP'),
('0000003', 'EMP'))a(NAMENO, NAMETYPE);
--Answer
SELECT NAMENO, NAME, FIRSTNAME, NAMETYPE
FROM (SELECT a.NAMENO, a.NAME, a.FIRSTNAME, b.NAMETYPE
FROM #NAMES a
INNER JOIN #NAMETYPE b ON a.NAMENO = b.NAMENO) c
WHERE NAMETYPE = CASE WHEN NAMENO = '0000001' --Specific "NAMENO"
THEN 'SIG' ELSE 'EMP' END;
May 3, 2012 at 3:44 am
I have added another table, CASES.
What I want to do is count all the cases a user has. At the moment it (correctly) shows the number of cases for each user as I am only joining on the EMP nametype.
However, say the user is Joe Bloggs (NAMENO 0000001), for each time this happens I want it to show the SIG name and be in the same count as the matching users count;
So, at present;
SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' N.NAME STAFFNAME
FROM CASES CA JOIN NAMETYPE NT ON CA.NAMENO = NT.NAMENO JOIN
NAME N ON NT.NAMENO = N.NAMENO
Would show this;
2 Joe Bloggs
1 John Smith
2 Jon Doe
But say Jon Doe was the SIG for both of Joe Bloggs' case, I want the result to be;
1 John Smith
4 Jon Doe
May 3, 2012 at 3:48 am
PLease show all DDL (create table etc), the sample data (INSERT INTO...) and the required output. This requirement is illdefined at present and we need to know more to help.
Fitz
May 3, 2012 at 4:11 am
lanky_doodle (5/3/2012)
I have added another table, CASES.What I want to do is count all the cases a user has. At the moment it (correctly) shows the number of cases for each user as I am only joining on the EMP nametype.
However, say the user is Joe Bloggs (NAMENO 0000001), for each time this happens I want it to show the SIG name and be in the same count as the matching users count;
So, at present;
SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' N.NAME STAFFNAME
FROM CASES CA JOIN NAMETYPE NT ON CA.NAMENO = NT.NAMENO JOIN
NAME N ON NT.NAMENO = N.NAMENO
Would show this;
2 Joe Bloggs
1 John Smith
2 Jon Doe
But say Jon Doe was the SIG for both of Joe Bloggs' case, I want the result to be;
1 John Smith
4 Jon Doe
Wonderful.
--Build #NAMES sample data
SELECT NAMENO, NAME, FIRSTNAME
INTO #NAMES
FROM (VALUES('0000001' /*This is bad, should instead be an INT or BIGINT*/, 'Bloggs', 'Joe'),
('0000002', 'Smith', 'John'),
('0000003', 'Doe', 'Jon'))a(NAMENO, NAME, FIRSTNAME);
--Build #NAMETYPE sample data
SELECT NAMENO, NAMETYPE
INTO #NAMETYPE
FROM (VALUES('0000001', /*This is bad, should instead be an INT or BIGINT*/ 'EMP'),
('0000001', 'SIG'),
('0000002', 'EMP'),
('0000003', 'EMP'))a(NAMENO, NAMETYPE);
--Build #CASES sample data
SELECT NAMENO, CASEID, CASEDESC
INTO #CASES
FROM (VALUES('0000001', /*This is bad, should instead be an INT or BIGINT*/ 1, 'Case 1'),
('0000001', 2, 'Case 2'),
('0000002', 3, 'Case 3'),
('0000003', 4, 'Case 4'),
('0000003', 5, 'Case 5'))a(NAMENO, CASEID, CASEDESC);
/*
Your current query: -
SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' N.NAME STAFFNAME
FROM #CASES CA JOIN #NAMETYPE NT ON CA.NAMENO = NT.NAMENO JOIN
#NAMES N ON NT.NAMENO = N.NAMENO
Syntactily incorrect. . . let's correct it do it works.
SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' + N.NAME AS STAFFNAME
FROM #CASES CA
INNER JOIN #NAMETYPE NT ON CA.NAMENO = NT.NAMENO
INNER JOIN #NAMES N ON NT.NAMENO = N.NAMENO
GROUP BY N.FIRSTNAME + ' ' + N.NAME;
OK, that returns: -
STAFFNAME
----------- -----------
4 Joe Bloggs
1 John Smith
2 Jon Doe
You imply that your sample data should show "Joe Bloggs" with two cases. . . which means you must
have a WHERE clause that filters out the SIG.
Let's fix it: -
SELECT N.FIRSTNAME + ' ' + N.NAME AS STAFFNAME,
SUM(CASE WHEN NAMETYPE = 'EMP' THEN 1 ELSE 0 END)
FROM #CASES CA
INNER JOIN #NAMETYPE NT ON CA.NAMENO = NT.NAMENO
INNER JOIN #NAMES N ON NT.NAMENO = N.NAMENO
GROUP BY N.FIRSTNAME + ' ' + N.NAME;
OK, that now returns: -
STAFFNAME
----------- -----------
Joe Bloggs 2
John Smith 1
Jon Doe 2
OK, but if the SIG "person" is the same as the EMP "person", you don't want to display
them.
*/
SELECT STAFFNAME, SUM(CASES) AS CASESCOUNT
FROM (SELECT N.FIRSTNAME + ' ' + N.NAME AS STAFFNAME, CASEID,
SUM(CASE WHEN NAMETYPE = 'EMP' THEN 1 ELSE 0 END) -
SUM(CASE WHEN NAMETYPE = 'SIG' THEN 1 ELSE 0 END) AS CASES
FROM #CASES CA
INNER JOIN #NAMETYPE NT ON CA.NAMENO = NT.NAMENO
INNER JOIN #NAMES N ON NT.NAMENO = N.NAMENO
GROUP BY N.FIRSTNAME + ' ' + N.NAME, CASEID) a
WHERE CASES > 0
GROUP BY STAFFNAME;
/*
And that returns: -
STAFFNAME CASESCOUNT
----------- -----------
John Smith 1
Jon Doe 2
*/
SELECT STAFFNAME, SUM(CASES) AS CASESCOUNT
FROM (SELECT N.FIRSTNAME + ' ' + N.NAME AS STAFFNAME, CASEID,
SUM(CASE WHEN NAMETYPE = 'EMP' THEN 1 ELSE 0 END) -
SUM(CASE WHEN NAMETYPE = 'SIG' THEN 1 ELSE 0 END) AS CASES
FROM #CASES CA
INNER JOIN #NAMETYPE NT ON CA.NAMENO = NT.NAMENO
INNER JOIN #NAMES N ON NT.NAMENO = N.NAMENO
GROUP BY N.FIRSTNAME + ' ' + N.NAME, CASEID) a
WHERE CASES > 0
GROUP BY STAFFNAME;
May 3, 2012 at 4:55 am
Mark Fitzgerald-331224 (5/3/2012)
PLease show all DDL (create table etc), the sample data (INSERT INTO...) and the required output. This requirement is illdefined at present and we need to know more to help.Fitz
Apologies. I am working on a 3rd party database so have no control over the datatypes etc, and as is confidential stuff I can't easily just duplicate it on here.
I will sanitize where I can so it's clean to publish. Give me a min
May 3, 2012 at 5:04 am
OK, this is current SQL with the output;
SELECTCOUNT(CA.CASEID) CASESCOUNT, CASE WHEN N.NAMENO = 2272 THEN 'Joe Bloggs' ELSE N.FIRSTNAME + ' ' + N.NAME END STAFFNAME
FROMCASES CA JOIN
CASEEVENT CE ON CA.CASEID = CE.CASEID JOIN
CASENAME CN ON CA.CASEID = CN.CASEID AND CN.NAMETYPE = 'EMP' JOIN
NAME N ON CN.NAMENO = N.NAMENO
WHERECE.EVENTNO = '-16' AND CE.EVENTDATE BETWEEN '2012-01-01' AND '2012-03-31' AND N.NAMENO IN(2272, -93)
GROUP BYN.FIRSTNAME, N.NAME, N.NAMENO
ORDER BYN.NAME
9Joe Bloggs
389Records Department
I will work on the individual tables now, but basically the result shows the Records Department as having 8887391 cases. Well Joe Bloggs is the Signatory on some of theses case so the numbers should reflect it (i.e. Joe Bloggs cases increase and the Records Department cases decreases). So the final result set will show 0 Records Deparment, so wherever the Records Department is the EMP, the number should be attributed to the SIG of the case.
May 3, 2012 at 5:17 am
NAME table;
NAMENOSTAFFNAME
-93Records Department
2272Joe Bloggs
CASENAME table;
CASEIDNAMETYPENAMENO
4382EMP2272
4382SIG2272
4383EMP-93
4385EMP-93
4385SIG2272
4387EMP-93
4388EMP2272
4388SIG2272
CASEEVENT TABLE;
CASEIDEVENTNOEVENTDATE
4382-162007-11-15 00:00:00.000
4383-162007-11-20 00:00:00.000
4385-162007-11-13 00:00:00.000
4387-162007-11-20 00:00:00.000
4388-162007-11-13 00:00:00.000
CASES table;
CASEID
4382
4383
4385
4387
4388
May 4, 2012 at 12:34 am
lanky_doodle (5/3/2012)
NAME table;
NAMENOSTAFFNAME-93Records Department
2272Joe Bloggs
CASENAME table;
CASEIDNAMETYPENAMENO4382EMP2272
4382SIG2272
4383EMP-93
4385EMP-93
4385SIG2272
4387EMP-93
4388EMP2272
4388SIG2272
CASEEVENT TABLE;
CASEIDEVENTNOEVENTDATE4382-162007-11-15 00:00:00.000
4383-162007-11-20 00:00:00.000
4385-162007-11-13 00:00:00.000
4387-162007-11-20 00:00:00.000
4388-162007-11-13 00:00:00.000
CASES table;
CASEID4382
4383
4385
4387
4388
Assuming that the output is supposed to be SIG overrides EMP then:
use tempdb;
go
create table Name(NameNo int,StaffName varchar(30))
create table CaseName(CaseID int,NameType char(3),NameNo int)
create table Cases(CaseID int)
insert into Name values (-93,'Records Department')
insert into Name values (2272,'Joe Bloggs')
insert into Cases values (4382)
insert into Cases values (4383)
insert into Cases values (4385)
insert into Cases values (4387)
insert into Cases values (4388)
insert into CaseName values (4382,'EMP',2272)
insert into CaseName values (4382,'SIG',2272)
insert into CaseName values (4383,'EMP',-93)
insert into CaseName values (4385,'EMP',-93)
insert into CaseName values (4385,'SIG',2272)
insert into CaseName values (4387,'EMP',2272)
insert into CaseName values (4388,'EMP',2272)
insert into CaseName values (4388,'SIG',2272)
select * from Name
select * from CaseName
select * from Cases
select N.StaffName, COUNT(*) as NumberCases from
(
-- return a row per case (SIG overrides EMP)
select *, ROW_NUMBER() over (
partition by CaseID order by
CASE WHEN NameType = 'SIG' then 0 else 1 end)
as ReturnPriority
from CaseName
) as A
inner join Name as N
on A.NameNo = N.NameNo
where ReturnPriority = 1
group by N.StaffName
Fitz
May 4, 2012 at 5:05 am
Thanks Mark, but that doesn't return the correct data.
As before, if the NAMENO is -93 (Record Department), I want the join to be made on the SIG nametype, therefore the count should assign the numbers to the SIG nametype.
doing a CASE WHEN NAMENO = -93 THEN 'SIG' ELSE 'EMP' END doesn't work for me. It just doesn't return any data for the -93 NAMENO
SELECTCOUNT(CA.CASEID) CASESCOUNT, N.FIRSTNAME + ' ' + N.NAME STAFFNAME
FROMCASES CA JOIN
CASEEVENT CE ON CA.CASEID = CE.CASEID JOIN
CASENAME CN ON CA.CASEID = CN.CASEID JOIN
NAME N ON CN.NAMENO = N.NAMENO
WHERECN.NAMETYPE = CASE WHEN N.NAMENO = -93 THEN 'SIG' ELSE 'EMP' END AND CE.EVENTNO = '-16'
AND CE.EVENTDATE BETWEEN '2012-01-01' AND '2012-03-31' AND N.NAMENO IN (2272, -93)
GROUP BYN.FIRSTNAME, N.NAME
ORDER BYN.NAME
9Joe Bloggs
against
SELECTCOUNT(CA.CASEID) CASESCOUNT, N.FIRSTNAME + ' ' + N.NAME STAFFNAME
FROMCASES CA JOIN
CASEEVENT CE ON CA.CASEID = CE.CASEID JOIN
CASENAME CN ON CA.CASEID = CN.CASEID JOIN
NAME N ON CN.NAMENO = N.NAMENO
WHERECN.NAMETYPE = 'EMP' AND CE.EVENTNO = '-16'
AND CE.EVENTDATE BETWEEN '2012-01-01' AND '2012-03-31' AND N.NAMENO IN (2272, -93)
GROUP BYN.FIRSTNAME, N.NAME
ORDER BYN.NAME
9Joe Bloggs
389Records Department
May 4, 2012 at 6:09 am
lanky_doodle (5/4/2012)
Thanks Mark, but that doesn't return the correct data.As before, if the NAMENO is -93 (Record Department), I want the join to be made on the SIG nametype, therefore the count should assign the numbers to the SIG nametype.
doing a CASE WHEN NAMENO = -93 THEN 'SIG' ELSE 'EMP' END doesn't work for me. It just doesn't return any data for the -93 NAMENO
SELECTCOUNT(CA.CASEID) CASESCOUNT, N.FIRSTNAME + ' ' + N.NAME STAFFNAMEFROMCASES CA JOIN
CASEEVENT CE ON CA.CASEID = CE.CASEID JOIN
CASENAME CN ON CA.CASEID = CN.CASEID JOIN
NAME N ON CN.NAMENO = N.NAMENO
WHERECN.NAMETYPE = CASE WHEN N.NAMENO = -93 THEN 'SIG' ELSE 'EMP' END AND CE.EVENTNO = '-16'
AND CE.EVENTDATE BETWEEN '2012-01-01' AND '2012-03-31' AND N.NAMENO IN (2272, -93)
GROUP BYN.FIRSTNAME, N.NAME
ORDER BYN.NAME
9Joe Bloggsagainst
SELECTCOUNT(CA.CASEID) CASESCOUNT, N.FIRSTNAME + ' ' + N.NAME STAFFNAMEFROMCASES CA JOIN
CASEEVENT CE ON CA.CASEID = CE.CASEID JOIN
CASENAME CN ON CA.CASEID = CN.CASEID JOIN
NAME N ON CN.NAMENO = N.NAMENO
WHERECN.NAMETYPE = 'EMP' AND CE.EVENTNO = '-16'
AND CE.EVENTDATE BETWEEN '2012-01-01' AND '2012-03-31' AND N.NAMENO IN (2272, -93)
GROUP BYN.FIRSTNAME, N.NAME
ORDER BYN.NAME
9Joe Bloggs389Records Department
Given the sample data :
CASEIDNAMETYPENAMENO
4382EMP2272
4382SIG2272
4383EMP-93
4385EMP-93
4385SIG2272
4387EMP-93
4388EMP2272
4388SIG2272
What should the result be?
Reasoning : The cases 4382, 4385 and 4388 have a SIG so should be allocated to 2272. The cases 4383 and 4387 do not a SIG to link to, so should this be allocated to the EMP i.e. -93?
If this is not correct please state the required result and a reason for the result.
Fitz
May 4, 2012 at 12:24 pm
Mark Fitzgerald-331224 (5/4/2012)Given the sample data :
CASEIDNAMETYPENAMENO
4382EMP2272
4382SIG2272
4383EMP-93
4385EMP-93
4385SIG2272
4387EMP-93
4388EMP2272
4388SIG2272
What should the result be?
Reasoning : The cases 4382, 4385 and 4388 have a SIG so should be allocated to 2272. The cases 4383 and 4387 do not a SIG to link to, so should this be allocated to the EMP i.e. -93?
If this is not correct please state the required result and a reason for the result.
Fitz
Not quite. Sample data;
CASEIDNAMETYPENAMENO
4382EMP2272
4382SIG2272
4383EMP-93
4385EMP-93
4385SIG2272
4387EMP-93
4388EMP2272
4388SIG2272
The ones in bold should be attributed to the sig (2272), but only because the EMP is -93. The SIG nametype should only be used where the EMP is -93 and not where the 2272 user is both.
Our live data shows 8 cases for user 2272 when matching on SIG and 9 cases when matching on EMP. The total is therefore 17 but this is actually the wrong result (or not the result I am looking for), as he is both EMP and SIG on some of them. In this case it should be counted as 1 rather than 2.
So lets say he is EMP AND/OR SIG on 13 of them, and he is the SIG on 2 of them where he is also NOT the EMP and the EMP is -93, the total should be 15.
May 8, 2012 at 9:01 am
Any thoughts guys?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply