Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Logic Help

Status
Not open for further replies.
fmrock

fmrock

Programmer
Sep 5, 2006
510
US
Hey everyone,

I have the following query. This seems to work, except for some weeks, there there are no Type=A or Type=P rows. I need to have both of these come back for each no matter what, even if its 0.

Any ideas would be great.


Code: 
SELECT 
	C.CPCODE Site, 
	C.BILLSTATUS FC,
	C.TYPE,
	Sum(C.PRAMOUNT) Payment_Amount,
	to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)) EOM_MONTH
FROM 
	CHARGES C
WHERE
	((C.TYPE='P') OR (C.TYPE='A') )
	AND (C.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
GROUP BY
	C.CPCODE,
	C.BILLSTATUS,
	C.TYPE,
	to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0))
 
Sort by date Sort by votes
It sounds like you're saying you need this section of the where ((C.TYPE='P') OR (C.TYPE='A') )

to be

((C.TYPE='P') OR (C.TYPE='A') OR (C.TYPE='0'))

OR

((C.TYPE='P') OR (C.TYPE='A') OR (C.TYPE IS NULL))



-- Jason
"It's Just Ones and Zeros
 
Upvote 0 Downvote
Sorry.

I need a payment amount for every week in the report. Some weeks where there are no A or P type payments, that row is left off the report. I need a row for every week to show.
 
Upvote 0 Downvote
Right -- because you're where statement is requesting ONLY those records with a payment type of A or P.

Leave that whole section out of the query and check your results.

-- Jason
"It's Just Ones and Zeros
 
Upvote 0 Downvote
There are other types in the charges table and I only need to sumerize the P and A types.
 
Upvote 0 Downvote
You need to change the where....but I cannot tell you how without seeing some sample data....

Currently your where says, in plain text...

where the TYPE is A or P and the date is between X and

But you're telling me that there are date with no A or P payment type....

So it sounds like your new where would need to say, in plain text

(where the TYPE is A or P and the date is between X and Y)
OR
the type is NOT IN (define those payment types you want to EXCLUDE) and date is between X and Y

in fact the second part of that might work on it's own....

the type is NOT IN (define those payment types you want to EXCLUDE) and date is between X and Y

--ASSUME 1,2,3 are the payment types you want to exclude

C.TYPE NOT IN ('1',2','3')
AND (C.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))

-- Jason
"It's Just Ones and Zeros
 
Upvote 0 Downvote
Same Data

Code: 
SITE	FC	Type	PAYMENT_AMOUNT	WEEK
123456	S	P	-97.60	5/4/2008
123456	S	A	8.15	5/4/2008
123456	S	P	100.00	5/11/2008
123456	S	A	15.50	5/11/2008
123456	S	P	75.80	5/18/2008
123456	S	P	25.86	5/25/2008

See how the A Records are missing for 5/18 and 5/25
 
Upvote 0 Downvote
No. I cannot see how the data is missing for those dates....because I would need to see what the records look like....what are they're PAYMENT TYPES?(Show the data of the "missing" records)

Also, send the select used to generate that data..

-- Jason
"It's Just Ones and Zeros
 
Upvote 0 Downvote
Here is the select.

Code: 
SELECT 
	C.CPCODE Site, 
	C.BILLSTATUS FC,
	C.TYPE,
	Sum(C.PRAMOUNT) Payment_Amount,
	to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
FROM 
	MEDCHARGES C
