SQL Query Month postion help

Transact-SQL
1

Hi Guys

i have the table below

employee start date sale date Sales Amount
Ian Jan-20 Jan-20 2
Ian Jan-20 Feb-20 5
Ian Jan-20 Mar-20 6
Ian Jan-20 Apr-20 10
Ian Jan-20 May-20 12
John Feb-20 Feb-20 7
John Feb-20 Mar-20 1
John Feb-20 Apr-20 5
John Feb-20 May-20 0
John Feb-20 Jun-20 11

I want to a create a query to add another column to show which month potion is the sales month from the employee start date ( e.g 1st month second month etc)

so the outcome i want is below

employee start date sale date Sales Amount month number
Ian Jan-20 Jan-20 2 1
Ian Jan-20 Feb-20 5 2
Ian Jan-20 Mar-20 6 3
Ian Jan-20 Apr-20 10 4
Ian Jan-20 May-20 12 5
John Feb-20 Feb-20 7 1
John Feb-20 Mar-20 1 2
John Feb-20 Apr-20 5 3
John Feb-20 May-20 0 4
John Feb-20 Jun-20 11 5

i am struggling to do this, can this be done? i

thank you for any help

2

please provide the data in real useable SQL Script format

declare @mango table(employee varchar(50), start_date date, 
sale_date date, salesAmount money)

insert into @mango
select 'Ian', '2020-01-01', '2020-01-01', 2

knowing what data types start_date & sale_date is critical

Help us help you

3 
SELECT
    employee, [start date], [sale date], [Sales Amount],
    CAST(DATEDIFF(MONTH, '01-' + [start date], '01-' + [sale date]) + 1 
        AS varchar(2)) AS [month number]
FROM dbo.table_name
1 Like
4

hi

this is one of several approaches .. Scotts is a better way ... but this is just another way

please click arrow for drop create data script 
create table test_data
(
employee varchar(10),start_date	varchar(10) , sale_date	varchar(10) , Sales_Amount int 
)

insert into test_data select 'Ian	','Jan-20','Jan-20',2
insert into test_data select 'Ian	','Jan-20','Feb-20',5
insert into test_data select 'Ian	','Jan-20','Mar-20',6
insert into test_data select 'Ian	','Jan-20','Apr-20',10
insert into test_data select 'Ian	','Jan-20','May-20',12
insert into test_data select 'John	','Feb-20','Feb-20',7
insert into test_data select 'John	','Feb-20','Mar-20',1
insert into test_data select 'John	','Feb-20','Apr-20',5
insert into test_data select 'John	','Feb-20','May-20',0
insert into test_data select 'John	','Feb-20','Jun-20',11

;
WITH tally_month
     AS (SELECT 'Jan' AS mth,
                1     AS mth_nbr
         UNION ALL
         SELECT 'Feb',
                2
         UNION ALL
         SELECT 'Mar',
                3
         UNION ALL
         SELECT 'Apr',
                4
         UNION ALL
         SELECT 'May',
                5
         UNION ALL
         SELECT 'Jun',
                6
         UNION ALL
         SELECT 'Jul',
                7
         UNION ALL
         SELECT 'Aug',
                8
         UNION ALL
         SELECT 'Sep',
                9
         UNION ALL
         SELECT 'Oct',
                10
         UNION ALL
         SELECT 'Nov',
                11
         UNION ALL
         SELECT 'Dec',
                12)
SELECT b.*,
       a.mth_nbr
FROM   tally_month a
       JOIN test_data b
         ON a.mth = LEFT(b.sale_date, 3)

image

5

@harishgg1:

For John, I believe Feb is month 1, thus Mar would be month 2, etc. That is, month number is relative to the starting month.

1 Like
6

oh

did not catch that detail

Thanks Scott