|
|
can someone figure out this sql?? |
|
Author |
Message |
KumarPart
|
Posted: Wed Oct 15 12:54:45 CDT 2003 |
Top |
SQL Server Developer >> can someone figure out this sql??
The following sql works fine if there are payment records in the payment
table, but if there are no records there, the AmountOwed = 0. How do I deak
with the null?
SELECT (SELECT SUM(SalesPrice)
FROM tempSalesDetail
WHERE (tempsales.tempSalesID =
tempSalesDetail.tempSalesID)) -
(SELECT SUM(paymentAmount)
FROM Payment
WHERE (tempsales.tempSalesID =
Payment.tempSalesID)) + tempSales.totalShipping + tempSales.taxableAmount AS
AmountOwed,
tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
Contacts.LAST_NAME AS theName, company.COMPANY,
Users.First_Name + ' ' + Users.Last_Name AS userName,
tempSales.SalesDate
FROM tempSales INNER JOIN
Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
INNER JOIN
company ON Contacts.COMPANY_ID = company.COMPANY_ID
INNER JOIN
Users ON tempSales.sentByID = Users.UserID
WHERE (tempSales.SalesActive = 1) AND (tempSales.salesStatusID = 1)
--
______________________
David Fetrow
HelixPoint LLC.
http://www.hide-link.com/
Interested in Affordable Email Marketing?
Check out the HelixMailer at http://www.hide-link.com/
If you are interested in becoming a Reseller of HelixPoint products, contact
______________________
SQL Server206
|
|
|
|
|
AngelWPB
|
Posted: Wed Oct 15 12:54:45 CDT 2003 |
Top |
SQL Server Developer >> can someone figure out this sql??
The NULLIF function will return NULL if two specified
values are equal. Good luck!
SELECT
NULLIF(
(SELECT SUM(SalesPrice)
FROM tempSalesDetail
WHERE (tempsales.tempSalesID =
tempSalesDetail.tempSalesID)
) -
(SELECT SUM(paymentAmount)
FROM Payment
WHERE (tempsales.tempSalesID =
Payment.tempSalesID)
) + tempSales.totalShipping
+ tempSales.taxableAmount
), 0) --remainder of NULLIF function call
AS AmountOwed,
FROM tempSales
INNER JOIN Contacts
ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
<the rest of your query>
>-----Original Message-----
>The following sql works fine if there are payment records
in the payment
>table, but if there are no records there, the AmountOwed
= 0. How do I deak
>with the null?
>
>SELECT (SELECT SUM(SalesPrice)
> FROM tempSalesDetail
> WHERE (tempsales.tempSalesID =
>tempSalesDetail.tempSalesID)) -
> (SELECT SUM(paymentAmount)
> FROM Payment
> WHERE
(tempsales.tempSalesID =
>Payment.tempSalesID)) + tempSales.totalShipping +
tempSales.taxableAmount AS
>AmountOwed,
> tempSales.tempSalesID,
Contacts.FIRST_NAME + ' ' +
>Contacts.LAST_NAME AS theName, company.COMPANY,
> Users.First_Name + ' ' +
Users.Last_Name AS userName,
>tempSales.SalesDate
>FROM tempSales INNER JOIN
> Contacts ON tempSales.CONTACT_ID =
Contacts.CONTACT_ID
>INNER JOIN
> company ON Contacts.COMPANY_ID =
company.COMPANY_ID
>INNER JOIN
> Users ON tempSales.sentByID =
Users.UserID
>WHERE (tempSales.SalesActive = 1) AND
(tempSales.salesStatusID = 1)
>
>--
>
>______________________
>David Fetrow
>HelixPoint LLC.
>http://www.helixpoint.com
>
>Interested in Affordable Email Marketing?
>Check out the HelixMailer at
http://www.helixpoint.com/helixmailer.asp
>If you are interested in becoming a Reseller of
HelixPoint products, contact
>______________________
>
|
|
|
|
|
Davef
|
Posted: Wed Oct 15 13:26:47 CDT 2003 |
Top |
SQL Server Developer >> can someone figure out this sql??
That is giving me a few errors
> The NULLIF function will return NULL if two specified
> values are equal. Good luck!
>
> SELECT
> NULLIF(
>
> (SELECT SUM(SalesPrice)
> FROM tempSalesDetail
> WHERE (tempsales.tempSalesID =
> tempSalesDetail.tempSalesID)
> ) -
> (SELECT SUM(paymentAmount)
> FROM Payment
> WHERE (tempsales.tempSalesID =
> Payment.tempSalesID)
> ) + tempSales.totalShipping
> + tempSales.taxableAmount
>
> ), 0) --remainder of NULLIF function call
>
> AS AmountOwed,
> FROM tempSales
> INNER JOIN Contacts
> ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
> <the rest of your query>
>
>
> >-----Original Message-----
> >The following sql works fine if there are payment records
> in the payment
> >table, but if there are no records there, the AmountOwed
> = 0. How do I deak
> >with the null?
> >
> >SELECT (SELECT SUM(SalesPrice)
> > FROM tempSalesDetail
> > WHERE (tempsales.tempSalesID =
> >tempSalesDetail.tempSalesID)) -
> > (SELECT SUM(paymentAmount)
> > FROM Payment
> > WHERE
> (tempsales.tempSalesID =
> >Payment.tempSalesID)) + tempSales.totalShipping +
> tempSales.taxableAmount AS
> >AmountOwed,
> > tempSales.tempSalesID,
> Contacts.FIRST_NAME + ' ' +
> >Contacts.LAST_NAME AS theName, company.COMPANY,
> > Users.First_Name + ' ' +
> Users.Last_Name AS userName,
> >tempSales.SalesDate
> >FROM tempSales INNER JOIN
> > Contacts ON tempSales.CONTACT_ID =
> Contacts.CONTACT_ID
> >INNER JOIN
> > company ON Contacts.COMPANY_ID =
> company.COMPANY_ID
> >INNER JOIN
> > Users ON tempSales.sentByID =
> Users.UserID
> >WHERE (tempSales.SalesActive = 1) AND
> (tempSales.salesStatusID = 1)
> >
> >--
> >
> >______________________
> >David Fetrow
> >HelixPoint LLC.
> >http://www.helixpoint.com
> >
> >Interested in Affordable Email Marketing?
> >Check out the HelixMailer at
> http://www.helixpoint.com/helixmailer.asp
> >If you are interested in becoming a Reseller of
> HelixPoint products, contact
> >______________________
> >
|
|
|
|
|
AngelWPB
|
Posted: Thu Oct 16 10:57:31 CDT 2003 |
Top |
SQL Server Developer >> can someone figure out this sql??
What error(s) are you getting? I may have gotten the
parentheses messed up. Here is a very simplified example
that will return all rows from Northwind.Customers, but
NULL will be substituted for country when the country is
USA:
SELECT CustomerID, NullIf(Country, 'USA')
FROM Northwind.dbo.Customers
>-----Original Message-----
>That is giving me a few errors
>
>> The NULLIF function will return NULL if two specified
>> values are equal. Good luck!
>>
>> SELECT
>> NULLIF(
>>
>> (SELECT SUM(SalesPrice)
>> FROM tempSalesDetail
>> WHERE (tempsales.tempSalesID =
>> tempSalesDetail.tempSalesID)
>> ) -
>> (SELECT SUM(paymentAmount)
>> FROM Payment
>> WHERE (tempsales.tempSalesID =
>> Payment.tempSalesID)
>> ) + tempSales.totalShipping
>> + tempSales.taxableAmount
>>
>> ), 0) --remainder of NULLIF function call
>>
>> AS AmountOwed,
>> FROM tempSales
>> INNER JOIN Contacts
>> ON tempSales.CONTACT_ID =
Contacts.CONTACT_ID
>> <the rest of your query>
>>
>>
>> >-----Original Message-----
>> >The following sql works fine if there are payment
records
>> in the payment
>> >table, but if there are no records there, the
AmountOwed
>> = 0. How do I deak
>> >with the null?
>> >
>> >SELECT (SELECT SUM(SalesPrice)
>> > FROM tempSalesDetail
>> > WHERE
(tempsales.tempSalesID =
>> >tempSalesDetail.tempSalesID)) -
>> > (SELECT SUM
(paymentAmount)
>> > FROM Payment
>> > WHERE
>> (tempsales.tempSalesID =
>> >Payment.tempSalesID)) + tempSales.totalShipping +
>> tempSales.taxableAmount AS
>> >AmountOwed,
>> > tempSales.tempSalesID,
>> Contacts.FIRST_NAME + ' ' +
>> >Contacts.LAST_NAME AS theName, company.COMPANY,
>> > Users.First_Name + ' ' +
>> Users.Last_Name AS userName,
>> >tempSales.SalesDate
>> >FROM tempSales INNER JOIN
>> > Contacts ON tempSales.CONTACT_ID
=
>> Contacts.CONTACT_ID
>> >INNER JOIN
>> > company ON Contacts.COMPANY_ID =
>> company.COMPANY_ID
>> >INNER JOIN
>> > Users ON tempSales.sentByID =
>> Users.UserID
>> >WHERE (tempSales.SalesActive = 1) AND
>> (tempSales.salesStatusID = 1)
>> >
>> >--
>> >
>> >______________________
>> >David Fetrow
>> >HelixPoint LLC.
>> >http://www.helixpoint.com
>> >
>> >Interested in Affordable Email Marketing?
>> >Check out the HelixMailer at
>> http://www.helixpoint.com/helixmailer.asp
>> >If you are interested in becoming a Reseller of
>> HelixPoint products, contact
>> >______________________
>> >
>
>
>.
>
|
|
|
|
|
|
|