Solved

t-sql: Keeping track of cumulative counts

Posted on 2012-03-13
6
417 Views
Last Modified: 2012-03-13
Techies--

Here is a distilled version of the source data:

dept      empNum            dateHired            dateTerm
10      100            3/1/2008
10      120            4/17/2008
10      122            4/18/2008            6/2/2011
10      138            5/7/2008
10            200            6/2/2011
10      201            6/2/2011
200      195            6/1/2011
200      196            6/1/2011
200      197            6/1/2011
200      207            6/3/2011      


Here is what I am trying to achieve:

dept      Date            HowManyHired      HowManyTerm      ActiveEmpls
10      6/1/2011            0            0            4
10      6/2/2011            2            1            5
10      6/3/2011            0            0            5
20      6/1/2011            3            0            3
20      6/2/2011            0            0            3
20      6/3/2011            1            0            4

Basically, I need to keep a cumulative tally of the active employees for a given department--taking into account hirings and terminations.

How do I accomplish this?

Also, I'm looking for some reference material on writing SQL for reports--or in general, understanding the concepts behind more complex SQL. If you have any references in mind, I am a willing student!:-)
0
Comment
Question by:ditallop
6 Comments
 
LVL 25

Expert Comment

by:jogos
Comment Utility
Notice that you have to replace the TODO with the expression that you find that evaluates an employee as active

select x.dept, x.dateHired, sum(HowManyHired) as  HowManyHired, sum(howmanyTerm) as howmanyTerm , sum(asActiveEmpls) as asActiveEmpls
from 
select dept , dateHired ,count(*) as HowManyHired  ,    0 as HowManyTerm
, sum(case when TODO then 1 else 0 ) as asActiveEmpls
from dbo.tableA
group by dept, dateHired
union all 
select dept,  dateTerm, 0 as HowManyHired , count (*)  as     HowManyTerm, 0 asActiveEmpls
From dbo.tableA 
where dateterm is not null
group by dept, dateHired
) as x
order by x.dept, x.dateHired
GROUP by x.dept, x.dateHired

Open in new window

0
 

Author Comment

by:ditallop
Comment Utility
Thanks Jogos-- the determining factor for active or non-active employees is the null value in the dateTerm. So if dateTerm is null, then that is an active employee if dateTerm is not null then that is an inactive employee.  How would I work that into a case statement with a sum() function?
0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
I see, it's including the other records

select x.dept, x.dateHired, sum(HowManyHired) as  HowManyHired, sum(howmanyTerm) as howmanyTerm , sum(asActiveEmpls) as asActiveEmpls
from 
select t1.dept , t1.dateHired ,count(*) as HowManyHired  ,    0 as HowManyTerm
, (select count(*) 
  from dbo.tableA as t2
  where t2.dept = t1.dept 
  and t2.dateHired <= t1.dateHired
  and (t2.dateTerm is null or t2.dateTerm >= t1.dateHired)
  ) as asActiveEmpls
from dbo.tableA as t1
group by t1.dept, t1.dateHired
union all 
select dept,  dateTerm, 0 as HowManyHired , count (*)  as     HowManyTerm, 0 asActiveEmpls
From dbo.tableA 
where dateterm is not null
group by dept, dateHired
) as x
order by x.dept, x.dateHired
GROUP by x.dept, x.dateHire

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 18

Expert Comment

by:lludden
Comment Utility
Here is my take:

DECLARE @T TABLE (DepID INT, EmpID INT, HireDate DATE, TermDate DATE)
INSERT INTO @T SELECT 10, 100, '3/1/2008',NULL
INSERT INTO @T SELECT 10, 120, '4/17/2008',NULL
INSERT INTO @T SELECT 10, 122, '4/18/2008', '6/2/2011'
INSERT INTO @T SELECT 10, 138, '5/7/2008',NULL
INSERT INTO @T SELECT 10 ,200 ,'6/2/2011',NULL
INSERT INTO @T SELECT 10, 201, '6/2/2011',NULL
INSERT INTO @T SELECT 200 ,195 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,196 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,197 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,207 ,'6/3/2011',NULL


DECLARE @StartDate DATE = '2011-06-01'
DECLARE @EndDate DATE = '2011-06-05'
;WITH --Generate a tally table from 0 to 9999
  N1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  N2(N) AS (SELECT 1 FROM N1 a, N1 b),
  N3(N) AS (SELECT 1 FROM N2 a, N2 b),
  Numbers(N) AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM N3),
DL(CalDate, DeptID) AS (SELECT dateadd(day,Numbers.n,@StartDate), D.DepID
FROM Numbers
CROSS JOIN (SELECT DISTINCT DepID FROM @T) D
WHERE dateadd(day,Numbers.n,@StartDate) <= @EndDate
)
SELECT DL.DeptID, DL.CalDate, (SELECT count(*) FROM @T T WHERE T.HireDate= DL.CalDate AND T.DepID = DL.DeptID) AS Hired,
(SELECT count(*) FROM @T T WHERE T.TermDate= DL.CalDate) AS Termed,
(SELECT count(*) FROM @T T WHERE T.HireDate< DL.CalDate) AS  Active
 FROM dl

