CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Mona
Mona

Reputation: 31

How to assign value to columns from StartDate to EndDate when StartDate is a fixed date, e.g. Jan 2013

I have table like this:

Country Project InvCode InvName  Site  WorkLoad    StartDate   EndDate
USA 1234    2020    Jane Box      101    20       2012-02-10  2013-04-15
USA 1234    2020    Jane Box      102    35       2013-02-10  2013-07-15
USA 2222    2140    James Smith 101 5   2012-10-10  2013-12-05
USA 2222    2590    Susan Falco 410 12  2013-05-10  2015-12-20
USA 2250    2140    James Smith 401 8   2013-04-01  2013-07-18

I added 4 columns to the table: StartYear, StartMonth from StartDate and EndYear, EndMonth from EndDate (hopefully they will help in writing the query)

StartYear   StartMonth EndYear  EndMonth
2012    2     2013   5
2013    2     2013   7
2012    10    2013   12
2013    5     2015   12
2013    4     2013   7

The desired output will look like this. I couldn't fit all months but it should go to the max Month-Year from the EndDate. In my table I will have columns from Jan 2013 through Dec 2015

country|Project|InvCode|INvName|Site|StartDate|EndDate|Jan-13|Feb-13|Mar-13|Apr-13|May-13|.

USA|1234|2020|Jane Box   |101|2012-02-10|2013-05-15|     20 20    20        20     0
USA|1234|   2020|Jane Box   |102|2013-02-10|2013-07-15|     0      35    35     35    35
USA|2222|2140|James Smith|101|2012-10-10|2013-12-05|     5  5      5      5     5
USA|2222|   2590|Susan Falco|410|2013-05-10|2015-12-20|     0   0      0         0   12
USA|2250|2140|James Smith|401|2013-04-01|2013-07-18|      0 0      0      8   8

When the StartDate is before Jan 2013, start with Jan 2013 and assign the same value, e.g., 20 for each month through the EndDate, after the EndDate assign 0 through max EndDate.

When the StartDate is after Jan 2013 assign 0 for the months before the month from the StartDate and starting with the month equal to the month in the StartDate assign the same value for each month through the EndDate, e.g., StartDate is Feb-2013, EndDate is Jul-2013, workload=35, for Jan-2013 value of 0 will be assigned, from Feb-2013 through Jul-2015 value of 35 will be assigned. After End Date assign 0 through max End Date.

I'll appreciate your help!

Upvotes: 0

Views: 166

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Here's a start; you'll need to fill out the columns for the remaining months:

-- MySQL
SELECT
  Country,
  Project,
  InvCode,
  InvName,
  Site,
  StartDate,
  EndDate,
  CASE WHEN 201301 BETWEEN DATE_FORMAT(StartDate, '%Y%m') AND DATE_FORMAT(EndDate, '%Y%m')
     THEN WorkLoad
     ELSE 0 END 'Jan-13',
  CASE WHEN 201302 BETWEEN DATE_FORMAT(StartDate, '%Y%m') AND DATE_FORMAT(EndDate, '%Y%m')
     THEN WorkLoad
     ELSE 0 END 'Feb-13',
... and so on through December
FROM my_table
ORDER BY whatever

Addendum I answered this by the then present tag (MySQL), but later noticed that the title said "SQL Server 2008" (which has since been moved to the tags).

The date "chopping" above would be different in SQL Server 2008, but all the rest should be the same. Because MySQL and SQL Server both support the YEAR and MONTH functions, the following query, though it makes each CASE longer than the original MySQL-only flavor, should work on either database:

-- SQL Server AND MySQL
SELECT
  Country,
  Project,
  InvCode,
  InvName,
  Site,
  StartDate,
  EndDate,
  CASE WHEN 201301 BETWEEN YEAR(StartDate)*100 + MONTH(StartDate) AND YEAR(EndDate)*100 + Month(EndDate)
     THEN WorkLoad
     ELSE 0 END 'Jan-13',
  CASE WHEN 201302 BETWEEN YEAR(StartDate)*100 + MONTH(StartDate) AND YEAR(EndDate)*100 + Month(EndDate)
     THEN WorkLoad
     ELSE 0 END 'Feb-13',
... and so on through December
FROM my_table
ORDER BY whatever

Upvotes: 3

Related Questions