Need SQL Function Brainwave

[gggram2000]

I have this function in sql server 2005:

ALTER FUNCTION dbo.fnGuestID()
RETURNS TABLE 
AS
RETURN SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice
       From Guest AS g, Sales As s 
       WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice' 
       GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice

And I get the different values on their respected cells....What I would like to know or get some ideas on, is how to make the same GuestID, that comes up more than once on the table, be on the same cells together one below the other instead of two separate cells...For example: Let's say there are more than one GuestID's that are the same, so it has same GuestFirstName, LastName and personal info the same, but the Sales part of it changes because it is a different Sale. Once again, I need some ideas on how to make that specific Guest(Same GuestID) have the different sales included for the same person...any enlightenment will be greatly appreciated. Let me know if this isn't clear.

[ck9663]

I have this function in sql server 2005:

ALTER FUNCTION dbo.fnGuestID()
RETURNS TABLE 
AS
RETURN SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice
       From Guest AS g, Sales As s 
       WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice' 
       GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice

And I get the different values on their respected cells....What I would like to know or get some ideas on, is how to make the same GuestID, that comes up more than once on the table, be on the same cells together one below the other instead of two separate cells...For example: Let's say there are more than one GuestID's that are the same, so it has same GuestFirstName, LastName and personal info the same, but the Sales part of it changes because it is a different Sale. Once again, I need some ideas on how to make that specific Guest(Same GuestID) have the different sales included for the same person...any enlightenment will be greatly appreciated. Let me know if this isn't clear.

try:

 SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,
  sum(s.SaleTotal) as TotalSaleOfThisGuestID, count(*) as NumberOfSaleOfThisGuestID
       From Guest AS g, Sales As s 
       WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice' 
       GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail
 

-- CK

[gggram2000]

try:

 SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,
  sum(s.SaleTotal) as TotalSaleOfThisGuestID, count(*) as NumberOfSaleOfThisGuestID
       From Guest AS g, Sales As s 
       WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice' 
       GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail
 

That works, the only issue is that I would like to print this info, and when I do that I would like to have the quantity for each ID, like the different items, quantities, sale prices and the dates of each sale....I hope you understand what I mean, thanks for the suggestion tho I can use that for one aspect of it.

[ck9663]

That works, the only issue is that I would like to print this info, and when I do that I would like to have the quantity for each ID, like the different items, quantities, sale prices and the dates of each sale....I hope you understand what I mean, thanks for the suggestion tho I can use that for one aspect of it.

if that's the case you will again break the rows into two or more. sum and count per guestid are aggregates...quantities, sale, item are all detail...if you include them, you will break the aggregate again...

-- CK

[gggram2000]

if that's the case you will again break the rows into two or more. sum and count per guestid are aggregates...quantities, sale, item are all detail...if you include them, you will break the aggregate again...

-- CK

Ok, thanks for the help

[gggram2000]

if that's the case you will again break the rows into two or more. sum and count per guestid are aggregates...quantities, sale, item are all detail...if you include them, you will break the aggregate again...

-- CK

One more question...is it possible to include an If statement or case in a sql function? (Not stored procedure) Because I was thinking about something like calling both dates separately...to somehow get all the dates, I wouldn't mind anything else about the sale not to come out, but the date would be great...

[ck9663]

One more question...is it possible to include an If statement or case in a sql function? (Not stored procedure) Because I was thinking about something like calling both dates separately...to somehow get all the dates, I wouldn't mind anything else about the sale not to come out, but the date would be great...

yes. it's called CASE...WHEN


-- CK