Open in new window


The first part just builds a temp table to test with (replace any references to it with your table).

Since we want to be able to (potentially) check status for all dates, not just dates with activities, we need to build a table to contain data to query off of, so we first build a numbers or tally table.  
We use this to calculate a list of every date for every department (the CROSS JOIN).  This gives us a table of every dept and every date in the range.  Then we run through this table and check each date/dept pair for count, term, and additions.  We could test for null for term date, or just use a date that is greater than the time frame of our query.

It is real handy to have a numbers table and a calendar table in your database for queries such as this.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
I tried for improved efficiency, in particular to do only ONE scan/search of the original table.


SET NOCOUNT ON
-- create sample data
DECLARE @employee_status TABLE (dept INT, empNum INT, dateHired DATE, dateTerm DATE)
INSERT INTO @employee_status SELECT 10, 100, '3/1/2008',NULL
INSERT INTO @employee_status SELECT 10, 120, '4/17/2008',NULL
INSERT INTO @employee_status SELECT 10, 122, '4/18/2008', '6/2/2011'
INSERT INTO @employee_status SELECT 10, 138, '5/7/2008',NULL
INSERT INTO @employee_status SELECT 10 ,200 ,'6/2/2011',NULL
INSERT INTO @employee_status SELECT 10, 201, '6/2/2011',NULL
INSERT INTO @employee_status SELECT 200 ,195 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,196 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,197 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,207 ,'6/3/2011',NULL
--
SET NOCOUNT OFF



-- main code begins here

DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @base_active int

SET @startDate = '20110601'
SET @endDate = '20110607'


DECLARE @empl_chgs TABLE (
    dept int,
    date datetime,
    base_active int,
    how_many_hired int,
    how_many_term int,
    PRIMARY KEY (dept, date)
    )
   
INSERT INTO @empl_chgs ( dept, date, base_active, how_many_hired, how_many_term )
SELECT
    dept AS dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END AS date,    
    SUM(CASE WHEN dateHired <= @startDate THEN 1 ELSE 0 END) AS base_active,
    SUM(CASE WHEN action = 'Hire' AND dateHired BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyHired,
    SUM(CASE WHEN action = 'Term' AND dateTerm BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyTerm  
FROM @employee_status
CROSS JOIN (
    SELECT 'Hire' AS action UNION ALL SELECT 'Term'
) AS Actions
WHERE
    dateHired <= @endDate AND
    (action = 'Hire' OR (action = 'Term' AND dateTerm IS NOT NULL))
GROUP BY
    dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END
ORDER BY
    dept, date
   
--SELECT * FROM @employee_status
--SELECT * FROM @empl_chgs

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS dayCounter
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]    
)
SELECT
    depts.Dept, dates.Date,
    COALESCE(ec.how_many_hired, 0) AS HowManyHired, COALESCE(ec.how_many_term, 0) AS HowManyTerm,
    (SELECT SUM(CASE WHEN ec2.date = '19000101' THEN ec2.base_active ELSE ec2.how_many_hired END) -
        SUM(CASE WHEN ec2.date = '19000101' THEN 0 ELSE ec2.how_many_term END)
    FROM @empl_chgs ec2
    WHERE
        ec2.dept = depts.dept AND
        (ec2.date = '19000101' OR ec2.date <= dates.date)
    ) AS ActiveEmpls
FROM (
    SELECT DATEADD(DAY, dayCounter, @startDate) AS date
    FROM cteTally
    WHERE
        dayCounter BETWEEN 0 AND DATEDIFF(DAY, @startDate, @endDate)
) AS dates
CROSS JOIN (
    SELECT DISTINCT dept
    FROM @empl_chgs
) AS depts
LEFT OUTER JOIN @empl_chgs ec ON
    ec.dept = depts.dept AND
    ec.date = dates.date
ORDER BY
    depts.dept, dates.date
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Minor correction, changing one "<=" to "<":
[Won't even affect the results now, but it's technically more accurate, and in case you change the query later, it might be significant]

....

INSERT INTO @empl_chgs ( dept, date, base_active, how_many_hired, how_many_term )
SELECT
    dept AS dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END    --**** changed "<=" to "<" ****
        ELSE dateTerm END AS date,    
    SUM(CASE WHEN dateHired <= @startDate THEN 1 ELSE 0 END) AS base_active,
    SUM(CASE WHEN action = 'Hire' AND dateHired BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyHired,
    SUM(CASE WHEN action = 'Term' AND dateTerm BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyTerm  
FROM @employee_status
CROSS JOIN (
    SELECT 'Hire' AS action UNION ALL SELECT 'Term'
) AS Actions
WHERE
    dateHired <= @endDate AND
    (action = 'Hire' OR (action = 'Term' AND dateTerm IS NOT NULL))
GROUP BY
    dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END
ORDER BY
    dept, date

...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now