WHERE
	((C.TYPE='P') OR (C.TYPE='A') )
	AND (C.PRCODE NOT IN ('76','77','78'))
	AND (C.SPLITFLAG IS  NULL)
	AND (C.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
GROUP BY
	C.CPCODE,
	C.BILLSTATUS,
	C.TYPE,
	to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

There is no data that matchs, thats why i need to to come back as Zero.


See the zero amount rows. They dont exist in the data, but I still need a row there.
Code: 
SITE    FC    Type    PAYMENT_AMOUNT    WEEK
123456    S    P    -97.60    5/4/2008
123456    S    A    8.15    5/4/2008
123456    S    P    100.00    5/11/2008
123456    S    A    15.50    5/11/2008
123456    S    P    75.80    5/18/2008
123456    S    A    0    5/18/2008
123456    S    P    25.86    5/25/2008
123456    S    A    0    5/25/2008
 
Upvote 0 Downvote
FMRock,

I am a bit puzzled...You want a representative row (even if it is '0'), for A and P types, for each week, yet you are grouping by "Year/Month".

...and if you have hundreds of different CPCODE values and BILLSTATUS combinations, do you still want a '0' A/P row for each within the "Year/Month" starting and ending range?

So, part of the conceptual problem I'm having is that your "Same Data" posting, above, does not match your query results: "WEEK" versus "EOM_MONTH".

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
FMRock,

To be clear, I understand completely that you want a '0' row to represent those periods where no rows exist. My problem is knowing how many '0' rows you want: Do you want one for each distinct site's, distinct billstatus's, distinct type's, distinct week?...That's alot of combinations for which you must check for existing rows. We can do that...I simply need to understand the "rules" you want to follow.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
SantaMufasa,

I need a row for every grouping, even if there is no data.

I am using this data to do some graphing, and when there is a missing row, it screws up the graph.

 
Upvote 0 Downvote
SantaMufasa,

Yes your understanding is right. Also, BTW, there are 53,8xx,xxx rows in the charges table.
 
Upvote 0 Downvote
SantaMufasa,

Here is something we came up with here. What do you think of this. Can this be done better? We are still validating the data that was returned, so not sure if this is 100% yet.

Code: 
SELECT
	lf.site "Site"
	, lf.finclass "Finclass"
	, lf.patype "Type"
	, lf.week "Week"
	, CASE WHEN ac.Payment_amount > 0 THEN ac.Payment_amount ELSE 0 END Payment_amount
FROM	(SELECT loccpcode Site
		, codes Finclass
		, patype PAType
		, week week
           FROM (SELECT DISTINCT loccpcode FROM medlocations) T1
		, (SELECT DISTINCT codes FROM financialcodes) T2
		, (SELECT 'A' PAType FROM DUAL
			UNION ALL
		   SELECT 'P' PAType FROM DUAL) T3
		, (SELECT DISTINCT to_char(next_day(trunc(to_date(ACCTLOGDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
		    FROM MEDACCOUNT
		    WHERE (ACCTLOGDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
		  ) T4
	) lf
LEFT JOIN (SELECT 
		C.CPCODE Site, 
		C.BILLSTATUS FC,
		C.TYPE,
		Sum(C.PRAMOUNT) Payment_Amount,
		to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)) EOM_MONTH,
		to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
	FROM 
		MEDCHARGES C
	WHERE
		((C.TYPE='P') OR (C.TYPE='A') )
		AND (C.PRCODE NOT IN ('76','77','78'))
		AND (C.SPLITFLAG IS  NULL)
		AND (C.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
	GROUP BY
		C.CPCODE,
		C.BILLSTATUS,
		C.TYPE,
		to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)),
		to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')
	) ac
ON lf.Site = ac.Site
AND lf.Finclass = ac.Fc
AND lf.patype = ac.Type
AND lf.week = ac.week
 
Upvote 0 Downvote
I made another slight adjustment

Code: 
SELECT
    lf.site "Site"
    , lf.finclass "Finclass"
    , lf.patype "Type"
    , lf.week "Week"
    , CASE WHEN ac.Payment_amount > 0 THEN ac.Payment_amount ELSE 0 END Payment_amount
FROM    (SELECT loccpcode Site
        , codes Finclass
        , patype PAType
        , week week
           FROM (SELECT DISTINCT loccpcode FROM medlocations) T1
        , (SELECT DISTINCT codes FROM financialcodes) T2
        , (SELECT 'A' PAType FROM DUAL
            UNION ALL
           SELECT 'P' PAType FROM DUAL) T3
        , (SELECT DISTINCT to_char(next_day(trunc(to_date(ACCTLOGDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
            FROM MEDACCOUNT
            WHERE (ACCTLOGDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
          ) T4
    ) lf
LEFT JOIN (SELECT 
        C.CPCODE Site, 
        C.BILLSTATUS FC,
        C.TYPE,
        Sum(C.PRAMOUNT) Payment_Amount,
        to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)) EOM_MONTH,
        to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
    FROM 
        MEDCHARGES C
    WHERE
        ((C.TYPE='P') OR (C.TYPE='A') )
        AND (C.PRCODE NOT IN ('76','77','78'))
        AND (C.SPLITFLAG IS  NULL)
        AND (C.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
    GROUP BY
        C.CPCODE,
        C.BILLSTATUS,
        C.TYPE,
        to_char(C.CURRYEAR) || '-' || to_char(LPAD(C.CURRMONTH,2,0)),
        to_char(next_day(trunc(to_date(C.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')
    ) ac
ON lf.Site = ac.Site
AND lf.Finclass = ac.Fc
AND lf.patype = ac.Type
AND lf.week = ac.week
 
Upvote 0 Downvote
Wow....I come back from lunch and find so many posts....

Thanks Santa!

-- Jason
"It's Just Ones and Zeros
 
Upvote 0 Downvote
FMRock said:
Can this be done better? We are still validating the data that was returned, so not sure if this is 100% yet.
Click to expand...
[ul][li]Requirement #1: Correct Results. There is no need to tweak performance/technique if the results are not correct.[/li][li]Requirement #2: Faster Performance. Before tuning any code, you must first identify the costs of running the code, per time period. For example, if you run the code once a year, or once a month, it doesn't need to perform as well as something that runs once a day or once an hour.


Also, if the code takes two minutes to run, and you want to improve the performance to only one minute, then you must decide what amount of tuning effort are we willing to invest to achieve that one-minute performance improvement.

For example, it is probably not cost justifiable to invest 8 hours of tuning effort to achieve one minute of performance improvement if the job runs once a month.[/li][/ul]So, once you get correct results, let us know how long the code took to execute, and if the business needs dictate a need for performance improvement, then we can re-visit the topic of tuning.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Santa,

The data is correct. It takes between 20-25min to run and will be run weekly.


Here is the latest version of the SQL.

Code: 
SELECT
	lf.site "Site"
	, lf.finclass "Finclass"
	, lf.patype "Type"
	, lf.week "Week"
	, CASE WHEN (ac.Payment_amount IS NOT NULL) THEN ac.Payment_amount ELSE 0 END Payment_amount
FROM	(SELECT loccpcode Site
		, codes Finclass
		, patype PAType
		, week week
           FROM (SELECT DISTINCT loccpcode FROM medlocations) T1
		, (SELECT DISTINCT codes FROM financialcodes) T2
		, (SELECT 'A' PAType FROM DUAL
			UNION ALL
		   SELECT 'P' PAType FROM DUAL) T3
		, (SELECT DISTINCT to_char(next_day(trunc(to_date(ACCTLOGDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
		    FROM MEDACCOUNT
		    WHERE (ACCTLOGDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
		  ) T4
	) lf
LEFT JOIN (SELECT 
		MEDCHARGES.CPCODE Site, 
		MEDCHARGES.BILLSTATUS FC,
		MEDCHARGES.TYPE,
		Sum(MEDCHARGES.PRAMOUNT) Payment_Amount,
		to_char(next_day(trunc(to_date(MEDCHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
	FROM 
		MEDCHARGES MEDCHARGES	
	WHERE
		((MEDCHARGES.TYPE='P') OR (MEDCHARGES.TYPE='A') )
		AND (MEDCHARGES.PRCODE NOT IN ('76','77','78'))
		AND (MEDCHARGES.SPLITFLAG IS  NULL)
		AND (MEDCHARGES.POSTDATE between to_Number(to_char(next_day(trunc(sysdate)-371,'SUNDAY'),'J')) and to_Number(to_char(next_day(trunc(sysdate)-6,'SATURDAY'),'J')))
	GROUP BY
		MEDCHARGES.CPCODE,
		MEDCHARGES.BILLSTATUS,
		MEDCHARGES.TYPE,
		to_char(next_day(trunc(to_date(MEDCHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')
	) ac
ON lf.Site = ac.Site
AND lf.Finclass = ac.Fc
AND lf.patype = ac.Type
AND lf.week = ac.week
 
Upvote 0 Downvote
So, can you live with a 20-25 minute run per week?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sqlPower
  • Locked
  • Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
737
SkipVought
S
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
Tsmtbo2011
  • Locked
  • Question
Help Needed!! Micros WS6 610
Replies
1
Views
189
carp
carp
EM1107
  • Locked
  • Question
Insert date and time using UTC time in a DATE column
Replies
14
Views
979
EM1107
EM1107
kernal
  • Locked
  • Question
If Day of the Week='Y' (i.e. Mon='Y') and between a Start Date and End Date then Count #of Days 1
Replies
1
Views
158
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top