How to combine the value of two columns into one column without duplicating the values in SQL?-CodePudding

I have two Select queries:

Select #1:

select 
    Date_Booking as 'Date', isnull(sum(Ticket_Price), 0) as 'Total sales' 
from
    (select 
         Date_Booking, Ticket_Price
     from 
         Booking 
     where
         month(Date_Booking) = 2 and year(Date_Booking) = 2022 
     union all
     select Date_Shipping, Cost_Shipping
     from Shipping 
     where month(Date_Shipping) = 2 and year(Date_Shipping) = 2022) t 
group by 
    Date_Booking

Select #2:

select 
    Date as 'Date', isnull(sum(Gas_Cost), 0) as 'Total Expenses'
from 
    (select Date, Gas_Cost
     from BusGas 
     where month(Date) = 2 and year(Date) = 2022
     union all
     select Date, Wash_Cost
     from BusWash 
     where month(Date) = 2 and year(Date) = 2022
     union all
     select Date, Drive_Money
     from Driver_Expenses 
     where month(Date) = 2 and year(Date) = 2022
     union all
     select Date, Emp_money
     from Emp_Expenses 
     where month(Date) = 2 and year(Date) = 2022
     union all
     select Date, Cost
     from OtherExpenses 
     where month(Date) = 2 and year(Date) = 2022
     union all
     select Date, ServiceRoad_Cost
     from RoadServices 
     where month(Date) = 2 and year(Date) = 2022) t 
group by 
    Date

Now, I want to combine these results so that I have three columns date, total sales and total expenses, and the date with no sales or expenses whose value is 0.

How can fix this problem?

CodePudding user response：

You need to follow below steps:

Create CalenderTable for the date range. Refer to great script by Aaron Bertnard at MSSQLTips

SET DATEFIRST 7, LANGUAGE us_english;
DECLARE @StartDate date = '20200101', 
        @Years     int  = 30;
;WITH seq(n) AS 
(
  SELECT 1 UNION ALL SELECT n   1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, DATEADD(YEAR, @Years, @StartDate))
),
d(d) AS 
(
  SELECT DATEADD(DAY, n - 1, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date,       d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(QUARTER,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
dim AS
(
  SELECT
    TheDate, 
    TheDay,
    TheDaySuffix        = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE 
                            CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
                            WHEN '3' THEN 'rd' ELSE 'th' END END),
    TheDayName,
    TheDayOfWeek,
    TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
                            (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    IsWeekend           = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST 
                            WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
                            THEN 1 ELSE 0 END,
    TheWeek,
    TheISOweek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheISOYear          = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1 
                            WHEN TheMonth = 12 AND TheISOWeek = 1  THEN -1 ELSE 0 END,      
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheLastOfYear,
    IsLeapYear          = CONVERT(bit, CASE WHEN (TheYear % 400 = 0) 
                            OR (TheYear % 4 = 0 AND TheYear % 100 <> 0) 
                            THEN 1 ELSE 0 END),
    Has53Weeks          = CASE WHEN DATEPART(WEEK,     TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    Has53ISOWeeks       = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                            CONVERT(char(4), TheYear),
    Style101            = CONVERT(char(10), TheDate, 101),
    Style103            = CONVERT(char(10), TheDate, 103),
    Style112            = CONVERT(char(8),  TheDate, 112),
    Style120            = CONVERT(char(10), TheDate, 120)
  FROM src
)
SELECT * 
  INTO dbo.Calendar
  FROM dim
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);
GO
ALTER TABLE dbo.Calendar ALTER COLUMN TheDate date NOT NULL;
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED(TheDate);
GO

Now, create two common table expressions and use calender table to join with these two CTEs.

;WITH cte_DateSales AS
(
select Date_Booking as 'Date',ISNULL(sum(Ticket_Price),0)as 'Total sales' from (
select Date_Booking,Ticket_Price
from Booking WHERE MONTH(Date_Booking) = 2  and YEAR(Date_Booking) = 2022 
union all
select Date_Shipping,Cost_Shipping
from Shipping WHERE MONTH(Date_Shipping) = 2  and YEAR(Date_Shipping) = 2022 ) t group by Date_Booking
), cte_TotalExpenses AS 
(
select Date as 'Date',ISNULL(sum(Gas_Cost),0) as 'Total Expenses'from (
select Date,Gas_Cost
from BusGas WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
union all
select Date,Wash_Cost
from BusWash WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
union all
select Date,Drive_Money
from Driver_Expenses WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
union all
select Date,Emp_money
from Emp_Expenses WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
union all
select Date,Cost
from OtherExpenses WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
union all
select Date,ServiceRoad_Cost
from RoadServices WHERE MONTH(Date) = 2  and YEAR(Date) = 2022 ) t group by Date
)
SELECT c.Date, s.[Total Sales], e.[Total Expenses]
FROM dbo.Calendar AS c
LEFT OUTER JOIN cte_DateSales  AS s
ON s.Date = c.Date
LEFT OUTER JOIN cte_TotalExpenses AS e
ON e.Date = c.Date

CodePudding user response：

You simply extend the technique you already use. Use UNION ALL to include all the expense and sales queries and add a "flag" to use conditional aggregation.

select [Date], 
   sum(case IsSale when 1 then Value else 0     end) as [Total sales], 
   sum(case IsSale when 1 then 0     else Value end) as [Total Expenses]
from (
    select Date_Booking as [Date], Ticket_Price as Value, cast(1 as bit) as IsSale
    from Booking WHERE MONTH(Date_Booking) = 2  and YEAR(Date_Booking) = 2022
    union all 
    select Date, Gas_Cost, 0 
    from BusGas WHERE MONTH(Date) = 2  and YEAR(Date) = 2022
    union all 
    ... 
) as combined 
group by [Date] 
order by ...;

The query that forms the derived table could be put into a view so that you can reuse the logic - which is likely in my experience. If you do that, do not include the date filter so you can use it with any period. There are better ways to filter by date but that is a different issue.

You will need to add the calendar table as already noted to force the inclusion of dates that do not exist within the various transaction tables. The