January 30, 2012 at 8:12 am
Hi I have a table like this which I have attached
I will have to figure out a measure
% of all meds with a doc on the day of the visit :
For all visits, the total number of medications that have had an action (action include add new, "taking", not taking") on the day of visit/total number of meds
I have total number of meds but I am not sure how to figure out the numerator.
These are the update statements I have made for all the flags that I have created in the above table.
Update #MRec_Gather_Med_Reconcilation_1a
SET cnt_allmed = 1
Where Medication is not null
/****added new to calculate visits with 100% - 3/22******************/
Update #MRec_Gather_Med_Reconcilation_1a
Set ActionTaken =1
where (LastAction is not null) or (LastAction <> 'No Info Avail.') or (LastAction <> '')
Update #MRec_Gather_Med_Reconcilation_1a
SET Active_Meds = 1
where (StopDate is null or StopDate >=GETDATE())
and FactAudit = 'F'
Update #MRec_Gather_Med_Reconcilation_1a
SET cnt_recon = 1
where Actiontaken = 1
Update #MRec_Gather_Med_Reconcilation_1a
SET Count_None = 1
where Active_Meds =0 and Medication = 'None'
Update #MRec_Gather_Med_Reconcilation_1a
SET Printdatecount = 1
where VisitDate = printdate
Update #MRec_Gather_Med_Reconcilation_1a
Set VisitwithMed =1
where Medication is not null
PLEASE HELP ME. THIS IS URGENT
Editor: I have removed the attachment.
January 30, 2012 at 8:51 am
What is the question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 8:55 am
I will have to figure out a measure
% of all meds with a doc on the day of the visit :
Question is how to write the code to figure out For all visits, the total number of medications that have had an action (action include add new, "taking", not taking") on the day of visit
For all visits, the total number of medications that have had an action (action include add new, "taking", not taking") on the day of visit/total number of meds
January 30, 2012 at 9:00 am
Can you some ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? It is unclear to me exactly what you want and I couldnt begin to put together a query without tables and data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 9:52 am
I have acually attached the data in the spread sheet.
January 30, 2012 at 9:56 am
there are three tabs in the spreadsheet....all have data
pls can you clarify further.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2012 at 9:56 am
The problem with not posting scripts is I have no idea what datatypes you are working with and I have to spend time download and importing a spreadsheet.
And I still don't know what you want out of this.
Take a look at the first link in my signature for best practices on posting questions with supporting information to help ensure you get tested, fast and accurate responses to your questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 9:58 am
J Livingston SQL (1/30/2012)
there are three tabs in the spreadsheet....all have datapls can you clarify further.
ROFL!!! I looked at this spreadsheet and was wondering why there was only 1 table.
This is another reason to post tables in a readily consumable format.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 12:23 pm
hbanerje (1/30/2012)
I have acually attached the data in the spread sheet.
OK......and this is pure guess work.
taking the 3rd tab on your attached spreadsheet...(seems to relate to your initial posts with all those updates.) I have ripped the data and assumed the datatypes.
this gives us a script as follows:
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MedRec]') AND type in (N'U'))
DROP TABLE [dbo].[MedRec]
GO
CREATE TABLE [dbo].[MedRec](
[Provider] [nvarchar](50) NULL,
[PTID] [int] NULL,
[ProvID] [int] NULL,
[VisitDate] [datetime] NULL,
[Medication] [nvarchar](50) NULL,
[MedType] [nvarchar](50) NULL,
[StatusCode] [nvarchar](50) NULL,
[LastAction] [nvarchar](50) NULL,
[MedTimestamp] [nvarchar](50) NULL,
[MedChangedBy] [int] NULL,
[ntlogin] [nvarchar](50) NULL,
[FactAudit] [nvarchar](5) NULL,
[Nbr_Visits] [int] NULL,
[cnt_allmed] [int] NULL,
[cnt_recon] [int] NULL,
[HundredPercent] [int] NULL,
[Active_Meds] [int] NULL,
[Active_Meds_Visits] [int] NULL,
[Count_none] [int] NULL,
[Actiontaken] [int] NULL,
[Printdatecount] [int] NULL,
[StopDate] [nvarchar](50) NULL,
[PrintDate] [datetime] NULL,
[Visitwithmed] [int] NULL
)
GO
-- edit data removed...see posts below
Please paste the above code into SSMS...run it and cross check to see if this is correct.
as for your query... I really do not know what you are asking (sorry)...but here are some initial thoughts for further discussion
SELECT Medication
FROM MedRec
WHERE ( Actiontaken = 1 )
GROUP BY Medication
SELECT COUNT(DISTINCT Medication) AS Cnt
FROM MedRec
WHERE ( Actiontaken = 1 )
SELECT PTID,
VisitDate,
Medication,
LastAction
FROM MedRec
WHERE ( Actiontaken = 1 )
GROUP BY LastAction,
Medication,
PTID,
VisitDate
HAVING ( NOT ( LastAction LIKE N'EDIT' ) )
I hope that you will now realise why we ask for set up scripts / sample data / example results.
I also think that you may have over simplified the final data set......maybe more than 6 rows are needed with different patients / dates / actions etc.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2012 at 12:34 pm
I'm not sure that you are supposed to be posting this kind of real data in a public forum. Moderators, you may want to take a look and make sure that this is okay.
Jared
CE - Microsoft
January 30, 2012 at 12:56 pm
SQLKnowItAll (1/30/2012)
I'm not sure that you are supposed to be posting this kind of real data in a public forum. Moderators, you may want to take a look and make sure that this is okay.
Hi Jared
how do we know its "real" ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2012 at 1:03 pm
J Livingston SQL (1/30/2012)
SQLKnowItAll (1/30/2012)
I'm not sure that you are supposed to be posting this kind of real data in a public forum. Moderators, you may want to take a look and make sure that this is okay.Hi Jared
how do we know its "real" ?
I assume because this is "urgent", no changes in DML that arer obvious to obstruct column names or simplify it, and no mention if it being sample or fake from the OP. Clearly with 3 tabs in the spreadsheet, this is not data specifically for this forum.
Jared
CE - Microsoft
January 30, 2012 at 1:06 pm
😎
Jared
CE - Microsoft
January 30, 2012 at 1:18 pm
DO NOT POST HIPAA controlled information on the site. I have deleted the spreadsheet.
If you are seeking help with something from your work, you must use made up data, not any real names, ID numbers, or other potentially identifying information.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 30, 2012 at 1:20 pm
Steve / Jared
have removed the inserts from my post.
sorry guys...should have checked / googled before....lesson learnt !!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply