SQL Query to get all days of month with specific data

Joined:	10/02/2012
Level:	Silver
Points:	101

Points:	5

Hello,

I have one table STOCK_REGISTER where I have item wise transaction data as below
ITM_ID Qty Date
319 65 2017-04-01 07:56:13.383
30 2100 2017-04-01 08:26:36.090
56 10000 2017-04-01 19:02:23.123
319 60 2017-04-02 07:49:02.780
319 60 2017-04-05 08:01:34.187
166 38 2017-04-06 08:11:56.183
319 60 2017-04-07 10:32:11.210
319 60 2017-04-08 08:14:38.540

Now I want monthly report where I want Sum of Qty group by Itm Id and date, It displays sum of qty for all dates where some transaction are done but I also want dates where no transaction is happen.

How to achieve this?

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	4

Hi Hardik,

You need to create a temp table to keep all the dates of current month.then make join with your table.Please use below code for your query:

DECLARE @startDate DATE=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' + + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATE=GETDATE() -- mm/dd/yyyy
SELECT @endDate= DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

SELECT [Date] = DATEADD(Day,Number,@startDate)
into #dates
FROM master..spt_values
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate

select dt.date,count(item_id) Item_ID,sum(cast(qty as int)) QTY
from Product P
right join #dates dt
on dt.[Date]=cast(P.Date as date)
group by dt.date

drop table #dates

Cheers :)

Thanks!
Anjali Bansal

~Give your best and lead the world

SQL Query to get all days of month with specific data

About dotnetspider.com

Quick Links

General

Services