CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008
Si8
Si8

Reputation: 9225

How to get monthly total in SQL

I have

The following query which gets the total for the day:

SELECT *
FROM [database].[dbo].[table]

The following query which gets the total for the week:

SELECT * , DATENAME(WEEKDAY, Date)
FROM [database].[dbo].[table]
WHERE Date >= cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)

How can I write a query which will give me the total for the month, given it starts from the first to the end of the month only?

The table looks like the following and it will continue to add itself for each day:

enter image description here

So tomorrow it will be entry #4 and date will be 2014-04-11 and so on and so on...

I will need to query which will be called in my Datasets for my SSRS report to create a bar chart.

The original query which the image is displayed for is:

INSERT INTO mydatabase.dbo.TABLE
Select getdate(),
Count([Draw Date]),
(Select Count([Draw Date]) from mydatabase.dbo.[radClinicalInHouseLab]) AS [IN HOUSE],
(Select Count([Column 0]) from mydatabase.dbo.[radRadOrders]) AS [RAD EMR ORDERS],
(Select Count([Value]) from mydatabase.dbo.[radEMROrders]) AS [EMR ORDERS],
(Select Count([Draw Date]) from mydatabase.dbo.[radAccessLabOrders]) AS [ACCESSIONED LAB ORDERS],
(Select Count([Draw Date]) from mydatabase.dbo.[radAPSurgical]) AS [AP SURGICAL],
(Select Count([Draw Date]) from mydatabase.dbo.[radClinicalRefLab]) AS [REF LAB],
(Select Count([Column 0]) from mydatabase.dbo.[radRadResults]) AS [RAD RESULTED],
(Select Count([Draw Date]) from mydatabase.dbo.[radLabResults]) AS [LAB_RESULT], --added 4/4
(Select Count([Draw Date]) from mydatabase.dbo.[radResultedLabOrders]) AS [RESULTED LAB], --4/4
(Select Count([Draw Date]) from mydatabase.dbo.[radAPCytology]) AS [AP CYTOLOGY MOLECULAR],
(Select Count([Draw Date]) from mydatabase.dbo.[radAutoAccessRadOrders]) AS [RAD ACCESSIONED],
(Select Count([Draw Date]) from mydatabase.dbo.[radPendingLabResults]) AS [PENDING LAB], --4/4
((Select Count([Value]) from mydatabase.dbo.[radEMROrders]) - (Select Count([Draw Date]) from mydatabase.dbo.[radAccessLabOrders])) AS [PENDING FOR PROCESSING],
(Select Count([Draw Date]) from mydatabase.dbo.[radChemInHouse]) AS [CHEMISTRY],
(Select Count([Draw Date]) from mydatabase.dbo.[radBloodBank]) AS [BLOOD BANK],
(Select Count([Draw Date]) from mydatabase.dbo.[radHemotology]),
(Select Count([Draw Date]) from mydatabase.dbo.[radMicrobiology]),
(Select Count([Draw Date]) from mydatabase.dbo.[radSerology]),
(Select Count([Draw Date]) from mydatabase.dbo.[radUrinalysis])
FROM mydatabase.dbo.[radAPDERM]

For some reason I can't get the following query to display the same Date format:

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),110) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),110) ,
'Last Day of Current Month'

which displays:

enter image description here

Result table:

enter image description here

APDERM  IN HOUSE    RAD EMR ORDERS  EMR ORDERS  ACCESSIONED LAB ORDERS  AP SURGICAL REF LAB RAD RESULTED    LAB_RESULT  RESULTED LAB    AP CYTOLOGY MOLECULAR   RAD ACCESSIONED PENDING LAB PENDING FOR PROCESSING  CHEMISTRY   BLOOD BANK  HEMOTOLOGY  MICROBIOLOGY    SEROLOGY    URINALYSIS
31  5122    35404   271265  7165    106 790 24736   61807   0   311 1122    2987    264100  4193    42  971 100 67  389

With query:

enter image description here

Upvotes: 1

Views: 108

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

DECLARE @DATE DATE
SET @DATE = GETDATE()
SELECT SUM(APDERM) as total
FROM [database].[dbo].[table]
WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE) -- the first day of current month
  AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE) -- the first day of next month

You can refer to the post to get helper UDFs.

Upvotes: 1

Related Questions