Need to count Round date, URGENT!!

Question

Need to count Round date, URGENT!!

hbanerje

SSC Eights!

Points: 818
More actions
March 25, 2011 at 11:34 am

#236852

I have this data set, where I want to count 3 mentioned in the dotted line.
How to write a sql qery to count the round date where Has Medi field is 'Has Active Meds' but for example, ID = 3399000, count only the first one ignore the second one because it is mixture of 'Has Active Meds' and 'Has Meds'
ID Round DateHas Medi
1864412310/18/2010Has Active Meds2
Has Meds1
10/18/2010 Total3
3197234 12/30/2010Has Active Meds6 ------1
12/30/2010 Total 6
3326345 10/13/2010Has Active Meds1
Has Meds7
10/13/2010 Total8
3399000 10/13/2010Has Active Meds6 -------1
10/13/2010 Total 6
11/3/2010Has Active Meds1
Has Meds9
11/3/2010 Total 10
Total16
4176411110/18/2010Has Active Meds2 -----1
10/18/2010 Total 2
4388100 11/3/2010Has Active Meds10
Has Meds5
11/3/2010 Total15
Thanks so much!
hai

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

hbanerje SSC Eights! Points: 818 More actions · Answer 2

This piece of code I have written so far, I want to count only the Round date

where [Has Meds] is equal to only [Has active Meds] (Stand alone).

So from the above data set, count will be 3

DROP TABLE #Active_Meds_Round

---select * from #Active_Meds_Visits

select ID,provider,[Round Date], 1 as Active_Round_cnt

into #Active_Meds_Round

from dbo.MRec_Gather_Med_Reconcilation_1

where [Has Meds?] not in ('Has Meds')

and Provider like 'S.Smith'

group by ID,provider,[Round Date]

Thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 3

That's just part of the stuff Wayne asked for...

Please provide table def and sample data for dbo.MRec_Gather_Med_Reconcilation_1 as well as your expected output.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]