Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Smile Unanswered: Help Needed with creating a date range of previous three months in Mckesson SQL

    I need to construct a report where it will retrieve data from previous three months. The query will run once each month, pulling data from previous three months. I know the code to pull data from previous month but not multiple months. The code is SET :XEOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = :XEOPM - SQL_FN_DAYOFMONTH(:XEOPM)+1. This wil pull data from May 01, 2009 to May 31, 2009. I've tried to modify, but can't get it to pul from previous three months. Any suggesstions.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is both ugly and "brute force" because I can't identify what SQL database engine you're using (McKesson uses several different engines in different products), but it should work.
    Code:
    SET :XEOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM) + 1
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2009
    Posts
    6

    Re-Mckesson SQL Help

    Thanks, Pat. I got the data that I was looking for.I'm using kbs sql

  4. #4
    Join Date
    Jun 2009
    Posts
    6

    KBS SQL Database question

    I'm using KBS SQL -a Microsoft product. I'm looking to retrieve data from previous three months. For example April 1, to June 30. May 1 to July 31. Although the following code does work correctly, I'm looking for data to start from the first of the month. The following code is SET :XEOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM) + 1

    However the data I get back gives me data that is not in chronological order. It gives me the first data with a admisson date of May 11,I'm looking for data that has May 1 to start with.

    Thanks

  5. #5
    Join Date
    Jun 2009
    Posts
    6

    The last post-KBS is a McKesson product

    KBS is a Mckesson product. My apologies

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So is this KBS SQL actually the MUMPS based SQL query tool? It is used by McKessen in some of their online products, but I don't think it ships with any of their PC based software.

    Either way, there should be a SELECT statement somewhere after the SET statements in your script. That SELECT statement is what you need to modify, but I'd need to see it's syntax in order to help you modify it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2009
    Posts
    6

    Code for the date range

    --READ :XBDT DATE PROMPT 'ENTER BEGINNING DATE TO PULL'
    --READ :XEDT DATE PROMPT 'ENTER ENDING DATE TO PULL'
    --Modified by Rajib Chaudhuri SQL Programmer Set date range to be previous three months
    DECLARE :XBOPM DATE, :XEOPM DATE,:XDSCHSTA CHAR(3),:XUBCODE INT(2)
    ,:XPOA1 char(1),:XPOA2 char(1),:XPOA3 char(1),:XPOA4 char(1)
    ,:XPOA5 char(1),:XPOA6 char(1),:XPOA7 char(1),:XPOA8 char(1)
    ,:XPOA9 char(1),:XPOA10 char(1),:XPOA11 char(1),:XPOA12 char(1)
    ,:XPOA13 char(1),:XPOA14 char(1),:XPOA15 char(1),:XPOAP char(1)
    ,:XINTN INT(10), :XAN CHAR(10)
    /*The above are all variables to be used in the code. :XBOPM is the Begining date, :XEOPM is the ending date
    :XDSCHSTA is the discharge stat key :XUBCODE is the ub code The codes from :XPOA1 thru :XPOPAP are all
    diagnosis codes :XAN is a patient identification number :XINTN is a patient identification number. */


    SELECT *

    FROM AG_DSCHRG_DT_IDX+ AS A
    ,AG_PHYSICIAN AS B
    ,CE_ABST_CODER_AUD AS C
    -- ,CE_ABST_DIAG_DTL AS C
    --Basically the report is pulling data from three tables. The AG_DISCHRG_DT_IDX is using a outer join
    --Basically it is selecting all from the dschrg table and any fields that the physician and coder tables
    --have in common with the dschrg table

    WHERE A.MED_LINK@ATTEND_PHY = B.PHYS_NBR
    and a.intn = c.intn
    and a.an = c.an
    --AND DSCHRG_DT BETWEEN :XBDT AND :XEDT
    --AND MED_LINK@ADM_DT BETWEEN :XBOPM AND :XEOPM
    --AND DSCHRG_DT between :XBOPM and :XEOPM
    AND MED_LINK@ADM_DT BETWEEN :XBOPM AND :XEOPM
    AND MED_LINK@INPAT_OUTPAT_IND = 'In'
    AND MED_LINK@INS_COB_1_LINK@CARRIER_CODE LIKE '010%'
    AND A.FAC = 'T'

    ORDER BY :XBOPM


    INITIAL
    SET :XEOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = TODAY - SQL_FN_DAYOFMONTH(TODAY)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM)
    SET :XBOPM = :XBOPM - SQL_FN_DAYOFMONTH(:XBOPM) + 1

    DETAIL
    BREAK AFTER A.PAT_ACCT_NBR
    SET :XDSCHSTA = A.MED_LINK@DSCHRG_COND
    ,:XUBCODE = ' '
    SET :XINTN = A.INTN
    SET :XAN = A.AN
    RUN QB_RC_TEST15_SUBQUERY --Getting ub code

    IF FAC = 'T'
    SET :XTAXID = '310722120'
    ELSE
    SET :XTAXID = '310785684'
    ENDIF

    IF :XUBCODE IN ('01','02','03','04','05','06','07','08','10',
    '13','20','30','43','50','51','61','62','63',
    '64','65','66','71','72')
    SET :XUBCODE = :XUBCODE
    ELSE
    SET :XUBCODE = ''
    ENDIF
    RUN QB_RC_TEST_SUBQUERY --getting poa codes
    WRITE :XTAXID HEADING 'MPN'
    ,'' HEADING 'FacNPI'
    ,B.UPIN HEADING 'UPIN'
    ,B.NPI HEADING 'AttNPI'
    ,MED_LINK@LGTH_OF_STAY HEADING 'LOS'

    ,DEMOG_LINK@PATIENT_AGE HEADING 'AGEYRS'
    ,DEMOG_LINK@SEX HEADING 'SEX'
    ,:XUBCODE HEADING 'DISSTAT'
    ,'' HEADING 'DMV'
    ,'' HEADING 'CCODE1'
    ,'' HEADING 'CCODE2'
    ,'' HEADING 'CCODE3'
    ,'' HEADING 'CCODE4'
    ,'' HEADING 'CCODE5'
    ,'' HEADING 'CCODE6'
    ,'' HEADING 'CCODE7'
    ,'' HEADING 'CCODE8'
    ,'' HEADING 'CCODE9'
    ,'' HEADING 'CCODE10'
    ,'' HEADING 'CCODE11'
    ,ABST_DIAG_DATA_LINK@ADM_DIAG_CODE HEADING 'ADMDX'
    ,ABST_DIAG_DATA_LINK@PRIN_DIAG_CODE HEADING 'PRIDX'
    ,:XPOAP HEADING 'PRIPOA'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE1 HEADING 'SecDX1'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE2 HEADING 'SecDX2'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE3 HEADING 'SecDX3'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE4 HEADING 'SecDX4'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE5 HEADING 'SecDX5'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE6 HEADING 'SecDX6'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE7 HEADING 'SecDX7'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE8 HEADING 'SecDX8'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE9 HEADING 'SecDX9'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE10 HEADING 'SecDX10'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE11 HEADING 'SecDX11'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE12 HEADING 'SecDX12'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE13 HEADING 'SecDX13'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE14 HEADING 'SecDX14'
    ,ABST_DIAG_DATA_LINK@SEC_DIAG_CODE15 HEADING 'SecDX15'
    ,'' HEADING 'SecDX16'
    ,'' HEADING 'SecDX17'
    ,'' HEADING 'SecDX18'
    ,'' HEADING 'SecDX19'
    ,'' HEADING 'SecDX20'
    ,'' HEADING 'SecDX21'
    ,'' HEADING 'SecDX22'
    ,'' HEADING 'SecDX23'
    ,'' HEADING 'SecDX24'
    ,'' HEADING 'SecDX25'
    ,'' HEADING 'SecDX26'
    ,'' HEADING 'SecDX27'
    ,'' HEADING 'SecDX28'
    ,'' HEADING 'SecDX29'
    ,'' HEADING 'SecDX30'
    ,'' HEADING 'SecDX31'
    ,'' HEADING 'SecDX32'
    ,'' HEADING 'SecDX33'
    ,'' HEADING 'SecDX34'
    ,'' HEADING 'SecDX35'
    ,'' HEADING 'SecDX36'
    ,'' HEADING 'SecDX37'
    ,'' HEADING 'SecDX38'
    ,'' HEADING 'SecDX39'
    ,'' HEADING 'SecDX40'
    ,'' HEADING 'SecDX41'
    ,'' HEADING 'SecDX42'
    ,'' HEADING 'SecDX43'
    ,'' HEADING 'SecDX44'
    ,'' HEADING 'SecDX45'
    ,'' HEADING 'SecDX46'
    ,'' HEADING 'SecDX47'
    ,'' HEADING 'SecDX48'
    ,'' HEADING 'SecDX49'
    ,:XPOA1 HEADING 'SecPOA1'
    ,:XPOA2 HEADING 'SecPOA2'
    ,:XPOA3 HEADING 'SecPOA3'
    ,:XPOA4 HEADING 'SecPOA4'
    ,:XPOA5 HEADING 'SecPOA5'
    ,:XPOA6 HEADING 'SecPOA6'
    ,:XPOA7 HEADING 'SecPOA7'
    ,:XPOA8 HEADING 'SecPOA8'
    ,:XPOA9 HEADING 'SecPOA9'
    ,:XPOA10 HEADING 'SecPOA10'
    ,:XPOA11 HEADING 'SecPOA11'
    ,:XPOA12 HEADING 'SecPOA12'
    ,:XPOA13 HEADING 'SecPOA13'
    ,:XPOA14 HEADING 'SecPOA14'
    ,:XPOA15 HEADING 'SecPOA15'
    ,'' HEADING 'SecPOA16'
    ,'' HEADING 'SecPOA17'
    ,'' HEADING 'SecPOA18'
    ,'' HEADING 'SecPOA19'
    ,'' HEADING 'SecPOA20'
    ,'' HEADING 'SecPOA21'
    ,'' HEADING 'SecPOA22'
    ,'' HEADING 'SecPOA23'
    ,'' HEADING 'SecPOA24'
    ,'' HEADING 'SecPOA25'
    ,'' HEADING 'SecPOA26'
    ,'' HEADING 'SecPOA27'
    ,'' HEADING 'SecPOA28'
    ,'' HEADING 'SecPOA29'
    ,'' HEADING 'SecPOA30'
    ,'' HEADING 'SecPOA31'
    ,'' HEADING 'SecPOA32'
    ,'' HEADING 'SecPOA33'
    ,'' HEADING 'SecPOA34'
    ,'' HEADING 'SecPOA35'
    ,'' HEADING 'SecPOA36'
    ,'' HEADING 'SecPOA37'
    ,'' HEADING 'SecPOA38'
    ,'' HEADING 'SecPOA39'
    ,'' HEADING 'SecPOA40'
    ,'' HEADING 'SecPOA41'
    ,'' HEADING 'SecPOA42'
    ,'' HEADING 'SecPOA43'
    ,'' HEADING 'SecPOA44'
    ,'' HEADING 'SecPOA45'
    ,'' HEADING 'SecPOA46'
    ,'' HEADING 'SecPOA47'
    ,'' HEADING 'SecPOA48'
    ,'' HEADING 'SecPOA49'
    ,ABST_PROC_LINK@PR_PROC_CD HEADING 'PRIPROC'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD1 HEADING 'PROC1'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD2 HEADING 'PROC2'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD3 HEADING 'PROC3'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD4 HEADING 'PROC4'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD5 HEADING 'PROC5'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD6 HEADING 'PROC6'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD7 HEADING 'PROC7'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD8 HEADING 'PROC8'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD9 HEADING 'PROC9'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD10 HEADING 'PROC10'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD11 HEADING 'PROC11'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD12 HEADING 'PROC12'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD13 HEADING 'PROC13'
    ,ABST_PROC_SEC_DATA_LINK@SEC_PROC_CD14 HEADING 'PROC14'
    ,'' HEADING 'PROC15'
    ,'' HEADING 'PROC16'
    ,'' HEADING 'PROC17'
    ,'' HEADING 'PROC18'
    ,'' HEADING 'PROC19'
    ,'' HEADING 'PROC20'
    ,'' HEADING 'PROC21'
    ,'' HEADING 'PROC22'
    ,'' HEADING 'PROC23'
    ,'' HEADING 'PROC24'
    ,'' HEADING 'PROC25'
    ,'' HEADING 'PROC26'
    ,'' HEADING 'PROC27'
    ,'' HEADING 'PROC28'
    ,'' HEADING 'PROC29'
    ,'' HEADING 'PROC30'
    ,'' HEADING 'PROC31'
    ,'' HEADING 'PROC32'
    ,'' HEADING 'PROC33'
    ,'' HEADING 'PROC34'
    ,'' HEADING 'PROC35'
    ,'' HEADING 'PROC36'
    ,'' HEADING 'PROC37'
    ,'' HEADING 'PROC38'
    ,'' HEADING 'PROC39'
    ,'' HEADING 'PROC40'
    ,'' HEADING 'PROC41'
    ,'' HEADING 'PROC42'
    ,'' HEADING 'PROC43'
    ,'' HEADING 'PROC44'
    ,'' HEADING 'PROC45'
    ,'' HEADING 'PROC46'
    ,'' HEADING 'PROC47'
    ,'' HEADING 'PROC48'
    ,'' HEADING 'PROC49'

    ,ABST_DRG_LINK@DRG_FINAL_NBR HEADING 'DRG'
    ,'' HEADING 'AGEDAYS'
    ,'' HEADING 'DISAGEDAYS'
    ,ABST_NB_DTH_CLS_LINK@NB_WT_KG HEADING 'BRTHWGHT'
    ,'' HEADING 'NURLEVEL'
    ,FIN_LINK@TOT_CHGS HEADING 'TOTCHRG'
    ,'' HEADING 'NONCOVCHRG'
    ,A.ABST_GEN_LINK@ABST_INIT HEADING 'CODER'
    ,A.VISIT_LINK@ADM_SOURCE HEADING 'AdmitSrc'
    ,A.FAC HEADING 'CAMPUS'

    SET :XPOA1 = '',:XPOA2 = '',:XPOA3 = '',:XPOA4 = ''
    ,:XPOA5 = '',:XPOA6 = '',:XPOA7 = '',:XPOA8 = ''
    ,:XPOA9 = '',:XPOA10 = '',:XPOA11 = '',:XPOA12 = ''
    ,:XPOA13 = '',:XPOA14 = '',:XPOA15 = '',:XPOAP = ''

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Replace the line that currently reads:
    Code:
       ORDER BY :XBOPM
    with one that reads:
    Code:
       ORDER BY MED_LINK@ADM_DT
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •