Brain Freeze

Transact-SQL
1

Trying to generate data for a chart report. looking to get the count of records for each new_submissionstatus.all I get with this query is 'no Colum name' where I'm looking for 'Unsubmitted', Accepted etc

SELECT COUNT(CASE new_submissionstatus
WHEN 1 THEN 'UNSUBMITTED'
WHEN 2 THEN 'ACCEPTED'
WHEN 3 THEN 'PROCESSING'
WHEN 4 THEN 'SUBMITTED'
WHEN 5 THEN 'REJECTED'
WHEN 6 THEN 'EXTRACTED'
WHEN 7 THEN 'APPROVAL'
END )
FROM [Operations_MSCRM].[dbo].[new_transactionsets]
Where DATEDIFF(d, new_submitteddate, GETDATE()) = 0
GROUP BY [new_submissionstatus]

2

Please post the table structure with sample data.

3

CREATE TABLE [dbo].[new_transactionsets](
[new_submissionstatus] [int] NOT NULL,
[new_submitteddate] [date] NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[new_transactionsets] ([new_submissionstatus] ,[new_submitteddate]) VALUES (2,Getdate())
GO
INSERT INTO [dbo].[new_transactionsets] ([new_submissionstatus] ,[new_submitteddate]) VALUES (3,Getdate())
GO
INSERT INTO [dbo].[new_transactionsets] ([new_submissionstatus] ,[new_submitteddate]) VALUES (2,Getdate())
GO
INSERT INTO [dbo].[new_transactionsets] ([new_submissionstatus] ,[new_submitteddate]) VALUES (4,Getdate())
GO
INSERT INTO [dbo].[new_transactionsets] ([new_submissionstatus] ,[new_submitteddate]) VALUES (6,Getdate())
GO

should end up with count of 2 for accepted , 1 for processing, 1 for submitted and 1 for extracted

4 
SELECT 
    SUM(CASE WHEN new_submissionstatus = 1 THEN 1 ELSE 0 END) AS UNSUBMITTED,
    SUM(CASE WHEN new_submissionstatus = 2 THEN 1 ELSE 0 END) AS ACCEPTED,
    SUM(CASE WHEN new_submissionstatus = 3 THEN 1 ELSE 0 END) AS PROCESSING,
    SUM(CASE WHEN new_submissionstatus = 4 THEN 1 ELSE 0 END) AS SUBMITTED,
    SUM(CASE WHEN new_submissionstatus = 5 THEN 1 ELSE 0 END) AS REJECTED,
    SUM(CASE WHEN new_submissionstatus = 6 THEN 1 ELSE 0 END) AS EXTRACTED,
    SUM(CASE WHEN new_submissionstatus = 7 THEN 1 ELSE 0 END) AS APPROVED
FROM [Operations_MSCRM].[dbo].[new_transactionsets]
WHERE new_submitteddate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)