November 16, 2009 at 8:25 am
Hi,
I am trying to get this SQL statement to display ALL times that are in the OptionsMapping table and not just the times that have data in the DailyMedicalPlanItem table, just wondering if anyone could take a look any ideas or suggestions would be appreciated
Thanks in advance
-- My current sql query
SELECT om.Name as Time,
(
SELECT IsNull(iitem.RehabTypeID, iitem.StaffID)
FROM DailyMedicalPlanItem iitem
Where iitem.Date = pitem.Date
AND iitem.PlayerID = 323
) as [Player1],
(
SELECT IsNull(iitem.RehabTypeID, iitem.StaffID)
FROM DailyMedicalPlanItem iitem
Where iitem.Date = pitem.Date
AND iitem.PlayerID = 19
) as [Player2]
FROM OptionsMapping om
LEFT JOIN DailyMedicalPlanItem pitem on Convert(varchar, pitem.Date, 8) like om.Name + '%'
WHERE Convert(varchar, Date, 101) = '11/13/2009'
GROUP BY om.name, date
ORDER BY date
-- create tables
CREATE TABLE [dbo].[DailyMedicalPlanItem]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[PlayerID] [int] NULL,
[RehabTypeID] [int] NULL,
[StaffID] [int] NULL
)
CREATE TABLE [dbo].[OptionsMapping]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL
)
-- populate tables
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT OptionsMapping ON
--===== Insert the test data into the test table
INSERT INTO OptionsMapping (ID, Name)
SELECT '983','09:00' UNION ALL
SELECT '984','09:30' UNION ALL
SELECT '985','10:00' UNION ALL
SELECT '986','10:30' UNION ALL
SELECT '987','11:00' UNION ALL
SELECT '988','11:30' UNION ALL
SELECT '989','12:00' UNION ALL
SELECT '990','12:30' UNION ALL
SELECT '996','13:00' UNION ALL
SELECT '997','13:30' UNION ALL
SELECT '998','14:00'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT OptionsMapping OFF
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT DailyMedicalPlanItem ON
--===== Insert the test data into the test table
INSERT INTO DailyMedicalPlanItem (ID, Date, PlayerID, RehabTypeID, StaffID)
SELECT '5','Nov 11 2009 12:30','19',null,'25263' UNION ALL
SELECT '6','Nov 11 2009 12:00','323', null,'25263' UNION ALL
SELECT '7','Nov 13 2009 12:00','323', null,'25706' UNION ALL
SELECT '8','Nov 13 2009 12:30','323', null ,'11625'UNION ALL
SELECT '9','Nov 13 2009 11:30','323','994', null UNION ALL
SELECT '10','Nov 13 2009 13:00','323','995','24941' UNION ALL
SELECT '11','Nov 13 2009 10:30','323',null,'24941' UNION ALL
SELECT '12','Nov 13 2009 10:00','19','993', null UNION ALL
SELECT '13','Nov 13 2009 10:30','19','993', null UNION ALL
SELECT '14','Nov 13 2009 11:00','19','993', null UNION ALL
SELECT '16','Nov 13 2009 11:30','19','994','25263'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT DailyMedicalPlanItem OFF
select * From OptionsMapping
select * From DailyMedicalPlanItem
November 16, 2009 at 8:57 am
Seems to be crazy but I ran the same query without the
WHERE Convert(varchar, Date, 101) = '11/13/2009'
and got the result what you wanted.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 9:06 am
Ah my apologies the test data was not what i was supposed to post as they did not match the OptionsMapping table times, the updated test data for the DailyMedicalPlanItem should be what is needed, thanks for your reply....
November 19, 2009 at 11:35 pm
Here is my shot at what you need.
select
om.Name,
ISNULL(pitem1.RehabTypeID, pitem1.StaffID) as Player1,
ISNULL(pitem2.RehabTypeID, pitem2.StaffID) as Player2
from
dbo.OptionsMapping om
left outer join dbo.DailyMedicalPlanItem pitem1
on (om.Name = CONVERT(varchar(5), pitem1.[Date], 108)
and pitem1.PlayerID = 323
and pitem1.[Date] >= '2009-11-13'
and pitem1.[Date] < '2009-11-14')
left outer join dbo.DailyMedicalPlanItem pitem2
on (om.Name = CONVERT(varchar(5), pitem2.[Date], 108)
and pitem2.PlayerID = 19
and pitem2.[Date] >= '2009-11-13'
and pitem2.[Date] < '2009-11-14')
November 20, 2009 at 3:54 am
Works like a charm nice one cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply