How do I get all the values from one table

Question

I am using Microsoft SQL Server. I have two tables

Cost Center - A list of all our production processes.
Scheduled Process - A list of jobs that are scheduled to go through each Cost Center

My table Cost Center looks as below

CostCenterCode  CostCenterDesc
------------------------------
123             Print
456             Stich
789             Fold
999             Ship

My table Process Schedule looks as below

JobNumber   StartDate   ScheduledHours  CostCenterCode
------------------------------------------------------
12345   2020-01-01      33               123
12345   2020-01-01      33               456
12345   2020-01-01      33               999

Now based on my above dataset, cost center 789 does not have anything scheduled. I want to write a query that will show what is scheduled for each cost center each day. Cost Center 789 does not have anything scheduled but it should still show that cost center with 0 hours.

[Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/2029983) What is your question here (you don't ask one)? What have you tried, and why didn't it work? — Thom A, Feb 14 '20 at 15:58
Can you post the table structure, sample data in insert into the script and expected output so it will be easy to help you? So, first, edit your question and include that in your question. — Suraj Kumar, Feb 14 '20 at 16:00

score 0 · Answer 1 · answered Feb 14 '20 at 16:01

0

Use a left join:

select cc.*, ss.*
from costcenter cc left join
     schedule s
     on cc.costcentercode = s.costcentercode

answered Feb 14 '20 at 16:01

Gordon Linoff

1,242,037
58
646
786

I had tried this before, it eliminates cost centers for days its not scheduled. – Parth Kadakia Feb 14 '20 at 17:03

score 0 · Accepted Answer · answered Feb 14 '20 at 16:16

You can try this using Aggregate Functions (Transact-SQL) and the SQL Server join.

create table CostCenter (CostCenterCode int, CostCenterDesc varchar(120))
insert into CostCenter Values(123, 'Print'), (456, 'Stich'), (789, 'Fold'), (999, 'Ship')

Create table ProcessSchedule (JobNumber int, StartDate Date, ScheduledHours int,
CostCenterCode int)
Insert into ProcessSchedule Values (12345, '2020-01-01', 33, 123),
(12345 , '2020-01-01', 33, 456), (12345, '2020-01-01', 33, 999)

;WITH cte
AS (
    SELECT CostCenter.CostCenterCode
        ,CostCenter.CostCenterDesc
        ,ProcessSchedule.StartDate
        ,ISNULL(ProcessSchedule.ScheduledHours, 0) AS ScheduledHours
    FROM CostCenter
    LEFT JOIN ProcessSchedule ON CostCenter.CostCenterCode = ProcessSchedule.CostCenterCode
    )
SELECT CostCenterCode
    ,CostCenterDesc
    ,StartDate
    ,SUM(ScheduledHours) AS TotalScheduledHours
FROM cte
GROUP BY CostCenterCode
    ,CostCenterDesc
    ,StartDate

Live Demo Here

How do I get all the values from one table

2 Answers2