Query previous week with no set date?

Question

Post reply

Query previous week with no set date?

chef423

SSCertifiable

Points: 5279
More actions
September 29, 2014 at 4:00 pm

#290442

SELECT c.cust_fullname AS Customer,
c.cust_membership_id AS Account#,
t.c_amount AS ChargeTotal,
t.i_ticket_id AS Ticket#,
t.s_credit_tran_type AS AccountType
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,'17530101',getdate())/7)*7)-7,'17530101') -- Date greater than or equal to Monday of last week
AND t.[dt_when] < = dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7),'17530101') -- Date before Monday of this week
GROUP BY c.cust_fullname,
c.cust_membership_id,
t.c_amount,
t.i_ticket_id,
t.s_credit_tran_type
Looking to pull Sales from only 'House Accounts' for the previous work week, no matter what day of the following week I run this query...
I don't have an extensive data set to test but can the Experts comment on my code? Is this correct?
Thanks to all who can help.
Chris
P.S. How do I post my code in block form like I see in these forums?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could try the formula yourself. I wouldn't use a date that far away.

SELECT comment,

TestDate,

dateadd(dd,((datediff(dd,'17530101',TestDate)/7)*7)-7,'17530101'),

dateadd(dd,((datediff(dd,'17530101',TestDate)/7)*7),'17530101'),

--My version for this problem

DATEADD(WK, DATEDIFF(WK, 0, TestDate - 1) - 1, 0),

DATEADD(WK, DATEDIFF(WK, 0, TestDate - 1) - 1, 7)

FROM (VALUES(CAST( '20140928' as datetime), 'Out of Range'),

('20140929', ''),

('20140930', ''),

('20141001', ''),

('20141002', ''),

('20141003', ''),

('20141004', ''),

('20141005', ''),

('20141006', 'Out of Range'))x(TestDate, Comment)

To get the code block, you use the IFCode tags [ code="sql"][/code] (without the space)

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

chef423 SSCertifiable Points: 5279 More actions · Answer 2

Thanks for the help....How do you post your code like that? I am using Snipping Tool but it doesnt let me post it to the thread...

Chris

chef423 SSCertifiable Points: 5279 More actions · Answer 3

So the Weeks code is:

DATEADD(WK, DATEDIFF(WK, 0, GETDATE()- 1) - 1, 0),

DATEADD(WK, DATEDIFF(WK, 0, GETDATE()- 1) - 1, 7)

Let me test...thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Luis Cazares (9/29/2014)
To get the code block, you use the IFCode tags [ code="sql"][/code] (without the space)

Actually, this:

DATEADD(WK, DATEDIFF(WK, 0, TestDate - 1) - 1, 7)

Would be the same as this:

DATEADD(WK, DATEDIFF(WK, 0, TestDate - 1), 0)

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