April 26, 2011 at 10:24 am
I have a query that I need to return results from 23 to < 6 (hours).
If I use comment the datepart (hh,ActivityTime) <6 .... Then correct activitydate is returned
SELECT DISTINCT txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As H
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
--AND datepart (hh,ActivityTime) <6 AND datepart (hh,ActivityTime) >= 23
Order By Terminal
But If I uncomment the datepart then activitydate from 2010 are returned.
SELECT DISTINCT txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As H
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
AND datepart (hh,ActivityTime) <6 AND datepart (hh,ActivityTime) >= 23
Order By Terminal
Any help appreciated.
April 26, 2011 at 11:30 am
Post the table structure please - along with PK and indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 11:34 am
PSB (4/26/2011)
SELECT DISTINCT txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As H
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
AND datepart (hh,ActivityTime) <6 AND datepart (hh,ActivityTime) >= 23
Order By Terminal
Any help appreciated.
How did the
AND datepart (hh,ActivityTime) <6 AND datepart (hh,ActivityTime) >= 23
Query work for ,did you realy get the data for AND condition.
Thanks
Parthi
April 26, 2011 at 11:35 am
CREATE TABLE [dbo].[ABCD](
[InternalID] [int] IDENTITY(1,1) NOT NULL,
[RecordInternalID] [int] NULL,
[ProcessorID] [int] NULL,
[Terminal] [varchar](50) NULL,
[TxnTypeID] [int] NULL,
[ResponseCodeID] [int] NULL,
[RejectCodeID] [int] NULL,
[BankID] [int] NULL,
[NetworkID] [int] NULL,
[TerminalSequenceNumber] [varchar](50) NULL,
[SettlementDate] [datetime] NULL,
[SettlementTime] [datetime] NULL,
[ActivityDate] [datetime] NULL,
[ActivityTime] [datetime] NULL,
[PAN] [varchar](19) NULL,
[Amount] [money] NULL,
[Fee] [money] NULL,
[Surcharge] [money] NULL,
[Interchange] [money] NULL,
[Txn] [bit] NULL,
[OurBank] [bit] NULL,
[EBTTransaction] [bit] NULL,
[LogID] [int] NULL,
[InterchangeCalc] [money] NULL,
[International] [bit] NULL,
[ATMInternalID] [int] NULL,
CONSTRAINT [PK_ABCD] PRIMARY KEY CLUSTERED
(
[InternalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ABCD] ADD CONSTRAINT [DF__T_TxnDeta__Inter] DEFAULT (0) FOR [International]
GO
April 26, 2011 at 11:36 am
No. I didnot get any data. I need to retrieve data between 11pm and 6am.
April 26, 2011 at 11:41 am
See if this works any better.
with preprocess as (
Select internalid,txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As ActivityHour
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
)
select distinct Terminal,ActivityDate,ActivityHour
From preprocess
Where ActivityHour >= 23 and ActivityHour < 6
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 11:46 am
This doesnot return any data!
April 26, 2011 at 11:46 am
PSB (4/26/2011)
I have a query that I need to return results from 23 to < 6 (hours)....
SELECT DISTINCT txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As H
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
--AND datepart (hh,ActivityTime) <6 AND datepart (hh,ActivityTime) >= 23
Order By Terminal
...
You've changed a few parts of the query - have you included all of the WHERE clause?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 11:49 am
SQLRNNR (4/26/2011)
See if this works any better.
with preprocess as (
Select internalid,txn.Terminal,txn.ActivityDate,datepart (hh,ActivityTime) As ActivityHour
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
)
select distinct Terminal,ActivityDate,ActivityHour
From preprocess
Where ActivityHour >= 23 and ActivityHour < 6
How and datepart (hh,ActivityDate) < 6 will work over here.
declare @Temp table (ActivityDate datetime)
insert into @Temp
values
('2011-03-17 05:39:42.937'),
('2011-03-17 23:39:42.937'),
('2011-03-17 13:44:56.807'),
('2011-03-17 18:02:42.893'),
('2011-03-18 11:30:50.077'),
('2011-03-18 12:10:36.163'),
('2011-03-28 02:30:50.077'),
('2010-03-18 12:10:36.163'),
('2010-03-28 01:30:50.077')
select * from @Temp WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011' AND datepart (hh,ActivityDate) >=23
and datepart (hh,ActivityDate) < 6 /* Will not return data*/
Try UNION will help you out
select * from @Temp WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011' AND datepart (hh,ActivityDate) <6
union
select * from @Temp WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011' AND datepart (hh,ActivityDate) >=23
Thanks
Parthi
April 26, 2011 at 11:56 am
PSB (4/26/2011)
This doesnot return any data!
Please provide sample data. You'll notice that the query is the same thing you have - just broken into two phases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 12:01 pm
Try this:
SELECT DISTINCT
txn.Terminal,
txn.ActivityDate,
DATEPART(hh, ActivityTime) AS H
FROM dbo.ABCD txn
WHERE ActivityDate >= '3/1/2011' AND DATEPART(hh, ActivityTime) >= 23
OR ActivityDate < '4/1/2011' AND DATEPART(hh, ActivityTime) < 6
ORDER BY Terminal ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2011 at 12:06 pm
Thanks Union worked !
April 26, 2011 at 12:16 pm
WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)
--===== Do the split
, abcd as (
SELECT ROW_NUMBER() OVER (ORDER BY N) AS InternalID
,CAST(RAND(CHECKSUM(NEWID()))*3653.0+38524.0 AS DATETIME) as ActivityDate
,ABS(CHECKSUM(NEWID()))%5+1 as terminal
FROM ctetally
),preprocess as (
Select InternalID,txn.Terminal,txn.ActivityDate,datepart (hh,txn.ActivityDate) As ActivityHour
FROM ABCD txn
WHERE ActivityDate >= '3/1/2011' AND ActivityDate < '4/1/2011'
)
select distinct Terminal,ActivityDate,ActivityHour
From preprocess
Where ActivityHour >= 23 or ActivityHour < 6
I went with setting up a little test code myself and found the flaw. Change the
select ... Where ActivityHour >= 23 and ActivityHour < 6
to
select ... Where ActivityHour >= 23 or ActivityHour < 6
edit: tried to fix < and > in code
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 12:21 pm
Thanks everyone !
April 26, 2011 at 12:22 pm
you're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply