May 22, 2008 at 3:39 pm
The top table works, the bottom table it doesn't return any rows. The 3rd is a simplified version of the 2nd, and it doesn't work either.
Both date fields are setup identical:
datetime format
default value is (getdate())
The data for these fields are input via a C# UI. Could some of the dates in one table be malformed somehow from the UI?
Thanks.
-Chad
DECLARE @tmpDate datetime
SET @tmpDate = '5/19/08'
SELECT cr.[ChildID], cr.[FName], cr.[MName], cr.[LName], gl.Gender, cr.[DOB], cr.[Age], cr.[1stArrivedDate], cr.[TS]
FROM ChildRecords cr
inner join genderlist gl
on cr.genderID = gl.genderID
WHERE ChildID NOT IN (SELECT CHILDID FROM ChildAdmitRecords) AND [1stArrivedDate] LIKE @tmpDate
--SET @tmpDate = '%'
SELECT [ChildXAdmitID], [ChildID], [AdmittedByStaffID], PrimaryReasonCodeID AS PCode, carl.AdmitReason AS [PrimaryReasonCode], SecondaryReasonCodeID AS SCode, carl2.AdmitReason AS [SecondaryReasonCode], [LastSchool], [AdmitDate], [RejectDate], [ReleaseDate], [TS]
FROM [CRH_IO].[dbo].[ChildAdmitRecords] car
inner join ChildAdmit_AdmitReasonList carl on car.PrimaryReasonCodeID = carl.AdmitReasonID
inner join ChildAdmit_AdmitReasonList carl2 on car.SecondaryReasonCodeID = carl2.AdmitReasonID
--Where car.[AdmitDate] like '03/08/08'
Where car.[AdmitDate] LIKE @tmpDate
SELECT [ChildXAdmitID], [ChildID], [AdmittedByStaffID], PrimaryReasonCodeID AS PCode, SecondaryReasonCodeID AS SCode, [LastSchool], [AdmitDate], [RejectDate], [ReleaseDate], [TS]
FROM [CRH_IO].[dbo].[ChildAdmitRecords] car
Where car.[AdmitDate] LIKE @tmpDate
May 22, 2008 at 3:58 pm
Could you post the DDL for the tables, some sample data (using inseret statements) for each table, and the expected output from each query based on the provided sample data.
😎
May 22, 2008 at 4:21 pm
Here are the create scripts for the 2 tables.
The only thing out of ordinary I'm trying to do is do 2 inner joins to the SAME Lookup table because we use the same reason list for primary reason and secondary reason. But those inner joins work when the select is done with no where clause or I use
AdmitDate = '03/08/08'
Now, the time value for all the entries in the top table are zero'd "2008-05-19 00:00:00.000", and those are then entries that the "LIKE" where clause works for...
But for the table it doesn't work for, some of the entries are mixed:
2008-03-08 00:00:00.000
2007-02-01 00:00:00.000
2007-03-19 00:00:00.000
2008-04-18 13:36:48.253
2008-05-16 15:10:45.513
Could that be it?
If so, how would I write the condition to get all the entries for a single day and (then another where condition for another query) for a single month?
Here's the 2 primary tables create scripts. The 3rd table is a lookup table of 2 columns, 1 identity and 1 text.
CREATE TABLE [dbo].[ChildAdmitRecords](
[ChildXAdmitID] [int] IDENTITY(1,1) NOT NULL,
[ChildID] [int] NOT NULL,
[AdmittedByStaffID] [int] NULL,
[PrimaryReasonCodeID] [int] NOT NULL,
[SecondaryReasonCodeID] [int] NULL,
[LastSchool] [nvarchar](100) NULL,
[AdmitDate] [datetime] NOT NULL CONSTRAINT [DF_ChildAdmitRecords_AdmitDate] DEFAULT (getdate()),
[RejectDate] [datetime] NULL,
[ReleaseDate] [datetime] NULL,
[TS] [timestamp] NULL,
CONSTRAINT [PK_ChildAdmit] PRIMARY KEY CLUSTERED
(
[ChildXAdmitID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildRecords](
[ChildID] [int] IDENTITY(-2000000,1) NOT NULL,
[FName] [varchar](50) NULL,
[MName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[GenderID] [int] NOT NULL,
[DOB] [datetime] NULL,
[Age] AS (datepart(year,getdate())-datepart(year,[DOB])),
[1stArrivedDate] [datetime] NOT NULL CONSTRAINT [DF_ChildRecords_1stArrivedDate] DEFAULT (getdate()),
[TS] [timestamp] NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
May 22, 2008 at 4:44 pm
Could still use some sample data and the expected output. Also, will need the lookup table as well if you want anyone to test their work.
😎
May 22, 2008 at 4:59 pm
Here is what I came up with, but no testing.
CREATE TABLE [dbo].[ChildAdmitRecords](
[ChildXAdmitID] [int] IDENTITY(1,1) NOT NULL,
[ChildID] [int] NOT NULL,
[AdmittedByStaffID] [int] NULL,
[PrimaryReasonCodeID] [int] NOT NULL,
[SecondaryReasonCodeID] [int] NULL,
[LastSchool] [nvarchar](100) NULL,
[AdmitDate] [datetime] NOT NULL CONSTRAINT [DF_ChildAdmitRecords_AdmitDate] DEFAULT (getdate()),
[RejectDate] [datetime] NULL,
[ReleaseDate] [datetime] NULL,
[TS] [timestamp] NULL,
CONSTRAINT [PK_ChildAdmit] PRIMARY KEY CLUSTERED
(
[ChildXAdmitID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildRecords](
[ChildID] [int] IDENTITY(-2000000,1) NOT NULL,
[FName] [varchar](50) NULL,
[MName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[GenderID] [int] NOT NULL,
[DOB] [datetime] NULL,
[Age] AS (datepart(year,getdate())-datepart(year,[DOB])),
[1stArrivedDate] [datetime] NOT NULL CONSTRAINT [DF_ChildRecords_1stArrivedDate] DEFAULT (getdate()),
[TS] [timestamp] NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @tmpDate datetime
SET @tmpDate = '5/19/08'
SELECT
cr.[ChildID],
cr.[FName],
cr.[MName],
cr.[LName],
gl.Gender,
cr.[DOB],
cr.[Age],
cr.[1stArrivedDate],
cr.[TS]
FROM
dbo.ChildRecords cr
inner join dbo.genderlist gl
on cr.genderID = gl.genderID
left outer join dbo.ChildAdmitRecords car
on (cr.ChildID = car.ChildID)
WHERE
-- ChildID NOT IN (SELECT CHILDID FROM ChildAdmitRecords)
car.ChildID is null
AND cr.[1stArrivedDate] >= @tmpDate
AND cr.[1stArrivedDate] < @tmpDate + 1
-- AND cr.[1stArrivedDate] >= dateadd(mm, datediff(mm,0,@tmpDate), 0) -- This would select all records
-- AND cr.[1stArrivedDate] < dateadd(mm, datediff(mm,0,@tmpDate) + 1, 0) -- for the month in which @tmpDate is in
--SET @tmpDate = '%'
SELECT
car.[ChildXAdmitID],
car.[ChildID],
car.[AdmittedByStaffID],
car.PrimaryReasonCodeID AS PCode,
carl.AdmitReason AS [PrimaryReasonCode],
car.SecondaryReasonCodeID AS SCode,
carl2.AdmitReason AS [SecondaryReasonCode],
car.[LastSchool],
car.[AdmitDate],
car.[RejectDate],
car.[ReleaseDate],
car.[TS]
FROM
[CRH_IO].[dbo].[ChildAdmitRecords] car
inner join dbo.ChildAdmit_AdmitReasonList carl
on car.PrimaryReasonCodeID = carl.AdmitReasonID
inner join dbo.ChildAdmit_AdmitReasonList carl2
on car.SecondaryReasonCodeID = carl2.AdmitReasonID
--Where
-- car.[AdmitDate] like '03/08/08'
Where
car.[AdmitDate] >= @tmpDate
AND car.[AdmitDate] < @tmpDate + 1
-- car.[AdmitDate] >= dateadd(mm, datediff(mm,0,@tmpDate), 0) -- This would select all records
-- AND car.[AdmitDate] < dateadd(mm, datediff(mm,0,@tmpDate) + 1, 0) -- for the month in which @tmpDate is in
SELECT
car.[ChildXAdmitID],
car.[ChildID],
car.[AdmittedByStaffID],
car.PrimaryReasonCodeID AS PCode,
car.SecondaryReasonCodeID AS SCode,
car.[LastSchool],
car.[AdmitDate],
car.[RejectDate],
car.[ReleaseDate],
car.[TS]
FROM
[CRH_IO].[dbo].[ChildAdmitRecords] car
Where
car.[AdmitDate] >= @tmpDate
AND car.[AdmitDate] < @tmpDate + 1
-- car.[AdmitDate] >= dateadd(mm, datediff(mm,0,@tmpDate), 0) -- This would select all records
-- AND car.[AdmitDate] < dateadd(mm, datediff(mm,0,@tmpDate) + 1, 0) -- for the month in which @tmpDate is in
May 22, 2008 at 5:09 pm
Chad --
Lynn has given you some options, but let's follow that with some explanation...
In the second line of your code above, you set your DATETIME variable to a day, '5/19/08'. If you do a SELECT of that variable, you'll fine that with the conversion to DATETIME from CHAR, you gain the time as well, resulting in '2008-05-19 00:00:00.000'. Now it should be obvious that [AdmitDate] LIKE '2008-05-19 00:00:00.000' is only going to result in exact matches. Make sense?
The best solution is something like Lynn's, where you perform your math on your DATETIME variable and do some combination of ><= or use BETWEEN, depending on your needs.
Another, though not recommended solution is something like:
DECLARE @tmpDate varchar(20)
SET @tmpDate = '2008-05-19%'
...
WHERE CAST([AdmitDate] as VARCHAR(20)) LIKE @tmpDate
This option won't be able to use indexes and is not recommended. However, hopefully all this gives you more understanding on why things didn't work as you expected.
GL!
May 22, 2008 at 5:21 pm
I'd like to be able to use something like:
SELECT [ChildXAdmitID], [ChildID], [AdmittedByStaffID], PrimaryReasonCodeID AS PCode, carl.AdmitReason AS [PrimaryReasonCode], SecondaryReasonCodeID AS SCode, carl2.AdmitReason AS [SecondaryReasonCode], [LastSchool], [AdmitDate], [RejectDate], [ReleaseDate], [TS]
FROM [CRH_IO].[dbo].[ChildAdmitRecords] car
INNER JOIN ChildAdmit_AdmitReasonList carl on car.PrimaryReasonCodeID = carl.AdmitReasonID
INNER JOIN ChildAdmit_AdmitReasonList carl2 on car.SecondaryReasonCodeID = carl2.AdmitReasonID
with a where condition like:
WHERE car.[AdmitDate] LIKE @tmpDate
in a sp for retrieving all records for a child
Here's what the query returns without a where clause (sample/test data)
1-2000000 11Abandonment - Refuse to provide adequate care3Court Order SomeSchool2008-04-01 00:00:00.000NULLNULL0x0000000000001780
2-1999999 22AWOL from another facility 4CRH AWOL RE-AdmitSomeOtherSchool2008-03-08 00:00:00.000NULL2008-04-19 00:00:00.0000x0000000000001781
3-1999998 41Abandonment - Refuse to provide adequate care2AWOL from another facilitySomeOtherSchool2007-02-01 00:00:00.000NULLNULL0x0000000000001782
4-1999997 22AWOL from another facility 5Medical Neglect SomeOtherSchool2007-03-19 00:00:00.000NULLNULL0x0000000000001783
6-1999989 15Medical Neglect 7Parent Arrested SomeSchool2008-04-18 13:36:48.253NULLNULL0x0000000000001784
7-1999993 NULL3Court Order 5Medical Neglect SomeSchool 2008-05-16 15:10:45.513NULLNULL0x0000000000001785
9-1999992 NULL7Parent Arrested 3Court Order SomeSchool2008-05-16 15:14:26.000NULLNULL0x0000000000001787
ReasonCodeID ReasonCode
1 Abandonment - Refuse to provide adequate care
2 AWOL from another facility
3 Court Order
4 CRH AWOL RE-Admit
5 Medical Neglect
6 No Parent,Guardian or relative available to care
7 Parent Arrested
When I use the Like method for the where, is works for 1 table, but when I use it on another table it doesn't return any values.
The only difference between the tables I can see is the table not working contains data with 000000's for the TIMESTAMP portion of the date, while the other has some 00000'd out entries, while others contain actual times inserted via the UI.
Since it was working for 1 table I don't understand what's happening with the 2nd.
When I try to use "WHERE ADATE = @tmpdate" then it only returns entries with 00000000s for the time value...(which I'm guessing is from a result of an internal conversion from datetime short format to an actual datetime value for the search condition...
Maybe a better question would be:
How would you use WHERE conditions to extract records with various timevalues but the same DATEvalue via a sp paramenter?
Same for a month.
Thanks for helping me out.
-Chad
==================== EDIT ==========================
Thanks guys, I was putting the above together and missed your posts. Reading now. Thanks!
May 22, 2008 at 5:38 pm
Thanks, Lynn, those WHERE clauses work.
I like the childID is null, saves a query...
Thanks John for the explanation, much appreciated!
-Chad
May 22, 2008 at 5:42 pm
VERY glad that I refreshed your post before responding. Your edit really cleared up what you were missing! 😀
Here is an example of how to solve it:
DECLARE @tmpDate DATETIME
SET @tmpDate = '5/19/08 08:23'
--This strips the TIME portion. If you are sure you won't have TIME in the parameter, you can skip
SET @tmpDATE = DATEADD(dd, DATEDIFF(dd,0,@tmpDate), 0)
SELECT ...
FROM ...
WHERE [SomeDate] >= @tmpDate --So, greater than or equal to date at midnight
AND [SomeDate] < @tmpDate + 1 --So, less than date of next day at midnight
GL!
May 22, 2008 at 5:51 pm
John, My only comment regarding your explanation post (which was well done, by the way), is to NOT use between on date range queries; use >= and <. I have an explanation for this somewhere on SSC, as Jeff Moden "made me" explain it to another OP. Too bad I can't remember where. (lol)
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply