Share
Report
955We have one customer entity in our Great Plains system, who does not get invoices posted. So the following SQL that looks at SOP10100, SOP10200, SOP30200, SOP30300 does not get any data for Census, Billing, PPD, fields. As you can see, I am linking via the SOP number and type. Is ther something you can point at that I am missing in this quest?
TIA, Frank
USE
[PSZ]GO
/****** Object: StoredProcedure [dbo].[spu_CRSS_Section3a] Script Date: 02/10/2010 09:54:01 ******/
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
-- =============================================
-- Author:
-- Create date: 17-July-2009
-- Description: To automatically produce section 3 data
-- =============================================
alter
PROCEDURE [dbo].[spu_CRSS_Section3a_test](
@STARTDATE
DATETIME,@ENDDATE
DATETIME,@Customer VARCHAR(20))
AS
BEGIN
DECLARE @STARTDATE1 DATETIMEDECLARE
@STARTDATE2 DATETIMEDECLARE @STARTDATE3 DATETIMEDECLARE
@STARTDATE4 DATETIME--- SET STARTDATES
SET
@STARTDATE1 = DateAdd (m , -1 , @EndDate +1)SET
@STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )SET
@STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )SELECT IV00101.ITMGEDSC,
IV00101
.ITEMDESC, RM00101.CUSTNAME,RM00101
.CPRCSTNM, IV40600.UserCatLongDescr,CATS
.UserCatLongDescr AS Expr1, SOP10200.ITEMNMBR,SOP10200
.SOPNUMBE, SOP10200.QUANTITY,SOP10200
.OXTNDPRC, SOP10200.SOPTYPE,SOP10100
.DOCDATE, 'Current' AS sourceINTO
#tempFROM
sop10200 AS SOP10200 INNER JOINiv00101
AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOINsop10100
AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOINiv40600
AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOINiv40600
AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOINrm00101
AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBRWHERE
(DOCDATE BETWEEN @StartDate4 AND @EndDate) OR (DOCDATE BETWEEN @StartDate AND @EndDate)UNION
ALLSELECT IV00101.ITMGEDSC,IV00101
.ITEMDESC, RM00101.CUSTNAME,RM00101
.CPRCSTNM, IV40600.UserCatLongDescr,CATS
.UserCatLongDescr AS Expr1, SOP30300.ITEMNMBR,SOP30300
.SOPNUMBE, SOP30300.QUANTITY,SOP30300
.OXTNDPRC, SOP30300.SOPTYPE,SOP30200
.DOCDATE, 'History' AS sourceFROM
sop30300 AS SOP30300 LEFT OUTER JOINiv00101
AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOINsop30200
AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOINiv40600
AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOINiv40600
AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOINrm00101
AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBRWHERE
(DOCDATE BETWEEN @StartDate4 AND @EndDate) OR(DOCDATE BETWEEN @StartDate AND @EndDate)
SELECT
CustName
,ItemDesc
, CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC, CAST(Quantity AS FLOAT) AS QUANTITY,DOCDATE
, SOURCE,ITEMNMBR
,CPRCSTNM
, CAST(0.0 AS FLOAT) AS BILLED, CAST(0.0 AS FLOAT) AS BILLED1, CAST(0.0 AS FLOAT) AS BILLED2, CAST(0.0 AS FLOAT) AS BILLED3, CAST(0.0 AS FLOAT) AS BILLED4, CAST(0.0 AS FLOAT) AS CENSUS, CAST(0.0 AS FLOAT) AS CENSUS1, CAST(0.0 AS FLOAT) AS CENSUS2, CAST(0.0 AS FLOAT) AS CENSUS3, CAST(0.0 AS FLOAT) AS CENSUS4, CAST(0.0 AS FLOAT) AS PPD1, CAST(0.0 AS FLOAT) AS PPD2, CAST(0.0 AS FLOAT) AS PPD3, CAST(0.0 AS FLOAT) AS PPD4, CAST(0.0 AS FLOAT) AS QUOTED, CAST(0.0 AS FLOAT) AS QUOTED1, CAST(0.0 AS FLOAT) AS QUOTED2, CAST(0.0 AS FLOAT) AS QUOTED3, CAST(0.0 AS FLOAT) AS QUOTED4, CAST(0.0 AS FLOAT) AS LTOTINTO
#FinalReportFROM
#tempWHERE
custname = 'Northern Adult Day Care Center'select
* from #FinalReportUPDATE
#FinalReportSET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END
select
* from #FinalReportUPDATE
#FinalReportSET
BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,BILLED2
= CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,BILLED3
= CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END
select
* from #FinalReportUPDATE
#FinalReportSET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END
select
* from #FinalReportUPDATE
#FinalReportSET
CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,CENSUS2
= CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,CENSUS3
= CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END
select
* from #FinalReportUPDATE
#FinalReportSET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END
select
* from #FinalReportUPDATE
#FinalReportSET
QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,QUOTED2
= CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,QUOTED3
= CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 ENDselect * from #FinalReport
--- SUMMARY FRO PPD Calculation
SELECT
CUSTNAME,CPRCSTNM
, MAX(CENSUS1) AS CENSUS1, MAX(BILLED1) AS BILLED1, MAX(CENSUS2) AS CENSUS2, MAX(BILLED2) AS BILLED2, MAX(CENSUS3) AS CENSUS3, MAX(BILLED3) AS BILLED3, MAX(CENSUS4) AS CENSUS4, MAX(BILLED4) AS BILLED4, SUM(QUOTED1) AS QUOTED1, SUM(QUOTED2) AS QUOTED2, SUM(QUOTED3) AS QUOTED3, SUM(QUOTED4) AS QUOTED4, CAST(0 AS FLOAT) AS PPD1, CAST(0 AS FLOAT) AS PPD2, CAST(0 AS FLOAT) AS PPD3, CAST(0 AS FLOAT) AS PPD4, CAST(0 AS FLOAT) AS LTOTINTO
#SUMMARYFROM
#FinalReportGROUP
BY CUSTNAME,CPRCSTNM
select
* from #SUMMARYUPDATE
#SUMMARYSET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END
UPDATE
#SUMMARYSET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END
UPDATE
#SUMMARYSET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END
UPDATE
#SUMMARYSET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END
UPDATE
#SUMMARYSET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 + BILLED3 + BILLED4
SELECT
* FROM #SUMMARY--select * from #temp where custname = 'Northern Adult Day Care Center'
END
*This post is locked for comments
All responses (
Answers (
