Member Avatar for jovillanuev

Hi Guys,

I have a requirements to backtrack 2 days if the date fall as Monday.
Why? We dont have any transaction from Sunday to Monday at 6:00AM that's why need to backtrack 2 days.
i want to share with you my query. if you have any additional idea to simplify this is very much appreaciated. Thanks.

--Sample DDL
Create table #SampleData(Prodid nvarchar(35), model nvarchar(35), Trandate datetime)
Go
Insert into #SampleData
Select 'TRC003328042','DRDRZR2BLK','2013-08-31 04:30:33.000' union all---Saturday
Select 'TRC003327670','DRDRZR2BLK','2013-08-31 02:40:23.000'union all---Saturday
Select 'TRC003327822','DROIDRZR','2013-08-31 05:17:28.000'union all---Saturday
Select 'TRC003328342','DROIDRZR','2013-08-31 08:10:27.000'union all---Saturday
Select 'TRC003328387','DROIDRZR','2013-08-31 09:22:01.000'union all---Saturday
Select 'TRC003322836','DRDRZRHDBLK','2013-08-30 23:40:19.000'union all---Saturday
Select 'TRC003326586','DROIDBIO2','2013-08-31 05:50:47.000'union all---Saturday
Select 'TRC003325460','DROIDBIO2','2013-08-30 22:36:50.000'union all---Saturday
Select 'TRC003328994','DRDRZR2WHT','2013-09-02 04:33:44.000'union all-- Monday
Select 'TRC003328998','DRDRZR2WHT','2013-09-02 06:37:13.000'-- Monday
Select 'TRC003328994','DRDRZR2WHT','2013-09-03 06:40:12.000'-- Tuesday

--Created SSRS Parameter
Declare @FromDate datetime, @ToDate datetime, @TodayName nvarchar(10)
@Fromtime nvarchar(6), @Totime nvarchar(6) ,@timezoneOffset int
set @FromDate='9/2/2013' --Monday
set @ToDate='9/3/2013'
Set @Fromtime='6:00 AM'
Set @Totime='6:00 AM'
set @timezoneOffset=8
Set @TodayName='Monday' --based on @FromDate(9.2.13)

--create dataset for todayname
Select DateName(dw,getdate())--Result set is monday

-Dataset Properties->Parameter
Parameter name--------Parameter value
@PF
Edited by pritaeas because: Added markdown.
  • 2 Contributors
  • 1 Reply
  • 215 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by ChrisHunter
Member Avatar for ChrisHunter

Theres a build in DATEADD() function in SQL Server (2005 at least), which lets you add or subtract seconds, minutes, hours, days, months or years to a datetime value.

Have a look at this link and it will tell you everything you need to know.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.