Better SQL script to create a table of aggregated results arranged in a calendar-like format?

Question

What the data looks like in the database:

id | source | createTime
------------------------
1  | pc     | 2019-01-02
2  | mobile | 2019-01-02
3  | pc     | 2019-02-01

What I want is a tabulated result for pc and mobile counts based on the days for each month for a given year:

jan_pc | jan_mobile | feb_pc | feb_mobile
-----------------------------------------
0      | 0          | 1      | 0         <- first row is day 1
-----------------------------------------
1      | 1          | 0      | 0         <- second row is day 2, etc.. all the way to day 31
-----------------------------------------

This is the SQL script I'm currently using (partial shown), where each row represents a day of the month starting from day 1, and the columns are the months + pc/mobile count starting from January, in this example I use a union to create two rows representing days 1 and 2.

select top (1)
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=1 and day(createtime)=1 and source='1') as '1_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=1 and day(createtime)=1 and source='2') as '1_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=2 and day(createtime)=1 and source='1') as '2_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=2 and day(createtime)=1 and source='2') as '2_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=3 and day(createtime)=1 and source='1') as '3_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=3 and day(createtime)=1 and source='2') as '3_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=4 and day(createtime)=1 and source='1') as '4_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=4 and day(createtime)=1 and source='2') as '4_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=5 and day(createtime)=1 and source='1') as '5_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=5 and day(createtime)=1 and source='2') as '5_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=6 and day(createtime)=1 and source='1') as '6_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=6 and day(createtime)=1 and source='2') as '6_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=7 and day(createtime)=1 and source='1') as '7_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=7 and day(createtime)=1 and source='2') as '7_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=8 and day(createtime)=1 and source='1') as '8_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=8 and day(createtime)=1 and source='2') as '8_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=9 and day(createtime)=1 and source='1') as '9_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=9 and day(createtime)=1 and source='2') as '9_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=10 and day(createtime)=1 and source='1') as '10_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=10 and day(createtime)=1 and source='2') as '10_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=11 and day(createtime)=1 and source='1') as '11_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=11 and day(createtime)=1 and source='2') as '11_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=12 and day(createtime)=1 and source='1') as '12_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=12 and day(createtime)=1 and source='2') as '12_M'
from customer

union all

select top (1)
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=1 and day(createtime)=2 and source='1') as '1_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=1 and day(createtime)=2 and source='2') as '1_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=2 and day(createtime)=2 and source='1') as '2_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=2 and day(createtime)=2 and source='2') as '2_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=3 and day(createtime)=2 and source='1') as '3_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=3 and day(createtime)=2 and source='2') as '3_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=4 and day(createtime)=2 and source='1') as '4_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=4 and day(createtime)=2 and source='2') as '4_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=5 and day(createtime)=2 and source='1') as '5_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=5 and day(createtime)=2 and source='2') as '5_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=6 and day(createtime)=2 and source='1') as '6_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=6 and day(createtime)=2 and source='2') as '6_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=7 and day(createtime)=2 and source='1') as '7_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=7 and day(createtime)=2 and source='2') as '7_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=8 and day(createtime)=2 and source='1') as '8_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=8 and day(createtime)=2 and source='2') as '8_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=9 and day(createtime)=2 and source='1') as '9_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=9 and day(createtime)=2 and source='2') as '9_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=10 and day(createtime)=2 and source='1') as '10_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=10 and day(createtime)=2 and source='2') as '10_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=11 and day(createtime)=2 and source='1') as '11_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=11 and day(createtime)=2 and source='2') as '11_M',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=12 and day(createtime)=2 and source='1') as '12_P',
(select
    count(source)
from customer where year(createtime)=2019 and month(createtime)=12 and day(createtime)=2 and source='2') as '12_M'
from customer

I'm sure there is a better way - any suggestions?

Answer 1

    SELECT * FROM 
    (
    SELECT DATEPART(YY,createTime) AS [YearPart],DATEPART(DD,createTime) AS 
    DayNum,CONCAT(SUBSTRING(DATENAME(MONTH,createTime),1,3),'_',source) AS Mon_Src
    FROM Customer
    ) AS SourceTable
    PIVOT
    (
    COUNT(Mon_Src)
    FOR Mon_Src IN ([Jan_pc],[Jan_mobile],[Feb_pc],[Feb_mobile],[Mar_pc], 
                    [Mar_mobile],[Apr_pc],[Apr_mobile],[May_pc],[May_mobile],[Jun_pc],[Jun_mobile], 
                    [Jul_pc],[Jul_mobile],[Aug_pc],[Aug_mobile],[Sep_pc],[Sep_mobile],[Oct_pc], 
                    [Oct_mobile],[Nov_pc]
                   ,[Nov_mobile],[Dec_pc],[Dec_mobile])
    ) AS PivotTable

Answer 2

1. First you need to have a separate Calendar Table, which will make all your date related queries simpler.
2. You need to pivot the results to get the daynumber and corresponding sales.

I have put sample code below for your needs. I have put a simple calendar table for one year. You might need a more rows for Calendar table based on the sales data.

CREATE TABLE #calendar(DateValue DATE)
CREATE TABLE #sales(id INT IDENTITY(1,1) not null, [source] VARCHAR(10), CreateTime DATE)


INSERT INTO #sales([source],CreateTime)
values('pc','2019-01-02'),('mobile','2019-01-02'),('pc','2019-02-01');

DECLARE @s DATE = '2019-01-01' 
DECLARE @e DATE = '2019-12-31' 

-- Loading Calendar Table
;WITH CTE_Numbers AS
(
SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects 
)
INSERT INTO #calendar(DateValue)
SELECT TOP (DATEDIFF(DAY, @s, @e) + 1) 
 d = CONVERT(DATE, DATEADD(DAY, n-1, @s))
 FROM CTE_Numbers;

 SELECT DayNumber, [Jan_PC],[Jan_Mobile],[Feb_PC],[Feb_Mobile]
 FROM
 (
 SELECT DATEPART(Day,DateValue) as DayNumber, CONCAT(LEFT(DATENAME(Month, DateValue),3),'_', s.[source]) as SourceMonth, s.ID
 FROM #calendar AS c
 INNER JOIN #sales AS s
 ON s.CreateTime = c.DateValue) AS ps
 PIVOT 
 (
    COUNT(ps.ID)
    FOR ps.[sourceMonth] IN ([Jan_pc],[Jan_mobile],[Feb_pc],[Feb_Mobile])
 )AS pvt
 ORDER BY DayNumber;

The result is given below for your reference:

+-----------+--------+------------+--------+------------+
| DayNumber | Jan_PC | Jan_Mobile | Feb_PC | Feb_Mobile |
+-----------+--------+------------+--------+------------+
|         1 |      0 |          0 |      1 |          0 |
|         2 |      1 |          1 |      0 |          0 |
+-----------+--------+------------+--------+------------+

References
- Loading Calendar Table
- Loading Number Table