Re: [SQL] Retrieve month from date

[RPK]

Thanks both of you,

I ran EXPLAIN command on above suggested query and got following result:

"Aggregate  (cost=2.77..2.79 rows=1 width=10)"
"  ->  Seq Scan on studentfeespayment  (cost=0.00..2.77 rows=1 width=10)"
"Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
without time zone) = 4::double precision)"

What does this mean? Can I optimize it better?

-- 
View this message in context: 
http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10105471
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Retrieve month from date

[RPK]

What this query will return:

Select  Extract(Month from 4/20/2007) from dual;

I suspect "dual" is not for PGSQL but Oracle. But I need to run the above
query. What is the replacement of "dual" in PGSQL.


Bart Degryse wrote:
> 
> Please always include the error message you get when something isn't
> working.
>  
> If you defined your table with quoted identifiers (create table
> "StudentFeesPayment" as ...) then try
>   Select max("ReceiptNo") from "StudentFeesPayment" where Extract(Month
> from "ReceiptMonthYear")=4;
> else try
>   Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
> from ReceiptMonthYear)=4;
> 
> 
> 
 "Rohit Khare" <[EMAIL PROTECTED]> 2007-04-20 14:19 >>>
> I am using PGSQL 8.2.3 on Windows XP.
> 
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "-mm-dd".
> I have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4.
> Or whatever month I give. 
> 
> I tried:
> 
> Select max(ReceiptNo) from StudentFeesPayment where
> Extract(Month('ReceiptMonthYear'))=4;
> 
> But it is not working. How to do it?
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10106861
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] Retrieve month from date in SQl query

[RPK]

I am using PGSQL 8.2.3 on Windows XP.

I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
"ReceiptMonthYear".
The column, "ReceiptMonthYear" stores date in the format "-mm-dd". I
have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or
whatever month I give.

I tried:

Select max(ReceiptNo) from StudentFeesPayment where
Extract(Month('ReceiptMonthYear'))=4;

But it is not working. How to do it?
-- 
View this message in context: 
http://www.nabble.com/Retrieve-month-from-date-in-SQl-query-tf3602302.html#a10063035
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] SQL query to display like this

[RPK]

I have a table with records like this:

Date  EmpIDADCode  ADAmount
-
01-Jul-07101  GPF150
01-Jul-07102  GPF.ADV100
01-Jul-07103  GPF200
01-jul-07104  GPF300

I want to show results like this using a single SQL query:

Date  EmpID  GPFGPF.ADV
-
01-Jul-07  101150  0
01-Jul-07  1020100
01-Jul-07  103200  0
01-Jul-07  104300  0


I tried:

select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord

It is showing like this:

Date  EmpID  GPFGPF.ADV
-
01-Jul-07  1010  0
01-Jul-07  1011500
01-Jul-07  1020  100
01-Jul-07  1030  0
01-Jul-07  10310000
01-Jul-07  1030  1000

It is showing multiple records of each employee for each date. First a
record with GPF and GPF.ADV both zero and then records with values. I want a
single record for each date and employee.
-- 
View this message in context: 
http://www.nabble.com/SQL-query-to-display-like-this-tf4322876.html#a12310093
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster