|
i am new to asp/sql and of course, i have some problems with it.
please let me explain:
i have a problem with a sql result and its output in asp
my sql result table looks like this
city date payment total
================================================
city1 1/1/2007 Visa 123.32
city1 1/1/2007 Mastercard 23.30
city1 1/1/2007 AMEX 40.30
city1 1/2/2007 Mastercard 12.30
city1 1/2/2007 AMEX 234.20
city2 1/1/2007 Visa 111.11
city2 1/1/2007 AMEX 222.22
city2 1/3/2007 Visa 233.33
city3 1/1/2007 Visa 123.23
city3 1/1/2007 Mastercard 45.34
....
and so forth
there are 3 fix payment options I want to show always: Visa, Mastercard
and AMEX.
I would like to output this result table shown before as followed in
asp:
First sorted by city, then date, and then always followed by 3 fixed
columns for the payment types
City1 Visa Mastercard Amex
-------------------------------------------------------------------
1/1/2007 123.32 23.30 40.30
1/2/2007 - 12.30 234.20
next city
City2 Visa Mastercard Amex
-------------------------------------------------------------------
1/1/2007 111.11 - 222.22
1/3/2007 233.33 - -
next city
City3 Visa Mastercard Amex
-------------------------------------------------------------------
1/1/2007 111.11 - 222.22
1/3/2007 233.33 - -
I tried it with looping while checking if date and city changed.
however, every time i try it, i can't show it properly like the way i
want it.
Please, can somebody help?
If you need more information, please let me know.
thank you so much.
|
|
|
|
|
select City, PaymentDate,
sum(case PaymentType when 'Visa' then PaymentTotal else 0 end) as Visa,
sum(case PaymentType when 'Mastercard' then PaymentTotal else 0 end) as Mastercard,
sum(case PaymentType when 'Amex' then PaymentTotal else 0 end) as Amex
from PaymentTable
group by City, PaymentDate
order by City, PaymentDate
|
|
|
|
|
Thank you so much Andy!
I really appreciate this, that made my day.
Thanks
|
|
|
|
|
Hi,
Need a simple create table script for SQL server 2007.
Here's the structure:
Column1: primary key, integer, auto increment
Column2: Name, char, length 100, not null
Column3: address, char length 200, not null
Please help
ASP - AJAX is SEXY. PERIOD.
|
|
|
|
|
|
Can anyone help me out please? I keep getting errors when importing msado15.dll even when removing the one error that microsoft identified as a bug. For win ce 4.23, is it even possible?
|
|
|
|
|
Hi
I have exported pubs authors table into flatfile. I am trying to import the same flat file data into dataset in C#. Could you any one help me please.
Data from flat file to Dataset(same format like authors table).
kesavan
kesavan
|
|
|
|
|
|
Guys, can any body suggest a place where i can get general information about sql server(2000-2005 other than google and MSC book.
Thanks
|
|
|
|
|
|
thanks hasan
|
|
|
|
|
Hello,
I have the following sql which will be stored a string, to be executed.
However, it gives me a syntax error -> Incorrect syntax near the keyword 'not'.
This is because sql server thinks its a keyword, how can I format my sql so that the arguments can be passed correctly.
<br />
BEGIN<br />
SET @SQL = 'CREATE PROCEDURE [dbo].[incident_selAllFilterRows]<br />
AS<br />
SELECT incidentID, company, subject, contact, phoneNo, assignedTo, priority<br />
FROM incident<br />
WHERE [status] = 'not started' AND [status] = 'in progress''<br />
EXEC sp_executeSQL @SQL END<br />
Many thanks,
Steve
|
|
|
|
|
steve_rm wrote: SET @SQL = 'CREATE PROCEDURE [dbo].[incident_selAllFilterRows]
AS
SELECT incidentID, company, subject, contact, phoneNo, assignedTo, priority
FROM incident
WHERE [status] = 'not started' AND [status] = 'in progress''
You have nested quotes (')... basically, the statement is terminated at the ' before the not and the rest of the statement is ignored / not recognised.
HTH
|
|
|
|
|
In addition to the previous respose. You might also want to check your WHERE clause for logical correctness.
WHERE [status] = 'not started' AND [status] = 'in progress'
What you are asking above is that the status column must be equal to "not started" AND "in progress" at the same time. Did you mean OR?
|
|
|
|
|
Missed that one...:->
Teaches me to read until the end
|
|
|
|
|
Thanks,
Single quotes should be used.
Steve
|
|
|
|
|
Hi,
I would like to know which is the most latest and good way of writing SQL Joins. Is it by using *=, =* etc.
eg:
select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
( or)is it ANSI Standard?
Eg: SELECT distinct *
FROM employee
LEFT OUTER JOIN
department
ON employee.DepartmentID = department.DepartmentID
Please help.
rmr
|
|
|
|
|
shivarishxxxx wrote: select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
This type of join is slowly being phased out. The Inner/Outer join is the way to go for you.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks for your prompt reply.
rmr
|
|
|
|
|
Does anybody know how to get the sql log information?
Thanks,
Rob
http://www.vwsolutions.com
|
|
|
|
|
Hello,
I have the following query; the results I need are returned however so are null values.
I need to exclude any results that are not defined in the case function.
select
(case when status like 'dialing%' then dtime end) as startcall
, (case when status like 'line%' then dtime end) as endcall
, (case when status like 'line%' then trunk end) as trunk
from
Table1
All of my references mention boolean logic, however; I am not sure how to apply it or if that's the right approach.
Any help or information would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
Hulicat wrote: I need to exclude any results that are not defined in the case function
Then shouldn't it go in a WHERE clause?
Since I don't actually know what you are trying to achieve I cannot say further what the WHERE clause should look like.
|
|
|
|
|
I am trying to obtain only the results defined in the where clauses without any nulls
Sample of Current Results:
StartCall……………...........EndCall………………………………..........Trunk
NULL……... ..................12/4/2006 3:09:48 PM………..………. 8
NULL…………… .............NULL……………………………. ............NULL
NULL……………….. ...............NULL……………………………. ............NULL
NULL……………. ............NULL……………………………. ............NULL
NULL…………….. ............NULL……………………………. ............NULL
NULL…………..................NULL……………………………. ............NULL
NULL………………. ..........NULL……………………………. ............NULL
NULL…….......…............NULL……………………………. ............NULL
12/4/2006 3:27:57 PM….....NULL……………………………. ............NULL
NULL…….. ………...........NULL……………………………. ............NULL
NULL…….. ………...........NULL…………………………….. ............NULL
Desired output:
Startcall……………………….........EndCall…………………………...........Trunk
12/4/2006 3:27:57 PM……....…12/4/2006 3:09:48 PM……....…….8
Regards,
Hulicat
|
|
|
|
|
select CallId,
max(case when status like 'dialing%' then dtime end) as startcall,
max(case when status like 'line%' then dtime end) as endcall,
max(case when status like 'line%' then trunk end) as trunk
from Table1
group by CallId
order by CallId
This assumes that all of the records for a single phone call share a unique identifier named "CallId".
|
|
|
|
|
I have a sqlconnection to a sql server 2005.
This server is in one server and my application is in another server.
I create a userid and password but when i try to connect to my database an error saying
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
appears. I do not know what is the problem for this.
Can somebody help me
Thanks
Javierarka
|
|
|
|