June 22, 2010 at 6:58 pm
/*
Hi there, so here is my problem. The code below works very nicely for what I originally wanted it to do, which is
to return when someone has recorded a login in a table. Grouped by Company and then Division with sub totals and an
overall grand total for Company.
How do I make it give me who HASN'T a record for 'Last Login'? There are 4 records not showing up, very handily since
the WHERE clause populating @tmpOutput is 'WHERE dtLogin IS NOT NULL'
I've been staring at this for a couple of hours now like a monkey with his fist stuck in a tree too stupid to release his
grasp and I just can't figure out how to work it.
I dont need to display the record, I just need to have the count of the record included at the 'Company Total' level as well
as included in the 'Grand Total'
This is under MS SQL 2008
thanks in advance!
*/
set nocount on
declare @tmpSample TABLE
(
vchCompvarchar(20),
vchDivvarchar(20),
intUserID int,
vchFName varchar(20),
vchLName varchar(20),
dtLogin datetime
)
DECLARE @tmpOutput TABLE
(
[Company Name]VARCHAR(20),
[Division Name]VARCHAR(20),
[First Name]VARCHAR(20),
[Last Name]VARCHAR(20),
[Last Login]VARCHAR(20),
[Login Count]VARCHAR(6),
GroupCompLevelINT,
GroupDivLevelINT,
GroupUserLevel INT
)
insert @tmpSample
select 'CompanyA','DivisionA',1,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyA','DivisionA',2,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyA','DivisionA',3,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyA','DivisionA',4,'Lily', 'NotLogged', NULL UNION
select 'CompanyB','SubDiv1',5,'Chris', 'Davis', NULL UNION
select 'CompanyB','SubDiv1',6,'Larry', 'DaCable', '03/12/2010' UNION
select 'CompanyB','SubDiv2',7,'Paul', 'Lynn', '02/18/2010' UNION
select 'CompanyB','SubDiv2',8,'Ron', 'White', NULL UNION
select 'CompanyC','AnotherDIVA',9,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyC','AnotherDIVB',10,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyC','AnotherDIVB',11,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyC','AnotherDIVC',12,'Sam', 'Hobbit', NULL
INSERT @tmpOutput
SELECTvchComp,
vchDiv,
vchFName,
vchLName,
LOGIN_DATE= LEFT(CONVERT(VARCHAR, dtLogin, 120), 10),
LOGIN_COUNT= CASE WHEN dtLogin IS NULL THEN COUNT(intUserID) ELSE '' END,
GroupCompLevel= GROUPING(vchComp),
GroupDivLevel= GROUPING(vchDiv),
GroupUserLevel= GROUPING(vchFName)
FROM @tmpSample
WHERE dtLogin IS NOT NULL
GROUP BY GROUPING SETS((vchComp),(vchComp,vchDiv),(vchComp,vchDiv,vchFName,vchLName,dtLogin),())
SELECT[Company Name]= CASE WHEN [Company Name] IS NULL THEN '' ELSE [Company Name] END,
[Division Name]= CASE WHEN [Division Name] IS NULL THEN '' ELSE [Division Name] END,
[First Name]= CASE WHEN [First Name] IS NULL THEN '' ELSE [First Name] END,
[Last Name]= CASE WHEN [Last Name] IS NULL THEN '' ELSE [Last Name] END,
[Last Login]= CASEWHEN (GroupCompLevel=0 AND GroupDivLevel=0 AND GroupUserLevel=1) THEN 'Division Total'
WHEN (GroupCompLevel=0 AND GroupDivLevel=1) THEN 'Company Total'
WHEN (GroupCompLevel=1 AND GroupDivLevel=1) THEN 'Grand Total'
ELSE [Last Login] END,
[Login Count]= CASE WHEN [Login Count]=0 THEN '' ELSE [Login Count] END
FROM@tmpOutput
June 23, 2010 at 12:51 am
This was removed by the editor as SPAM
June 23, 2010 at 5:03 am
Yes, I tried that first and all it did was double up my record count and show the records.
I'm just having a major case of the dums on this 🙁
June 23, 2010 at 5:40 am
It seems that you want to have a total not matching the underlying (preceding) rows ie having 3 staff members in CompanyA and DivisionA then showing 4 at the division and company summary level. The code below does this by removing the where clause in the grouping query (to have the non-login users counted) but then filter them out in the last query.
An alternative is to have 2 counts which would be the Login_Count you already had plus an Employee_Count. This would probably convey the meaning of the report better in the summary lines. i.e.
CompanyA..DivisionA..Login_Count=3..Employee_Count=4
set nocount on
declare @tmpSample TABLE
(
vchComp varchar(20),
vchDiv varchar(20),
intUserID int,
vchFName varchar(20),
vchLName varchar(20),
dtLogin datetime
)
DECLARE @tmpOutput TABLE
(
[Company Name] VARCHAR(20),
[Division Name] VARCHAR(20),
[First Name] VARCHAR(20),
[Last Name] VARCHAR(20),
[Last Login] VARCHAR(20),
[Login Count] VARCHAR(6),
GroupCompLevel INT,
GroupDivLevel INT,
GroupUserLevel INT
)
insert @tmpSample
select 'CompanyA','DivisionA',1,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyA','DivisionA',2,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyA','DivisionA',3,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyA','DivisionA',4,'Lily', 'NotLogged', NULL UNION
select 'CompanyB','SubDiv1',5,'Chris', 'Davis', NULL UNION
select 'CompanyB','SubDiv1',6,'Larry', 'DaCable', '03/12/2010' UNION
select 'CompanyB','SubDiv2',7,'Paul', 'Lynn', '02/18/2010' UNION
select 'CompanyB','SubDiv2',8,'Ron', 'White', NULL UNION
select 'CompanyC','AnotherDIVA',9,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyC','AnotherDIVB',10,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyC','AnotherDIVB',11,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyC','AnotherDIVC',12,'Sam', 'Hobbit', NULL
--SELECT * FROM @tmpSample
INSERT @tmpOutput
SELECT vchComp,
vchDiv,
vchFName,
vchLName,
LOGIN_DATE = LEFT(CONVERT(VARCHAR, dtLogin, 120), 10),
LOGIN_COUNT = CASE WHEN dtLogin IS NULL THEN COUNT(intUserID) ELSE '' END,
GroupCompLevel = GROUPING(vchComp),
GroupDivLevel = GROUPING(vchDiv),
GroupUserLevel = GROUPING(vchFName)
FROM @tmpSample
--WHERE dtLogin IS NOT NULL
GROUP BY GROUPING SETS((vchComp),(vchComp,vchDiv),(vchComp,vchDiv,vchFName,vchLName,dtLogin),())
SELECT [Company Name] = CASE WHEN [Company Name] IS NULL THEN '' ELSE [Company Name] END,
[Division Name] = CASE WHEN [Division Name] IS NULL THEN '' ELSE [Division Name] END,
[First Name] = CASE WHEN [First Name] IS NULL THEN '' ELSE [First Name] END,
[Last Name] = CASE WHEN [Last Name] IS NULL THEN '' ELSE [Last Name] END,
[Last Login] = CASE WHEN (GroupCompLevel=0 AND GroupDivLevel=0 AND GroupUserLevel=1) THEN 'Division Total'
WHEN (GroupCompLevel=0 AND GroupDivLevel=1) THEN 'Company Total'
WHEN (GroupCompLevel=1 AND GroupDivLevel=1) THEN 'Grand Total'
ELSE [Last Login] END,
[Login Count] = CASE WHEN [Login Count]=0 THEN '' ELSE [Login Count] END
FROM @tmpOutput
Where [Last Login] IS NOT NULL OR [First Name] IS NULL
GO
------------------------ TRY 2
set nocount on
declare @tmpSample TABLE
(
vchComp varchar(20),
vchDiv varchar(20),
intUserID int,
vchFName varchar(20),
vchLName varchar(20),
dtLogin datetime
)
DECLARE @tmpOutput TABLE
(
[Company Name] VARCHAR(20),
[Division Name] VARCHAR(20),
[First Name] VARCHAR(20),
[Last Name] VARCHAR(20),
[Last Login] VARCHAR(20),
[Login Count] VARCHAR(6),
[Employee Count] VARCHAR(6) ,
GroupCompLevel INT,
GroupDivLevel INT,
GroupUserLevel INT
)
insert @tmpSample
select 'CompanyA','DivisionA',1,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyA','DivisionA',2,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyA','DivisionA',3,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyA','DivisionA',4,'Lily', 'NotLogged', NULL UNION
select 'CompanyB','SubDiv1',5,'Chris', 'Davis', NULL UNION
select 'CompanyB','SubDiv1',6,'Larry', 'DaCable', '03/12/2010' UNION
select 'CompanyB','SubDiv2',7,'Paul', 'Lynn', '02/18/2010' UNION
select 'CompanyB','SubDiv2',8,'Ron', 'White', NULL UNION
select 'CompanyC','AnotherDIVA',9,'Tom', 'Jones', '06/22/2010' UNION
select 'CompanyC','AnotherDIVB',10,'Bob', 'Smith', '03/12/2010' UNION
select 'CompanyC','AnotherDIVB',11,'Dick', 'Adams', '02/18/2010' UNION
select 'CompanyC','AnotherDIVC',12,'Sam', 'Hobbit', NULL
--SELECT * FROM @tmpSample
INSERT @tmpOutput
SELECT vchComp,
vchDiv,
vchFName,
vchLName,
LOGIN_DATE = LEFT(CONVERT(VARCHAR, dtLogin, 120), 10),
LOGIN_COUNT = COUNT(dtLogin),
EMPLOYEE_COUNT = count(*) ,
GroupCompLevel = GROUPING(vchComp),
GroupDivLevel = GROUPING(vchDiv),
GroupUserLevel = GROUPING(vchFName)
FROM @tmpSample
--WHERE dtLogin IS NOT NULL
GROUP BY GROUPING SETS((vchComp),(vchComp,vchDiv),(vchComp,vchDiv,vchFName,vchLName,dtLogin),())
SELECT * FROM @tmpOutput
SELECT [Company Name] = CASE WHEN [Company Name] IS NULL THEN '' ELSE [Company Name] END,
[Division Name] = CASE WHEN [Division Name] IS NULL THEN '' ELSE [Division Name] END,
[First Name] = CASE WHEN [First Name] IS NULL THEN '' ELSE [First Name] END,
[Last Name] = CASE WHEN [Last Name] IS NULL THEN '' ELSE [Last Name] END,
[Last Login] = CASE WHEN (GroupCompLevel=0 AND GroupDivLevel=0 AND GroupUserLevel=1) THEN 'Division Total'
WHEN (GroupCompLevel=0 AND GroupDivLevel=1) THEN 'Company Total'
WHEN (GroupCompLevel=1 AND GroupDivLevel=1) THEN 'Grand Total'
ELSE [Last Login] END,
[Login Count] = CASE WHEN [Login Count]=0 THEN '' ELSE [Login Count] END,
[Employee Count] = CASE WHEN [Employee Count]=0 THEN '' ELSE [Employee Count] END
FROM @tmpOutput
Where [Last Login] IS NOT NULL OR [First Name] IS NULL
Fitz
June 23, 2010 at 6:25 am
Mark, you da man!
the 2nd option is exactly what was called for. Now I need to grab some asprin from smacking my head so much.
thank you very much!
June 23, 2010 at 3:30 pm
Joe Celko (6/23/2010)
I think you want to use ROLLUP and not GROUPING SET.You mgiht also want to start using ISO-11179 data element names, proper daets and the "VALUES(), (), () " table constrructor symntax.
thanks, I threw something together quickly show an example of what I was trying to accomplish, I'll do it differently next time if it comes to it.
I'll check out your sample codes and see what differences there are between ROLLUP and GROUPING SET.
thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply