multiple where clause dates

Question

I pass two date range to my procedure then I select last date of each and every month as below

create procedure stcheck
@From_Date date,
@To_Date date
as
begin


declare @tmpTable table (dates date)
declare @startDate date = @From_Date
declare @endDate date = @To_Date

while @startDate <= @endDate

begin
    insert into @tmpTable (dates) values (@startDate)
    set @startDate = DATEADD(DAY, 1, @startDate)
end

select max(dates) as [Last day] from @tmpTable as o
group by datepart(YEAR, dates), datepart(MONTH, dates)

If am getting result as follows

Last day
2017-01-31

2017-02-28

2017-03-31

2017-04-30

I need to pass some data to temporary table by using these date output

Ex :-

Insert #xyz

Select * from abcd where postdate <=’2017-01-31’

And my requirement is that these all dates should be automatically used and relevant data should pass to #table.

Hi expert . thank for your early reply.

In brief my requirement is as follows. Please help because I am new with sql.

Think,I have a table as ‘stock’ with in and out stock transaction and stock

report can be taken as follows to any as at date

SELECT item ,SUM(InQty-OutQty)  FROM stock
WHERE TransDate <= @AsatDate 
GROUP BY item 
Having SUM(InQty-OutQty) > 0

If I need to get the each and every month end stock when I am passing the date range

from @From date= ‘2017/05/01’ to @AsatDate=‘2017/09/30’ .

The stock report result should be appear something like this.

STOCK REPORT EXAMPLE

Please help what can be done?

NB:- it should be able to pass any date range when it is required.

Just a few items 1) I suspect SQL Server ... what version? 2) Looping functions should be avoided. 3) Sample source data and desired results would be more helpful. — John Cappelletti, Oct 15 '17 at 19:21
Can you clarify the last part? How does that insert query relate to stored proc? — Parfait, Oct 15 '17 at 21:02

score 0 · Answer 1 · answered Oct 16 '17 at 01:43

Your question is a little unclear, but I'm assuming you want to run the stored procedure and insert data into the temp table #xyz (assumes temp table #xyz already exists):

insert into #xyz
exec stcheck @From_Date, @To_Date

Now off on a little tangent:

I'd also look at table-valued functions if you need parameterized, deterministic tabular output like this. I agree with @John that you should avoid looping if possible. One possible way to achieve this is instead of iterating day by day you use a pre-populated calendar table (stick say 50-100 years worth of future calendar dates in a table and use this in calculations - there are tons of examples of how to do this online). Another method is to use a recursive CTE or a numbers table, both outlined in answers here.

Once your calendar dates are easily queryable, you can stick all this in a view or inline table-valued function and it will be super fast and much clearer. If you regularly use "last day of month" as a filter criteria, you could even pre-calculate these as a column in your calendar table. Here's what it could end up like:

insert into #xyz
select dbo.DatesBetween(@From_Date, @To_Date)

You could even use dbo.DatesBetween(@From_Date, @To_Date) directly in your query in place of the temp table, if it's only used once.

please see my further clarification – Damith Suranga Oct 17 '17 at 17:29 — Damith Suranga, Oct 17 '17 at 17:29

multiple where clause dates

1 Answers1