Solved

T-SQL: flattening rows with counts

Posted on 2012-03-12
1
361 Views
Last Modified: 2012-03-13
Techies--

A simplified version of the data I have looks like this:

Store      DateHired      DateTerminated      
10      20090701
10      20110120                     20110817
10      20010601                     20120120
20      20030110                     20110817
20      20110802                     20110817
20              20110817      
20      20110817
20      20110817


What I want back looks like this:

Date            Store      HireCount      TerminatedCount
20010601                      10      1            NULL
20030110                      20      1            NULL
20090701                      10      1            NULL
20110120                      10      1            NULL
20110802                      20      1            NULL
20110817                      10                  1
20110817                      20      3            2
20110817                      20                  2


How do achieve this?  Please advise.
0
Comment
Question by:ditallop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 37711241
Something like this would work.  

DECLARE @Table TABLE(store INT,datehired VARCHAR(10), DateTerminated VARCHAR(10))


INSERT INTO @Table 
        (store, datehired, DateTerminated)
VALUES
        (10,'20090701',NULL)
		,(20,'20110120','20110817')
		,(20,'20110817',NULL)
		,(20,'20110817',NULL)


;WITH CTE AS (
SELECT
	Store
	,DateHired
	,1 Hired
FROM
	@Table
UNION ALL
SELECT
	Store
	,DateTerminated
	,0
FROM
	@Table
WHERE
	DateTerminated IS NOT NULL)

SELECT 
	c.DateHired
	,c.Store	
	,SUM(CASE WHEN Hired = 1 THEN 1 ELSE 0 END) HireCount
	,SUM(CASE WHEN Hired = 0 THEN 1 ELSE 0 END) TerminatedCount
FROM 
	CTE c
GROUP BY
	c.Store
	,c.DateHired

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 58
Need help with a Stored Proc on Sql Server 2012 4 35
Are triggers slow? 7 23
SQL- GROUP BY 4 22
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

762 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