Databases
StackOverflow2013
Postsdbo
POObjective: Create Staff Turnover Report from scratch
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POObjective: Create Staff Turnover Report from scratch
    primarykey
    data
    text
    <p>I have a requirement to create staff turnover report that should display following information according to parameterized period:</p> <ol> <li>current staff</li> <li>starters</li> <li>leavers</li> <li>manager</li> <li>contracted site</li> </ol> <p>Currently, I have a few ideas how to get a report. Either create slowly changing dimension or copy snapshots of employees table for each period or track starter and leavers seperately for each payrun. I have tried slowly changing dimension and it did fine job on new starters; however, it failed on leavers. I am not sure if tracking starters and leaver for each payruns would work.</p> <p><strong>Created a table below as a start of solution</strong></p> <p>I am relatively new in MS SQL 2008 business. Please adise. The main goal is to identify managers and contracted sites with highest staff turnover.</p> <p><strong>Notes</strong> -Currently, I am not using any specific techonlogy and looking for solution. </p> <p>-I do intend to build and design a brand new database for the sake of the report. By <em>database</em> I mean a few collection of tables that sit on CRM database (SQL 2008). By <em>Collection of tables</em> I mean "dimension" table for managers, "dimension" table of contracted sites, and a table of staff. So currently, I think that my solution should have at least 3 tables; however, my skills are not that good to solve the puzzle.</p> <p>I think that have tables above would let me to write a SQL query that could compare different periods and get required results. </p> <p>payno depcod Idd Imported</p> <p>12568 EDE322001 12568EDE322001A 31 December 2011</p> <p>12568 EDE322001 12568EDE322001B 31 December 2011</p> <p>16822 EDE322001 16822EDE322001A 31 December 2011</p> <p>17694 EDE322001 17694EDE322001A 31 December 2011</p> <p>12568 EDE322001 12568EDE322001A 04 January 2012</p> <p>12568 EDE322001 12568EDE322001B 04 January 2012</p> <p>16822 EDE322001 16822EDE322001A 04 January 2012</p> <p>17694 EDE322001 17694EDE322001A 04 January 2012</p> <p>12568 EDE322001 12568EDE322001A 31 January 2012</p> <p>12568 EDE322001 12568EDE322001B 31 January 2012</p> <p>16822 EDE322001 16822EDE322001A 31 January 2012</p> <p>17694 EDE322001 17694EDE322001A 31 January 2012</p> <p>17661 EDE322001 17661EDE322001A 31 January 2012</p> <p>12568 EDE322001 12568EDE322001A 01 February 2012</p> <p>12568 EDE322001 12568EDE322001B 01 February 2012</p> <p>16822 EDE322001 16822EDE322001A 01 February 2012</p> <p>17906 EDE322001 17906EDE322001A 01 February 2012</p> <p>17907 EDE322001 17907EDE322001A 01 February 2012</p> <p>12568 EDE322001 12568EDE322001A 29 February 2012</p> <p>12568 EDE322001 12568EDE322001B 29 February 2012</p> <p>17907 EDE322001 17907EDE322001A 29 February 2012</p> <p>Table above is table of snapshots. Snapshot date is displayed in Imported Column. ( Applogoes for messy table; I could not figure out how to make a table) Now i need to find a away how to compare one date to another (ideally in the loop) to figure out the difference between 1st date and following date.</p> <p>For instance, 17661 EDE322001 17661EDE322001A 31 January 2012 is a new staff and 16822 EDE322001 16822EDE322001A 01 February 2012 is a leaver.</p> <p>Many thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBI Dude
    1. USBI Dude
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAt the moment, this question is rather open-ended. Can you amend it to be more specific? Additionally, it isn't clear whether you are using a specific data-warehousing methodology (although the rreference to SCDs sounds specific to Kimball), nor how you represented employees in your SCD (there are at least three different types of SCD in the Kimball methodology).
      singulars
      1. POObjective: Create Staff Turnover Report from scratch
      1. USJosef K
    2. COThanks Mark for your reply. I have used historical SCD and only for testing purposes. I am looking for advice/solution which way to go to create report with requirements above. It is open ended question.
      singulars
      1. POObjective: Create Staff Turnover Report from scratch
      1. USBI Dude
    3. CO@TadasV please read the meta entries on open ended and wide ranging questions. As you're a newbie I'm loathe to mark this as unacceptably so.
      singulars
      1. POObjective: Create Staff Turnover Report from scratch
      1. USMD-Tech
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload