September 14, 2010 at 10:04 am
vivek.verma (9/14/2010)
@ ColdCoffeeThe other code also worked well, and off course I will prefer less number of lines as it is somewhat good for speed. Thanks again
Thanks for the feedback, Vivek 🙂
September 14, 2010 at 10:57 am
ColdCoffee (9/14/2010)
WayneS (9/14/2010)
@cc - thanks for helping out!Welcome , Sheff.. 🙂
As a side note, did u prepare a solution for this? I would love to look at your piece..
I hadn't, but how's this?
(Note: I'm using the BP2/WT2 CTEs just to prevent doing that DateAdd/DateDiff twice)
declare @PatientID varchar(50);
set @PatientID = '1002044';
--set @PatientID = '1865004';
WITH BP1 AS
( -- strip the time from the date
SELECT b.BPM_TimeStmp,
b.BPM,
b.BPM_ID,
BP_Date = DateAdd(day, DateDiff(day, 0, b.BPM_TimeStmp), 0),
p.PateintID
FROM dbo.BPM_Reading b
JOIN dbo.patient p
ON p.BPM_ID = b.BPM_ID
WHERE p.PateintID = @PatientID
), BP2 AS
( -- get row numbers, restarting at bpm_id/bp_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY BPM_ID, BP_Date ORDER BY BPM_TimeStmp DESC)
FROM BP1
),WT1 AS
( -- strip the time from the date
SELECT w.WTimeStmp,
w.[Weight],
w.WM_ID,
WT_Date = DateAdd(day, DateDiff(day, 0, w.WTimeStmp), 0)
FROM dbo.WM_Reading w
JOIN dbo.Patient p
ON w.WM_ID = p.WM_ID
WHERE p.PateintID = @PatientID
), WT2 AS
( -- get row numbers, restarting at wm_id/wt_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY WM_ID, WT_Date ORDER BY WTimeStmp DESC)
FROM WT1
)
-- now get everything for this patient.
SELECT BP2.BPM_TimeStmp, BP2.BPM, WT2.WTimeStmp, WT2.[Weight]
FROM BP2
FULL OUTER JOIN WT2
ON BP2.RN = WT2.RN
AND BP2.BP_Date = WT2.WT_Date
ORDER BY BPM_TimeStmp DESC;
Your solution is very good!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 5:20 am
Thanks guys 🙂
@Wayne
Your solution is also working, is it some way possible to order the final result by date desc as the present final result set is as order by BPM_TimeStmp desc and those rows with only weight readings didn't come in order by date desc in whole result set. I don't have a problem with datetime coming together in both BPM_TimeStmp and WTimeStmp column as I can easily strip it off in ASP.Net web page while databinding. May be is it possible to add another row to your result set as Date which will put BPM_TimeStmp's date if both weight and BP readings or only BP readings are there and WTimeStmp's date if only Weight reading is there in a row. Then final result set should be set order by Date desc. Any solution for this?
Your solution is also very good, actually as I stated in my first post that I am using these example tables to get the solution of the problem, my real tables have more columns and I am trying to use your method to achieve solution on my real sql tables.. Thanks
September 15, 2010 at 6:42 am
vivek.verma (9/15/2010)[/b]
@Wayne
Your solution is also working, is it some way possible to order the final result by date desc as the present final result set is as order by BPM_TimeStmp desc and those rows with only weight readings didn't come in order by date desc in whole result set. I don't have a problem with datetime coming together in both BPM_TimeStmp and WTimeStmp column as I can easily strip it off in ASP.Net web page while databinding. May be is it possible to add another row to your result set as Date which will put BPM_TimeStmp's date if both weight and BP readings or only BP readings are there and WTimeStmp's date if only Weight reading is there in a row. Then final result set should be set order by Date desc. Any solution for this?
Well achieved this by collecting the final result in a temp table and using a new column Date, Case(T-SQL) conditions when any BPM_TimeStmp Null then Column Date filled with /WTimeStmp's date and vice-versa, lastly order by that Date desc
September 15, 2010 at 8:14 am
vivek.verma (9/15/2010)
Thanks guys 🙂@Wayne
Your solution is also working, is it some way possible to order the final result by date desc as the present final result set is as order by BPM_TimeStmp desc and those rows with only weight readings didn't come in order by date desc in whole result set. I don't have a problem with datetime coming together in both BPM_TimeStmp and WTimeStmp column as I can easily strip it off in ASP.Net web page while databinding. May be is it possible to add another row to your result set as Date which will put BPM_TimeStmp's date if both weight and BP readings or only BP readings are there and WTimeStmp's date if only Weight reading is there in a row. Then final result set should be set order by Date desc. Any solution for this?
Your solution is also very good, actually as I stated in my first post that I am using these example tables to get the solution of the problem, my real tables have more columns and I am trying to use your method to achieve solution on my real sql tables.. Thanks
Just move that final select into yet another CTE, add a date field, and in the final select order by that field.
declare @PatientID varchar(50);
set @PatientID = '1002044';
--set @PatientID = '1865004';
WITH BP1 AS
( -- strip the time from the date
SELECT b.BPM_TimeStmp,
b.BPM,
b.BPM_ID,
BP_Date = DateAdd(day, DateDiff(day, 0, b.BPM_TimeStmp), 0),
p.PateintID
FROM dbo.BPM_Reading b
JOIN dbo.patient p
ON p.BPM_ID = b.BPM_ID
WHERE p.PateintID = @PatientID
), BP2 AS
( -- get row numbers, restarting at bpm_id/bp_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY BPM_ID, BP_Date ORDER BY BPM_TimeStmp DESC)
FROM BP1
),WT1 AS
( -- strip the time from the date
SELECT w.WTimeStmp,
w.[Weight],
w.WM_ID,
WT_Date = DateAdd(day, DateDiff(day, 0, w.WTimeStmp), 0)
FROM dbo.WM_Reading w
JOIN dbo.Patient p
ON w.WM_ID = p.WM_ID
WHERE p.PateintID = @PatientID
), WT2 AS
( -- get row numbers, restarting at wm_id/wt_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY WM_ID, WT_Date ORDER BY WTimeStmp DESC)
FROM WT1
),NearFinal AS
(
-- now get everything for this patient.
SELECT BP2.BPM_TimeStmp, BP2.BPM, WT2.WTimeStmp, WT2.[Weight],
DateSort = Coalesce(BP2.BPM_TimeStmp, WT2.WTimeStmp)
FROM BP2
FULL OUTER JOIN WT2
ON BP2.RN = WT2.RN
AND BP2.BP_Date = WT2.WT_Date
)
SELECT DateSort, BPM_TimeStmp, BPM, WTimeStmp, [Weight]
FROM NearFinal
ORDER BY DateSort DESC;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 9:27 am
Just move that final select into yet another CTE, add a date field, and in the final select order by that field.
Yeah that is what I already did, Thanks
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply