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.

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.-PatP

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

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

KBS is a Mckesson product. My apologies

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

--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 = ''

Replace the line that currently reads:with one that reads:-PatP