Data Reterive Based on date selection senarios

Transact-SQL
1

Hi Experts !
Required your help .

I have SSRS one table to get data in different scenario date range scenario

If I select first date of the month then report runs from first date of the month to 10th date of the month and then 11th date to 20th date of the month in different columns.

ELSE

if start date is 11th of the month then retrieve the data from 11th to 20th of the month then 21st to last date of the month in two different columns

In attachment showing two tables for understandings , but currently the report format has one table to deal with... Hope I understand well.

Capture
Capture.PNG1184×54 1.48 KB

2

Hi,

Here you go:

Declare @startDate DateTime
Declare @endDate DateTime
Declare @startDate2 DateTime
Declare @endDate2 DateTime
set @startDate = '2019-01-11'

select @endDate=DateAdd(day,9,@startDate)
select @startDate2=DateAdd(day,10,@startDate)
select @endDate2=case when Day(DateAdd(day,9,@startDate2))>=20 then dbo.udf_GetLastDayOfMonth(DateAdd(day,9,@startDate2)) else DateAdd(day,9,@startDate2) end



select DateRange_1,GrandTotal_1=sum(Val),DateRange_2,GrandTotal_2=sum(Val2)
from (
	select DateRange_1=CONVERT(nvarchar(30), @startDate, 112)+'-'+CONVERT(nvarchar(30), @endDate, 112),
	Val=(case when datetrans between @startDate and @endDate then sum(Val) else 0 end),
	DateRange_2=CONVERT(nvarchar(30), @startDate2, 112)+'-'+CONVERT(nvarchar(30), @endDate2, 112),
	Val2=(case when datetrans between @startDate2 and @endDate2 then sum(Val) else 0 end)
	from (
		select id=1,datetrans='2019-01-02',Val=200.00
		union all
		select id=2,datetrans='2019-01-05',Val=500.00
		union all
		select id=3,datetrans='2019-01-10',Val=550.00
		union all
		select id=4,datetrans='2019-01-15',Val=400.00
		union all
		select id=5,datetrans='2019-01-18',Val=500.00
		union all
		select id=6,datetrans='2019-01-22',Val=550.00
		union all
		select id=7,datetrans='2019-01-24',Val=200.00
		union all
		select id=8,datetrans='2019-01-27',Val=30.00
		union all
		select id=9,datetrans='2019-01-30',Val=150.00
		union all
		select id=10,datetrans='2019-01-31',Val=750.00
	) A
	group by datetrans
)X
group by DateRange_1,DateRange_2



Create FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Regards,
Micheale

3

Hi Expert Micheale !
Many thanks for your hard work really. When passing date 2019-01-11 worked perfectly by providing DateRange_1 = 20190111-20190120 and DateRange_2 = 20190121-20190131

Only one change required when pass the date 2019-01-01

  • Datarange_2 date need to be till '20190111-20190120' and grand total required to be from 20190111-20190120 dates.

Screenshot

4

Hi,

Sorry. Just need to twist little as below:-

select @endDate2=case when Day(DateAdd(day,9,@startDate2))>=20 then dbo.udf_GetLastDayOfMonth(DateAdd(day,9,@startDate2)) else DateAdd(day,9,@startDate2) end

to

select @endDate2=case when Day(DateAdd(day,9,@startDate2))>=21 then dbo.udf_GetLastDayOfMonth(DateAdd(day,9,@startDate2)) else DateAdd(day,9,@startDate2) end

Declare @startDate DateTime
Declare @endDate DateTime
Declare @startDate2 DateTime
Declare @endDate2 DateTime
--set @startDate = '2019-01-01'
set @startDate = '2019-01-11'

select @endDate=DateAdd(day,9,@startDate)
select @startDate2=DateAdd(day,10,@startDate)
select @endDate2=case when Day(DateAdd(day,9,@startDate2))>=21 then dbo.udf_GetLastDayOfMonth(DateAdd(day,9,@startDate2)) else DateAdd(day,9,@startDate2) end



select DateRange_1,GrandTotal_1=sum(Val),DateRange_2,GrandTotal_2=sum(Val2)
from (
	select DateRange_1=CONVERT(nvarchar(30), @startDate, 112)+'-'+CONVERT(nvarchar(30), @endDate, 112),
	Val=(case when datetrans between @startDate and @endDate then sum(Val) else 0 end),
	DateRange_2=CONVERT(nvarchar(30), @startDate2, 112)+'-'+CONVERT(nvarchar(30), @endDate2, 112),
	Val2=(case when datetrans between @startDate2 and @endDate2 then sum(Val) else 0 end)
	from (
		select id=1,datetrans='2019-01-02',Val=200.00
		union all
		select id=2,datetrans='2019-01-05',Val=500.00
		union all
		select id=3,datetrans='2019-01-10',Val=550.00
		union all
		select id=4,datetrans='2019-01-15',Val=400.00
		union all
		select id=5,datetrans='2019-01-18',Val=500.00
		union all
		select id=6,datetrans='2019-01-22',Val=550.00
		union all
		select id=7,datetrans='2019-01-24',Val=200.00
		union all
		select id=8,datetrans='2019-01-27',Val=30.00
		union all
		select id=9,datetrans='2019-01-30',Val=150.00
		union all
		select id=10,datetrans='2019-01-31',Val=750.00
	) A
	group by datetrans
)X
group by DateRange_1,DateRange_2

Thanks.

Regards,
Micheale

5

Perfect / Great thank you for your kind help.:smiley: