Help: 2 queries involving data types & functions

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help: 2 queries involving data types & functions

Author

Topic

II Traveler II
Starting Member

11 Posts

Posted - 2011-11-09 : 20:09:27

I have two queries that I'm running into a problem with. Both of these involve various data types and functions. My first problem might be easy to solve.

1) I need to convert the following query to return the invoices due before the last day of the current month, instead of 30 days from now. I know it involves something with pulling the first day of the next month, then subtracting it by one to get the last day. But I just can't wrap my head around getting the code for that : /

SELECT InvoiceNumber, (InvoiceTotal - PaymentTotal - CreditTotal) AS Balance, InvoiceDueDate
FROM Invoices
WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0 and InvoiceDueDate < GETDATE() + 30

2) I need to write a summary query WITH CUBE that returns LineItemSum (which is the sum of InvoiceLineItemAmount) grouped by Account (an alias for Accounting Description) and State (an alias for VendorState). I also need to use the CASE and GROUPING function to substitute the literal value "*ALL*" for rows with null values. Here is the code I have, but it's an absolutely mess and I know the results aren't right.

SELECT
SUM(InvoiceLineItemAmount) AS LineItemSum,
AccountDescription AS Account,
CASE
WHEN GROUPING(VendorState) = 1 THEN '*ALL*'
ELSE VendorState
END AS State
FROM Vendors, InvoiceLineItems, GLAccounts
GROUP BY VendorState, AccountDescription, InvoiceLineItemAmount WITH CUBE

Here is the table structure:
_______________
Vendors table

VendorID
VendorName
VendorAdress1
VendorAdress2
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactLName
VendorContactFName
DefaultTermsID
DefaultAccountNo
___________________

___________________
InvoiceLineItems

InvoiceID
InvoiceSequence
AccountNo
InvoiceLineItemAmount
InvoiceLineItemDescription
___________________

___________________
GLAccounts

AccountNo
AccountDescription
___________________

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 20:27:58

(1) to get the last day of current month


select	dateadd(month, datediff(month, 0, getdate()) + 1, -1)

So your query should be (assuming your InvoiceDueDate does not contain time -> 00:00)

WHERE InvoiceDueDate <= dateadd(month, datediff(month, 0, getdate()) + 1, -1)

if it does change to

WHERE InvoiceDueDate <  dateadd(month, datediff(month, 0, getdate()) + 1, 0)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 20:30:23

quote:
2) I need to write a summary query WITH CUBE that returns LineItemSum (which is the sum of InvoiceLineItemAmount) grouped by Account (an alias for Accounting Description) and State (an alias for VendorState).

I can see that State (VendorState) is from Vendor table. How do you relate the Vendor table to InvoiceLineItems table ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 01:00:13

what you're effectively doing now in your second query is taking cross join between tables which doesnt make much sense at all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

II Traveler II
Starting Member

11 Posts

Posted - 2011-11-10 : 11:44:57

Thanks so very much khtan for the first query!!! I didn't really think it was so easy ha.

Sorry I forgot to include the other table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 13:25:49

quote:
Originally posted by II Traveler II

Thanks so very much khtan for the first query!!! I didn't really think it was so easy ha.

Sorry I forgot to include the other table . Here it is:
________________
Vendors table

VendorID |
VendorName |
VendorAdress1 |
VendorAdress2 |
VendorCity |
VendorState |
VendorZipCode |
VendorPhone |__
VendorContactLName |
VendorContactFName |
DefaultTermsID |
DefaultAccountNo |
___________________|

Vendors table was already given. I think what you missed to give was table which relates Vendors to Invoicelines

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

II Traveler II
Starting Member

11 Posts

Posted - 2011-11-10 : 16:51:06

II Traveler II
Starting Member

11 Posts

Posted - 2011-11-10 : 22:01:32

So I'm now down to this code. It returns 505 rows though; not nearly the 65 rows I'm looking for. Thoughts?

SELECT SUM(InvoiceLineItemAmount) AS LineItemSum, AccountDescription AS Account,
CASE
	WHEN GROUPING(VendorState) = 1 THEN '*ALL*'
	ELSE VendorState
END AS 'State'
FROM Vendors, InvoiceLineItems, GLAccounts, Invoices
WHERE Vendors.VendorID = Invoices.VendorID and Invoices.InvoiceID = InvoiceLineItems.InvoiceID and GLAccounts.AccountNo = InvoiceLineItems.AccountNo
GROUP BY VendorState, AccountDescription, InvoiceLineItemAmount WITH CUBE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 03:26:56

how many unique combinations of VendorState, AccountDescription, InvoiceLineItemAmount you've on table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chanwmcolin
Starting Member

1 Post

Posted - 2012-11-01 : 16:04:40

quote:

Originally posted by II Traveler II

So I'm now down to this code. It returns 505 rows though; not nearly the 65 rows I'm looking for. Thoughts?

SELECT SUM(InvoiceLineItemAmount) AS LineItemSum, AccountDescription AS Account,
CASE
	WHEN GROUPING(VendorState) = 1 THEN '*ALL*'
	ELSE VendorState
END AS 'State'
FROM Vendors, InvoiceLineItems, GLAccounts, Invoices
WHERE Vendors.VendorID = Invoices.VendorID and Invoices.InvoiceID = InvoiceLineItems.InvoiceID and GLAccounts.AccountNo = InvoiceLineItems.AccountNo
GROUP BY VendorState, AccountDescription, InvoiceLineItemAmount  WITH CUBE

This might be a late response but I think i may have gotten better results by removing the following (see in red). I also added another CASE to account for AccountDescription (which is in my query in red). This gave me 65 rows.

SELECT SUM(InvoiceLineItemAmount) AS LineItemSum
, CASE
WHEN GROUPING(VendorState) = 1 THEN '*ALL*'
ELSE VendorState END State
, CASE
WHEN GROUPING(AccountDescription) = 1 THEN '*ALL*'
ELSE AccountDescription END Account
FROM Vendors
, InvoiceLineItems
, GLAccounts
, Invoices
WHERE Vendors.VendorID = Invoices.VendorID
AND Invoices.InvoiceID = InvoiceLineItems.InvoiceID
AND GLAccounts.AccountNo = InvoiceLineItems.AccountNo
GROUP BY AccountDescription, VendorState WITH CUBE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources