0

I would like to get a pivot table with a different status and has count days from a table without a prepared pivot column in it by using SQL query. I have try use UNION ALL to insert the column, but the query was too long and not practicable and efficiency.

My Table

Data Table

Status  |date
-------------
New     |25/2/2017
Confirm |29/1/2017

Status Table

Status
-------
New
Confirm
Finish

Results I want:

Status | 0-7|8-14|15-21|<30     ---->Days
------------------------------
New    | 1  | 0  | 0   | 1
Confirm| 0  | 0  | 0   | 1
Finish | 0  | 0  | 0   | 0

SOLVED

Solution

Using left join status table and data table, then use case statement and pivot to solved it. Thanks to all who try to help me ^^

Ling
  • 87
  • 1
  • 13
  • 1
    You can use 'Case Statement'. Refer to this http://stackoverflow.com/a/13526616/2960555 – unknownerror Mar 01 '17 at 02:21
  • I hv try to combine using pivot and case, but the result is just showing New and Confirm. I not sure what is the problem are.... – Ling Mar 01 '17 at 04:06
  • I hv find the problem. The problem is there was no data with status Finish in the table. However, I need it to display in sql server 2012. Anyone have any solution for this? – Ling Mar 01 '17 at 04:15

1 Answers1

0

USe below code that will fulfill your requirement. I have used Date format : DDMMYYYY

--Create Table
CREATE TABLE A (Status Varchar(20),DT Date)
--Insert data
INSERT INTO A (Status,DT) VALUES ('New','2/25/2017')
INSERT INTO A (Status,DT) VALUES ('Confirm','01/29/2017')
INSERT INTO A (Status,DT) VALUES ('Finish','01/2/2017')

-- Query to get output
Select Status
       ,(Select CASE WHEN DATEPART(Day,b.DT) <=7  THEN 1 ELSE 0 END from A b WHERE b.Status = a.Status) as '0-7'
       ,(Select CASE WHEN DATEPART(Day,b.DT) <=14 and DATEPART(Day,b.DT)>7 THEN 1 ELSE 0 END from A b WHERE b.Status = a.Status) as '8-14'
      ,(Select CASE WHEN DATEPART(Day,b.DT) <=21 and DATEPART(Day,b.DT)>14 THEN 1 ELSE 0 END from A b WHERE b.Status = a.Status) as '15-21'
      ,(Select CASE WHEN DATEPART(Day,b.DT) <=30 and DATEPART(Day,b.DT)>21 THEN 1 ELSE 0 END from A b WHERE b.Status = a.Status) as '<30'
from A a
Parag
  • 46
  • 3