ODBC Query help 1

[CCPKGUY]

I have a ODBC query created below:

SELECT pu_balance_hist.account_no, pu_balance_hist.occupant_code, pu_balance_hist.trans_date, pu_balance_hist.jour_code, pu_balance_hist.trans_amt, pu_balance_hist.desc_code, iccpaymc.descript, Month(trans_date)
FROM Northstar_live.harris_live.iccpaymc iccpaymc, Northstar_live.harris_live.pu_balance_hist pu_balance_hist
WHERE iccpaymc.glacct = pu_balance_hist.desc_code AND ((pu_balance_hist.jour_code='CR') AND (pu_balance_hist.trans_date>={ts '2013-01-01 00:00:00'}) AND (iccpaymc.descript<>'Web Credit Card Payment'))
ORDER BY pu_balance_hist.trans_date


I am trying to add if Month(trans_date)=1 then January 2013. I am not sure on how to write that part. I would like to get it from Janaury 2013 to December 2013. Any suggestions?

 

[CCPKGUY]

I forgot to add that I wanted to sorted by trans_date then by account.no

ORDER BY pu_balance_hist.trans_date

ORDER BY pu_balance_hist.account.no

 

[bborissov]

SELECT pu_balance_hist.account_no,
       pu_balance_hist.occupant_code,
	   pu_balance_hist.trans_date,
	   pu_balance_hist.jour_code,
	   pu_balance_hist.trans_amt,
	   pu_balance_hist.desc_code,
	   iccpaymc.descript,
	   Month(trans_date)
FROM Northstar_live.harris_live.iccpaymc iccpaymc,
INNER JOIN Northstar_live.harris_live.pu_balance_hist pu_balance_hist
      ON iccpaymc.glacct = pu_balance_hist.desc_code
     AND ((pu_balance_hist.jour_code='CR') 
     AND (pu_balance_hist.trans_date >= '20130101' AND pu_balance_hist.trans_date < '20140101' )) ---Here?
WHERE (iccpaymc.descript<>'Web Credit Card Payment')
ORDER BY pu_balance_hist.trans_date

?




Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

...

ORDER BY pu_balance_hist.trans_date,  pu_balance_hist.account.no 
[/code


Borislav Borissov
VFP9 SP2, SQL Server

 

[CCPKGUY]

Thanks bborissov

In my SQL below I have the order that I want as far as the trans date and account no

SELECT pu_balance_hist.account_no, pu_balance_hist.occupant_code, pu_balance_hist.trans_date, pu_balance_hist.jour_code, pu_balance_hist.trans_amt, pu_balance_hist.desc_code, iccpaymc.descript, Month(trans_date)

FROM Northstar_live.harris_live.iccpaymc iccpaymc, Northstar_live.harris_live.pu_balance_hist pu_balance_hist
WHERE iccpaymc.glacct = pu_balance_hist.desc_code AND ((pu_balance_hist.jour_code='CR') AND (pu_balance_hist.trans_date>={ts '2013-01-01 00:00:00'}) AND (iccpaymc.descript<>'Web Credit Card Payment'))
ORDER BY pu_balance_hist.trans_date, pu_balance_hist.account_no

Not sure on how to add a column to show the Months to say January 2013 - December 2013. I have IFF Month(trans_date)=1 then January 2013. That did not work...any thoughts??

 

[CCPKGUY]

What I did was write Month([trans_date])and it gave me the number of the month. For instance if the date says 1/1/2013 it gives me the number 1 for January. What I would like to see is January 2013. What I have tried so far is Month([trans_date])'mm-dd-yyyy' and that did not work.

 

[wbodger]

something like

SELECT DATENAME(month, trans_date)

should work for you. Month(trans_date) returns an integer.