Finding the last period''s dates
-
I need to send emails to staff letting them know their particular transactions for the previous month, can happily write something identifying the Active period ones, but need it to show the month(period) before. This will be an automatic email alert, so it needs no manual intervention once set up.
I have 3 tables; PERIOD, YEAR, TRANSACTIONS where YEAR.DIWOR = PERIOD.FINYEARDIWOR
YEAR shows the current year 2004-2006 as YEAR.Status = A, and all the others are C.
PERIOD basically has a row for each period in each year, with the current one being status = A. EG below
DIWOR FINYEARDIWOR PERIOD STATUS DATESTART DATEEND
1 1 1 C 1998-05-01 00:00:00.000 1998-05-31 23:59:59.000
2 1 2 C 1998-06-01 00:00:00.000 1998-06-30 23:59:59.000
3 1 3 C 1998-07-01 00:00:00.000 1998-07-31 23:59:59.000
4 1 4 C 1998-08-01 00:00:00.000 1998-08-31 23:59:59.000
5 1 5 C 1998-09-01 00:00:00.000 1998-09-30 23:59:59.000
6 1 6 C 1998-10-01 00:00:00.000 1998-10-31 23:59:59.000
7 1 7 C 1998-11-01 00:00:00.000 1998-11-30 23:59:59.000
8 1 8 C 1998-12-01 00:00:00.000 1998-12-31 23:59:59.000
9 1 9 C 1999-01-01 00:00:00.000 1999-01-31 23:59:59.000
10 1 10 C 1999-02-01 00:00:00.000 1999-02-28 23:59:59.000
11 1 11 C 1999-03-01 00:00:00.000 1999-03-31 23:59:59.000
12 1 12 O 1999-04-01 00:00:00.000 1999-04-30 23:59:59.000
13 2 1 C 1999-05-01 00:00:00.000 1999-05-31 23:59:59.000
14 2 2 C 1999-06-01 00:00:00.000 1999-06-30 23:59:59.000
15 2 3 C 1999-07-01 00:00:00.000 1999-07-31 23:59:59.000
Transaction has list of all transactions along with the date they were done. It contains a DateTime stamp, posted year and posted period.
I use the following query for identifying those in the Active Period, but need the one before.
select * from tr_time_txn txn, pm_period p
where txn.posted_yr = p.finyeardiwor
and txn.posted_prd = p.period
and p.status = 'A'
-
I would use the dateadd function with getdate:
select dateadd(m,-1,getdate())
will give you last month..
Also, if I were you I would write your query with the INNER JOIN clause, like this:
SELECT *
FROM tr_time_txn txn
INNER JOIN pm_period p
ON txn.posted_yr = p.finyeardiwor
AND txn.posted_prd = p.period
WHERE p.status = 'A'
Putting these 2 together, you'll have something like this:
SELECT *
FROM tr_time_txn txn
INNER JOIN pm_period p
ON txn.posted_yr = p.finyeardiwor
AND txn.posted_prd = p.period
WHERE dateadd(m,-1,getdate()) BETWEEN p.datestart and p.dateend
-- where last month is within the period
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply