Post reply

Monthly Data

  • May 11, 2010 at 3:02 pm

    #156944

    I have this query that I want it to give me data for the 12 months. Any ideas how I can do that in one query? I want to put the result in one temp table.

    Any ideas how I can do that?

    declare @Facility varchar(15)

    declare @TheDate smalldatetime

    declare @date int

    Set @Facility='01'

    Set @TheDate = '4/30/2010'

    set @date=cast(convert(char(8),@TheDate,112 ) as int)

    select

    count(distinct ch.vcownerid) as act

    from tblContractHistory ch

    Where

    ch.vcHomeFacilityId = @Facility

    and ch.siStatusId = 1

    and @date between ch.from_date and ch.to_date

  • May 11, 2010 at 4:16 pm

    #1165867

    a few things I noticed:

    If the columns from_date and to_date contain date values, you should store them as datetime data, not as int.

    Based on your description it's unclear how @date, ch.from_date, ch.to_date, and "12 months" need to be compared. Please clarify.

    The best way to do so is to provide some sample data and expected result in a ready to use format as described in the first link in my signature. This would help a lot more than a rather long verbal description...


    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]

  • May 11, 2010 at 5:32 pm

    #1165893

    This is some sample data

    drop table #sample_data

    create table #sample_data

    (

    memberid varchar(10),

    facility varchar(2),

    from_date int,

    to_date int

    )

    insert into #sample_data

    (memberid, facility, from_date, to_date)

    select '10001', '01', 20100115, 20101231 union all

    select '10002', '01', 20100115, 20101231 union all

    select '10003', '02', 20100115, 20101231 union all

    select '10004', '01', 20100115, 20101231 union all

    select '10005', '01', 20100215, 20101231 union all

    select '10006', '02', 20100215, 20101231 union all

    select '10007', '02', 20100215, 20101231 union all

    select '10008', '02', 20100215, 20101231 union all

    select '10009', '02', 20100215, 20101231 union all

    select '10010', '02', 20100215, 20101231

    select * from #sample_data

    This is the result I would like to see

    MemberCountFacility Month

    3011

    1021

    4012

    6022

    Hope this makes more sense

  • May 11, 2010 at 7:45 pm

    #1165912

    shjaffer (5/11/2010)

    I have this query that I want it to give me data for the 12 months.

    Thanks for the sample data but... WHICH 12 months? Current Calendar year that contains @TheDate? Previous 12 months from @TheDate? Or ???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 11, 2010 at 9:28 pm

    #1165933

    I only added data for 2 months. If I could get the results like I want for the 2 months I will be happy.

    My issue is I don't know how to make my @Date variable come up with 12 values - and each being an end of month.

    Somehow I need the @Date variable to be '1/31/2010', '2/28/2010', '3/31/2010'...

    Hopefully I am making sense

  • May 11, 2010 at 11:40 pm

    #1165959

    I dint understand your Expected result.. can u pls elaborate??

    I see that

    1. For the first month, you are finding the count of distinct facility - which gives '01' = 3 and '02' = 1.

    2. But for second month, the count is '01' = 1 and '02' = 5; but in your expected result , for second month ('01' = 4 and '02' = 6) , you are adding the result of previous month also with this!

    Is that your requirement, friend ?

  • May 12, 2010 at 12:49 am

    #1165980

    My expected results are the total number of Members at the end of every month - not members who were added during the month.

  • May 12, 2010 at 2:01 am

    #1166002

    Hi friend!

    Here is a code that might do what you wished!

    ;WITH DATE_CTE AS

    (

    SELECT DISTINCT DATEPART(MM,from_date) [MONTH] FROM #sample_data

    )

    SELECT SD.facility , DC.[MONTH], COUNT(*) MEMBER_COUNT

    FROM #sample_data SD

    CROSS JOIN DATE_CTE DC

    WHERE DATEPART(MM,SD.from_date) <= DC.[MONTH]

    GROUP BY

    SD.facility ,DC.[MONTH]

    Please tell us if this work for you! Hope this helps you!

    CHeers!

  • May 12, 2010 at 10:13 pm

    #1166623

    shjaffer (5/11/2010)

    I only added data for 2 months. If I could get the results like I want for the 2 months I will be happy.

    My issue is I don't know how to make my @Date variable come up with 12 values - and each being an end of month.

    Somehow I need the @Date variable to be '1/31/2010', '2/28/2010', '3/31/2010'...

    Hopefully I am making sense

    Heh... I can generate a million rows of daily test data across a ten year period in just a couple of seconds so don't worry about that. What I need to know is WHICH 12 months?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 13, 2010 at 8:08 am

    #1166890

    It seems like I am not communicating my issue well.

    I wrote a sp with a while loop in it and I am getting what I want.

    Thanks

    SJ

  • May 13, 2010 at 8:22 am

    #1166903

    Jaffer, no while loops/cursors :-).. didn't my code work for you??

  • May 13, 2010 at 8:29 am

    #1166916

    Sorry it didn't. My from_date and to_date are stored as integers.

    Are While loops considered bad development? I have a 100 combinations of facility/months to go through.

  • May 13, 2010 at 8:44 am

    #1166939

    shjaffer (5/13/2010)

    My from_date and to_date are stored as integers.

    Try this then (assuming your from_date is 8 digits long and 5 and 6th digits represent months):

    ;WITH DATE_CTE AS

    (

    SELECT DISTINCT CAST(SUBSTRING(CAST(from_date AS VARCHAR),5,2) AS INT) [MONTH] FROM #sample_data

    )

    SELECT SD.facility , DC.[MONTH], COUNT(*) MEMBER_COUNT

    FROM #sample_data SD

    CROSS JOIN DATE_CTE DC

    WHERE CAST(SUBSTRING(CAST(SD.from_date AS VARCHAR),5,2) AS INT) <= DC.[MONTH]

    GROUP BY

    SD.facility ,DC.[MONTH]

    Are While loops considered bad development? I have a 100 combinations of facility/months to go through

    Yes they are, but not always! The code i provided will work super fast!

    Hope this u! Tell us if that worked for you!

  • May 13, 2010 at 9:39 am

    #1167006

    IMHO the main problem you should try to solve is dates stored as integer. If you can't change the column datatype, maybe you could either add another column, or work with a temp table where you can set the correct types.

    If the dates are stored as datetime, you have lots of functions available, like MONTH, or DATEADD and DATEDIFF. These allow you easy manipulation of dates and you'll be able to write a simple query like this:

    drop table #sample_data

    create table #sample_data

    (

    memberid varchar(10),

    facility varchar(2),

    from_date int,

    to_date int,

    from_date_dt datetime,

    to_date_dt datetime

    )

    insert into #sample_data

    (memberid, facility, from_date, to_date,from_date_dt,to_date_dt)

    select '10001', '01', 20100115, 20101231, '20100115', '20101231' union all

    select '10002', '01', 20100115, 20101231, '20100115', '20101231' union all

    select '10003', '02', 20100115, 20101231, '20100115', '20101231' union all

    select '10004', '01', 20100115, 20101231, '20100115', '20101231' union all

    select '10005', '01', 20100215, 20101231, '20100215', '20101231' union all

    select '10006', '02', 20100215, 20101231, '20100215', '20101231' union all

    select '10007', '02', 20100215, 20101231, '20100215', '20101231' union all

    select '10008', '02', 20100215, 20101231, '20100215', '20101231' union all

    select '10009', '02', 20100215, 20101231, '20100215', '20101231' union all

    select '10010', '02', 20100215, 20101231, '20100215', '20101231'

    select * from #sample_data

    select count(*) as member_count, s.facility, Q.mnth as [month]

    from (select 1 as mnth union select 2 /*union select 3 union select 4... etc. until 12*/) as Q

    join #sample_data s

    on MONTH(s.from_date_dt) <= Q.mnth and MONTH(s.to_date_dt)>= Q.mnth

    group by Q.mnth, s.facility

    order by Q.mnth

    Of course, in a real scenario I would probably create a permanent table for months (or use one I already have available), and I would prefer DATEADD/DATEDIFF over MONTH. But you didn't supply enough information to create full solution (see Jeff's question about what 12 months do you wish to display), so I'm just posting one of the simplest ways how to achieve required result. It probably won't work well if some of the dates are from a different year etc., but that's precisely what was missing in the requirements, so I just took the supplied testing data and fitted the solution to them.

  • May 13, 2010 at 11:37 am

    #1167092

    shjaffer (5/13/2010)

    It seems like I am not communicating my issue well.

    I wrote a sp with a while loop in it and I am getting what I want.

    Thanks

    SJ

    I'm trying to communicate that a While Loop is a bad thing. Trying to help you do it correctly and with some performance and scalability. I've asked the same question twice and you keep fighting me so I guess I'll just move on. Good luck with the While Loop. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

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