How can I run this query for the current days business?

Question

Post reply

How can I run this query for the current days business?

chef423

SSCertifiable

Points: 5030
More actions
September 21, 2014 at 4:41 pm

#290213

SELECT c.cust_fullname AS Customer, c.cust_membership_id AS Account#, SUM(t.c_amount) AS ChargeTotal
FROM Transactions AS t
INNER JOIN Customers AS c ON t.s_ref_num = c.cust_id
where s_credit_tran_type = 'House Account' and b_cancel = 0
GROUP BY c.cust_fullname, c.cust_membership_id
I need this simple query to run automatically every night at 11:30pm, everyday, but only for the Current Day's business. So embedding the time/date wont work here.
Any tips on how to achieve this?
My file will output to .CVS format for import into Quickbooks. I'd love to have it import directly INTO Quickbook's, but I am not at that level yet..
Thanks to anyone who is taking time to help.
Chris

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

Does your transaction table have a column that records when the transaction was made?

If so it should be fairly easy to limit the result set to just the current day.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Without knowing how to identify the Current Day's business, I just can help by leaving this article:

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

That way you don't need to hardcode the date.

To transfer directly to quickbooks, you might want to read the following: http://www.rssbus.com/kb/articles/ado-ssistask.rst

It's the opposite process, but might help. I'm not sure that it will be faster than generating the csv.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323506 More actions · Answer 3

assuming your Transactions table has a column named [TransactionDate], and that date represents the date you are looking for, this query will

get all transactions from midnight until the second it was run:

SELECT c.cust_fullname AS Customer,

c.cust_membership_id AS Account#,

Sum(t.c_amount) AS ChargeTotal

FROM Transactions AS t

INNER JOIN Customers AS c

ON t.s_ref_num = c.cust_id

WHERE s_credit_tran_type = 'House Account'

AND b_cancel = 0

AND t.[TransactionDate] > = Dateadd(dd, Datediff(dd, 0, Getdate()), 0) --midnight/early this morning.

GROUP BY c.cust_fullname,

c.cust_membership_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chef423 SSCertifiable Points: 5030 More actions · Answer 4

Well the CIO left me this code from their software for the date/time..

select dbo.dwf_beginofday_for_day(getdate())

select dbo.dwf_endofday_for_day(getdate())

But where do I embed?

Thanks for the replies folks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I am curious about what you need your data to look like when you run your query. Can you provide a sample of what the output needs to look like? Otherwise it just seems like a lot of guessing to be done.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

chef423 SSCertifiable Points: 5030 More actions · Answer 6

Customer Account# ChargeTotal

Warehouse, Men's 100054 91.57

Then I need to push that data into Quickbooks...

This code worked for today only, as it should...

SELECT c.cust_fullname AS Customer,

c.cust_membership_id AS Account#,

Sum(t.c_amount) AS ChargeTotal

FROM Transactions AS t

INNER JOIN Customers AS c

ON t.s_ref_num = c.cust_id

WHERE s_credit_tran_type = 'House Account'

AND b_cancel = 0

AND t.[dt_when] > = Dateadd(dd, Datediff(dd, 0, Getdate()), 0) --midnight/early this morning.

GROUP BY c.cust_fullname,

c.cust_membership_id

BUT I feel there is an easier way to do this cause the customer wants this fully automated. They want to wake up every morning have have the previous days House Accounts pushed into QB.

I hope I am going about this correctly, I dont want to lose this long standing client.

Again, thanks to all helping...

chef423 SSCertifiable Points: 5030 More actions · Answer 7

I'm actually shocked there is only Google info on how to push data FROM Quickbook's into SQL but not the other way around.

Doesn't Intuit understand people use Point of Sale programs for their business and need to push that data INTO QB's???

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Building on what Lowell already did and the information you provided concerning the start and end of the business day, you can schedule a job to run your export at the end of the business day every day with a query such as this.