Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nhaas
Yak Posting Veteran
90 Posts |
 Posted - 2009-07-01 : 11:42:56
|
| I have a query that shows "Number Header and Total"query = SELECT SERVE_NO, ACC_HEADER, '$' + CONVERT(varchar(12), TRANS_TOTAL, 1) FROM Transactions WHERE (serve_No = @Phone) ORDER BY ACC_HEADER DESCBut what I want is instead of Header I want the month that corisponds with the header. I know I am close but dont have that Cigar yet - could someone show me the errors of my ways:SELECT SERVE_NO, ACC_HEADER as lkheader, '$' + CONVERT(varchar(12), TRANS_TOTAL, 1) FROM Transactions WHERE (serve_No = @Phone) and ACC_HEADER in (Select BILL_Year_Month from QF_Usagesummarybycc Where ACC_HEADER = lkHEADER) ORDER BY ACC_HEADER DESCThanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 14:42:25
|
Are there error messages?Try this:SELECT SERVE_NO, ACC_HEADER as lkheader, '$' + CONVERT(varchar(12), TRANS_TOTAL, 1) FROM Transactions ta WHERE (ta.serve_No = @Phone) and ta.ACC_HEADER in (Select BILL_Year_Month from QF_Usagesummarybycc q Where q.ACC_HEADER = ta.ACC_HEADER) ORDER BY ACC_HEADER DESC
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2009-07-01 : 17:50:12
|
| OK, I am getting an error of "Syntax error converting the varchar value '2007-09' to a column of data type int."And the 2007-09 looks to be the correct returned data. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-01 : 22:36:10
|
quote:
Originally posted by nhaas
OK, I am getting an error of "Syntax error converting the varchar value '2007-09' to a column of data type int."And the 2007-09 looks to be the correct returned data.
how to convert the string "2007-09" to integer ? Convert to what value ?This error is coming from which part of the query ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-02 : 01:43:33
|
The question is:What kind of data is in ACC_HEADER and what datatype is that column (int?).I have already guessed...an error will come...Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2009-07-02 : 11:27:45
|
| OK for the ACC_Header in QQF_UsageSummaryByCC it is int 4, BILL_YEAR_MONTH is varchar(7)I think that I am going to approach this a little different... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-02 : 11:39:20
|
ACC_Header is the Year ? Maybe convert the first 4 chars of BILL_Year_Month to integer ?Select convert(int, left(BILL_Year_Month,4)) from QF_Usagesummarybycc
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Query guidence