CODEWITHSUNDEEP
sqldatabasepostgresql
Naveen Reddy Marthala
Naveen Reddy Marthala

Reputation: 3123

Select counts, calculate date differences along with group by

I have a table that look like this:

date_one    date_two   categorical_col_1 categorical_col_2 categorical_col_3
2019-01-01  2019-01-04  category_11         category_21        M
2019-01-01  2019-01-04  category_11         category_21        M
2019-01-02  2019-01-24  category_11         category_22        M
2019-01-03  2019-01-14  category_12         category_21        F
2019-01-03  2019-01-02  category_12         category_22        F
.....
2019-08-02              category_11         category_22        M
2019-08-08  2019-08-16  category_12         category_21        F
2019-08-08  2019-08-16  category_12         category_21        F

I now want to make a table that has:
1. five columns with date_one column expressed in different formats(date, week, month, quarter, year)
2. one column with difference between two date columns(date_one and date_two, empty rows in date_two column has to be filled with today's date) and another column with counts; all while grouping by pre-exisiting categorical columns(categorical_col_1,categorical_col_2,categorical_col_3).

date_1_date  date_1_week  date_1_month  date_1_quarter  date_1_year  counts  dates_difference categorical_col_1 categorical_col_2 categorical_col_3
2019-01-01    W0-2019      Jan-2019       Q1-2019           2019          2      3                category_11           category_22              M   
...
2019-08-08    W30-2019     Aug-2019       Q3-2019           2019          2      8                category_12           category_21              F

Note: first day of week is Monday, not Sunday(like %W in datetime.strftime() method of Python3)

Here's what I tried:

select count(date_one),categorical_col_1,categorical_col_2,categorical_col_3 from main_table_raw group by (categorical_col_1,categorical_col_2,categorical_col_3);

and 

select date_one, date_part('day',age(date_two, date_one)) from main_table_raw;

using group by in second query produced error. and i also couldn't find a way to combine above both queries to get the result i want and also don't know how nulls can be filled with today's date?

How do i do this?

EDIT 1:
1. All null values in date_two to be filled with today's date.
2. make a column with difference between date_two and date_one, in days. name of this column is dates_difference.
3. now take counts of number of rows, when grouped by date_one, dates_difference, categorical_column_1, categorical_col_2 and categorical_col_3. Name of this column with count of rows is counts. (i no-more want date_two in this table.)
4. week, month, quarter and year versions of date_one has to be made now. that is how i need my table to be made.

EDIT 2:
week numbers I have in my sample solution are, as specified in documentation:

Week number of the year (Monday as the first day of the week) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.

That's how i get my numbers. And i prepend 'W' and append '-year'. Ex: 22nd week of year will be: W22-19

Upvotes: 0

Views: 85

Answers (1)

Hardik Vyas
Hardik Vyas

Reputation: 56

As I understand your question, You want date_one, date_two, categorical_col_1, categorical_col_2, categorical_col_3 all with count. How is that possible? You want count of what? Because if you want this columns then you will have to group all this columns which will not serve any purpose.

Elaborate your requirement clear for better understanding.

Have a look at below query. It will work without count

    SET DATEFIRST 1
    SELECT  CONVERT(DATE,date_one) AS date_1_date,
            DatePart(week, date_one) AS date_1_week,
            MONTH(date_one) AS date_one_month,
            CONCAT('Q'+ convert(varchar,DATEPART(qq,getdate())),'-',datepart(yyyy,getdate())) AS date_1_quarter,
            YEAR(date_one) AS date_one_year,
            DATEDIFF(DAY,date_one,CASE WHEN date_two IS NULL THEN GETDATE() ELSE date_two END) AS dates_difference,
            categorical_col_1,categorical_col_2,categorical_col_3
    FROM    main_table_raw

Upvotes: 1

Related Questions