December 6, 2011 at 8:02 am
I have a problem with modulo operator. I have records that meet the criteria x%2=0 but i dont get any results.
CREATE TABLE #workexperiece
(id int identity(1, 1),
userid int,
jobname VARCHAR(20) NULL,
timestartyear int,
timestartmonth int,
timeendyear int,
timeendmonth int)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (1, 'Manager', 2000, 1, 2001, 1)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (2, 'Pilot', 2004, 3, 2005, 6)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Clown', 2006, 8, 2008, 1)
SELECT * FROM #workexperiece
--Here's an example without modulo
SELECT DISTINCT userid, YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END)) as totaldays
FROM #workexperiece WHERE YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END))>60
--And an example with modulo
SELECT DISTINCT userid, YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END)) as totaldays
FROM #workexperiece WHERE YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END))>60
AND YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) - ((CASE timeendyear
WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) + (CASE timeendmonth
WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END))%2=0
DROP TABLE #workexperiece
I should get two records in the query with modulo, but i get none.
Any ideas?
Ty for your time.
December 6, 2011 at 8:24 am
You need to wrap the whole statement in your WHERE clause in some brackets and then take the modulo.
Try this..
SELECT DISTINCT userid, YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END)) as totaldays,
(YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END)))%2
AS premod
FROM #workexperiece WHERE YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END))>60
AND
((YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 365 ELSE timeendyear * 365 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE())*30 ELSE timeendmonth*30 END)))%2 ) = 0
Hope that helps!
Cheers,
Jim.
December 6, 2011 at 8:39 am
For your own sanity, or that of whoever has to maintain this monster, try this instead: -
SELECT id, dateFormula - (yearFormula+monthFormula) AS totaldays,
(dateFormula - (yearFormula+monthFormula)) % 2 AS premod
FROM (SELECT id,
CASE timeendyear WHEN 9999
THEN YEAR(GETDATE()) * 365
ELSE timeendyear * 365 END AS yearFormula,
CASE timeendmonth WHEN 9999
THEN MONTH(GETDATE()) * 30
ELSE timeendmonth * 30 END AS monthFormula,
b.dateFormula
FROM #workexperiece a
CROSS JOIN (SELECT YEAR(GETDATE()) * 365 + MONTH(GETDATE()) * 30 + DAY(GETDATE()) AS dateFormula) b) work
WHERE dateFormula - (yearFormula+monthFormula) > 60
AND (dateFormula - (yearFormula+monthFormula)) % 2 = 0
December 6, 2011 at 9:04 am
Cadavre (12/6/2011)
For your own sanity, or that of whoever has to maintain this monster, try this instead: -
I would go even further. For your own sanity, your whole data model needs to be changed.
* You're storing date/time data in non-date/time fields. Date/time data has specific characteristics that it's very difficult to replicate when you model them using non-date/time data types.
* Because you are using non-date/time fields, you can't use the existing datetime functions such as DateAdd() or DateDiff().
* Because you can't use the existing datetime functions, your formula is ignoring leap years.
* Because you're not using the built in functions, you're making logical errors. For instance, you're adding the "begin date" to the "end date". This number is completely meaningless since it depends on the reference date which is completely arbitrarily set as '0001-01-01' in your system.
* You're using 9999 to represent unknown/missing data. The NULL value was specifically designed to represent unknown/missing data. By using a non-NULL value to represent NULL, you've made it impossible to distinguish between records where the value is known to be that non-NULL value (however unlikely it is to occur) and records where the value is truly unknown/missing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2011 at 9:13 am
drew.allen (12/6/2011)
Cadavre (12/6/2011)
For your own sanity, or that of whoever has to maintain this monster, try this instead: -I would go even further. For your own sanity, your whole data model needs to be changed.
* You're storing date/time data in non-date/time fields. Date/time data has specific characteristics that it's very difficult to replicate when you model them using non-date/time data types.
* Because you are using non-date/time fields, you can't use the existing datetime functions such as DateAdd() or DateDiff().
* Because you can't use the existing datetime functions, your formula is ignoring leap years.
* Because you're not using the built in functions, you're making logical errors. For instance, you're adding the "begin date" to the "end date". This number is completely meaningless since it depends on the reference date which is completely arbitrarily set as '0001-01-01' in your system.
* You're using 9999 to represent unknown/missing data. The NULL value was specifically designed to represent unknown/missing data. By using a non-NULL value to represent NULL, you've made it impossible to distinguish between records where the value is known to be that non-NULL value (however unlikely it is to occur) and records where the value is truly unknown/missing.
Drew
In case it helps the OP to decide, I agree whole-heartedly with the above comment by drew.
You're borrowing trouble by not using date/time/datetime datatypes to store date/time data.
December 7, 2011 at 2:12 pm
First and foremost ty for the input, both solutions worked!
I understand that our dates structure is not optimal, unfortunatly it's already in use and its not possible to change it atm.
I've been trying to get specific data for two days now, and i dont know if its possible anymore. I though that modulo would solve it, and it almost did... but i got duplicates.
I have two tables, usersand workexperience.
What i need is
Get all users
that have been active during last 6 months
that only have jobs that are older then 6 months,
that are not marked as inactive,
that have number of days passed since most recent job %2= 0
Sound simple enough? The trick is our dates are int, and could have value 9999 that stands for ongoing job.
The code below is for creating the tables with the data
--users
CREATE TABLE #users (id int identity(1, 1), firstname VARCHAR(20), lastlogin DATE, inactive int)
insert into #users(firstname, lastlogin, inactive) VALUES ('Peter', '20111201',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Robert', '20111202',1)
insert into #users(firstname, lastlogin, inactive) VALUES ('Johan', '20111203',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Noah', '20111204',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('David', '20001205',0)
--workexperience
CREATE TABLE #workexperiece
(id int identity(1, 1),userid int,jobname VARCHAR(20) NULL,timestartyear int,timestartmonth int,timeendyear int,timeendmonth int)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (1, 'Manager', 2011, 1, 9999, 9999)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (2, 'Pilot', 2011, 2, 2011, 5)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Clown', 2011, 3, 2011, 12)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Librarian', 2010, 5, 2010, 12)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Accountant', 2010, 6, 2010, 7)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (4, 'Farmer', 2006, 1, 2009, 7)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (5, 'Driver', 2007, 4, 2008, 5)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Chef', 2009, 2, 2009, 9)
The purpose is to send a reminder to our active users that have old jobs.
Because my lack of knowlege in TSQL, ive thought of splitting the query into three parts:
1. Get all users that have not worked for 6 months and are'nt inactive=1 into #temp1
2. Get the most recent job for each user in #temp1 and put the resaults in temp2
3. Get users from #temp2 that have days since last job %2=0
STEP 1
SELECT #users.id, (YEAR(GETDATE())*12+MONTH(GETDATE())) -
MAX(((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) ELSE timeendyear END)*12)+
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE()) ELSE timeendmonth END)) AS pmonth
INTO #temp1
FROM #users
INNER JOIN #workexperience ON #users.id=#workexperience.userid
WHERE inactive<>1 AND DATEDIFF(DAY,lastlogin,GETDATE())<180
GROUP BY #users.id
--Edited forgot the having part, to get all users that have older jobs then 5 months
HAVING ((YEAR(GETDATE())*12+MONTH(GETDATE())) -
MAX(((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) ELSE timeendyear END)*12)+
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE()) ELSE timeendmonth END))>5)
STEP(2) 3 (Jumping here directly coz i havent figured out step two yet)
SELECT DISTINCT userid, MAX(timeendyear*12+timeendmonth) AS workmax INTO #temp2 FROM #workexperience
INNER JOIN #temp1 ON #workexperience.userid=#temp1.id
WHERE
((YEAR(GETDATE())*365+MONTH(GETDATE())*30+DAY(GETDATE()) -
((CASE timeendyear WHEN 9999 THEN YEAR(GETDATE()) * 12 ELSE timeendyear * 12 END) +
(CASE timeendmonth WHEN 9999 THEN MONTH(GETDATE()) ELSE timeendmonth END)))%2) = 0
GROUP BY userid
SELECT * FROM #temp2
STEP(2) 3 seems to work but the problem is it checks against every job for every user in #temp1. It suppose to check only against the most recent job. But i cant figure out how to do it.
Hope it makes sense...
Btw, here's an attempt for STEP2
SELECT
t1.*
FROM
#workexperience t1
INNER JOIN (select
t2.userid AS userid,
MAX(t2.timeendyear*12+t2.timeendmonth) AS StatusDate
from
#workexperience t2
GROUP BY
t2.userid
) t3
ON (t1.userid = t3.userid
AND t1.timeendyear*12+t1.timeendmonth = t3.StatusDate)
ORDER BY
t1.userid
It works but i cant figure out how to check for 9999 in that sql. Also it's very abstract and i confess that i dont 100% understand that code...
Once again ty for your time.
December 7, 2011 at 5:24 pm
OK, this looks like as good time as any to ask about DateTime data. The advice to the OP was to use datetime datatypes for date data. What's bugging me is that Microsoft sometimes uses other data types to represent time. See the system table "sysjobhistory" (sorry I just realized that I'm looking at SQL 2005). Run_date and Run_time are integer data types. Can anyone tell me why?
December 8, 2011 at 6:44 am
Sorry, lost track of this thread.
It sounds like what you want to do should be possible in one query. Can you provide me with expected result from the sample data that you've posted please?
OK, I've had a bit of a guess 😛
;WITH workQuery AS (
SELECT a.id AS userid, a.firstname, a.lastlogin, a.inactive, b.jobname,
DATEADD(m, CASE WHEN timestartmonth = 9999
THEN NULL
ELSE timestartmonth END, CONVERT(DATETIME, CASE WHEN timestartyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timestartyear) END)) AS startDate,
DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END)) AS endDate,
MAX(DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END))) OVER (PARTITION BY a.id) AS lastEndDate
FROM #users a
INNER JOIN #workexperiece b ON a.id = b.userid)
SELECT userid, firstname, lastlogin, inactive, jobname, startDate, endDate
FROM workQuery
WHERE (inactive <> 1 AND lastEndDate <= DATEADD(m, -6, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)))
OR lastEndDate <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -2)
Entire code, including sample data below: -
BEGIN TRAN
--users
CREATE TABLE #users (id INT identity(1, 1), firstname VARCHAR(20), lastlogin DATE, inactive INT)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Peter', '20111201', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Robert', '20111202', 1)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Johan', '20111203', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Noah', '20111204', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('David', '20001205', 0)
--workexperience
CREATE TABLE #workexperiece (id INT identity(1, 1), userid INT, jobname VARCHAR(20) NULL, timestartyear INT, timestartmonth INT, timeendyear INT, timeendmonth INT)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (1, 'Manager', 2011, 1, 9999, 9999)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (2, 'Pilot', 2011, 2, 2011, 5)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Clown', 2011, 3, 2011, 12)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Librarian', 2010, 5, 2010, 12)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Accountant', 2010, 6, 2010, 7)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (4, 'Farmer', 2006, 1, 2009, 7)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (5, 'Driver', 2007, 4, 2008, 5)
INSERT INTO #workexperiece (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (6, 'Chef', 2009, 2, 2009, 9)
;WITH workQuery AS (
SELECT a.id AS userid, a.firstname, a.lastlogin, a.inactive, b.jobname,
DATEADD(m, CASE WHEN timestartmonth = 9999
THEN NULL
ELSE timestartmonth END, CONVERT(DATETIME, CASE WHEN timestartyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timestartyear) END)) AS startDate,
DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END)) AS endDate,
MAX(DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END))) OVER (PARTITION BY a.id) AS lastEndDate
FROM #users a
INNER JOIN #workexperiece b ON a.id = b.userid)
SELECT userid, firstname, lastlogin, inactive, jobname, startDate, endDate
FROM workQuery
WHERE (inactive <> 1 AND lastEndDate <= DATEADD(m, -6, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)))
OR lastEndDate <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -2)
ROLLBACK
It returns the following: -
userid firstname lastlogin inactive jobname startDate endDate
----------- -------------------- ---------- ----------- -------------------- ----------------------- -----------------------
2 Robert 2011-12-02 1 Pilot 2011-03-01 00:00:00.000 2011-06-01 00:00:00.000
4 Noah 2011-12-04 0 Farmer 2006-02-01 00:00:00.000 2009-08-01 00:00:00.000
5 David 2000-12-05 0 Driver 2007-05-01 00:00:00.000 2008-06-01 00:00:00.000
Am I on the right track?
December 8, 2011 at 10:36 am
Hi,
Given the criteria:
All users:
a: that have been active during last 6 months
b: that only have jobs that are older then 6 months,
c: that are not marked as inactive,
d: that have number of days passed since most recent job %2= 0
There is only one correct answer as far as I can see given the sample data and that is Noah.
Robert is inactive (c)
Peter has an ongoing job (b)
Johan has a job more recent that six months ago (b)
David hasn't been active in the last 6 months (a)
I've tried to comment the code as best I can as there are a few references to virtual tables created by joining the #users and #workexperience tables:
SELECT u.*, DATEDIFF(DD,lastJob, GETDATE()) DaysSinceLastJob
FROM
(SELECT id, MAX(JobEnd) AS lastJob
FROM
(SELECT u.*, we.id AS WE_Id, we.jobname,
--Here we're combining timestartyear and timestartmonth into a more useable DATETIME.
DATEADD(MONTH,((we.timestartyear-1900)*12)+we.timestartmonth-1,1-1) AS JobStart,
--Here we're converting timeendyear and timeendmonth into a DATETIME
--while creating a NULL if either of the values are 9999
CASE WHEN we.timeendyear = 9999 OR we.timeendmonth = 9999
THEN NULL
ELSE DATEADD(MONTH,((we.timeendyear-1900)*12)+we.timeendmonth-1,1-1)
END AS JobEnd
FROM #users u
INNER JOIN #workexperiece we ON we.userid = u.id
WHERE u.lastlogin >= DATEADD(MM,-6,GETDATE()) --Only users that have logged in in the last 6 months
AND u.inactive <> 1 --Only users that are active
) job
GROUP BY job.id --We need to group by the #users.id field to get the user's last job.
--VV Here we need to find all users have a last job more than 6 months ago.
HAVING MAX(JobEnd) < DATEADD(M,-6,GETDATE())
--VV Here we need to figure out the difference between their last job and today and make sure %2 = 0
AND DATEDIFF(DD,MAX(JobEnd), GETDATE()) % 2 = 0
) oldJobs
INNER JOIN #users u ON u.id = oldJobs.id --Now join back to the #users table to find the names of all the users that fit the criteria.
Basically at the start you have to convert the integers to datetimes, then get the users that have only been active in the last 6 months and are not flagged as inactive, this becomes a virtual table.
Then find the maximum end date of the last job for each user and make sure it is less than 6 months ago and % 2 = 0, this becomes another virtual table.
Then join to the #users table to find the list of users that adhere to all your criteria.
Are we getting warmer???
haha
Jim.
December 8, 2011 at 1:54 pm
Jim-720070 (12/8/2011)
Are we getting warmer???
Wow, yes, we certainly are!
There's a couple details left...
If Noah gets an ongoing job, he's still going to be drafted by the query. (Users having ongoing jobs and jobs that are older then 6months get selected, which should'nt happend)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (4, 'Farmer', 2006, 1, 2009, 7)
insert into #workexperiece(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (4, 'Fighter', 2009, 2, 9999, 9999)
Ty for commenting the query, it helps alot.
Im not so accustomed to "SELECT u.*", what is it called when you select data from a table that does'nt exist... (u table). Need to learn more about this, seems Very useful 🙂
Also is it possible to show the most recent jobname in the selected query?
Something like the result below:
"4 Noah Farmer 2011-12-04 0 1986"
So close.. i can taste it!
December 9, 2011 at 2:10 am
I fear there's been a certain amount of 'scope creep' in this request..
Also, this query will return different results each day. For instance, if nothing changes for the next week and you leave out the ongoing job for Noah, today 2011-12-09 won't return any results for the query, however tomorrow will. Can I ask what is the purpose of using the modulo operator?
Given the extra data you've added this query now doesn't return anything as Noah now has an ongoing job.
SELECT id, firstname, jobname, DaysSinceLastJob
FROM
(SELECT u.id, u.firstname, we.jobname, DATEDIFF(DD,lastJob, GETDATE()) DaysSinceLastJob,
--The below ROW_NUMBER function lets you get the most recent job
ROW_NUMBER() OVER(ORDER BY u.id,we.timeendyear DESC,we.timeendmonth DESC) AS RecentJob
FROM
(SELECT id, MAX(JobEnd) AS lastJob
FROM
(SELECT u.id, we.id AS WE_Id, we.jobname,
--Here we're combining timestartyear and timestartmonth into a more useable DATETIME.
DATEADD(MONTH,((we.timestartyear-1900)*12)+we.timestartmonth-1,1-1) AS JobStart,
--Here we're converting timeendyear and timeendmonth into a DATETIME
--while creating a NULL if either of the values are 9999
CASE WHEN we.timeendyear = 9999 OR we.timeendmonth = 9999
THEN NULL
ELSE DATEADD(MONTH,((we.timeendyear-1900)*12)+we.timeendmonth-1,1-1)
END AS JobEnd
FROM #users u
INNER JOIN #workexperiece we ON we.userid = u.id
WHERE u.lastlogin >= DATEADD(MM,-6,GETDATE()) --Only users that have logged in in the last 6 months
AND u.inactive <> 1 --Only users that are active
) job
GROUP BY job.id --We need to group by the #users.id field to get the user's last job.
--VV Here we need to find all users have a last job more than 6 months ago.
HAVING MAX(ISNULL(JobEnd,GETDATE())) < DATEADD(M,-6,GETDATE())
--VV Here we need to figure out the difference between their last job and today and make sure %2 = 0
AND DATEDIFF(DD,MAX(JobEnd), GETDATE()) % 2 = 0
) oldJobs
INNER JOIN #users u ON u.id = oldJobs.id --Now join back to the #users table to find the names of all the users that fit the criteria.
INNER JOIN #workexperiece we ON we.userid = u.id) FinalJobs
WHERE RecentJob = 1
December 9, 2011 at 4:20 am
Jim-720070 (12/9/2011)
I fear there's been a certain amount of 'scope creep' in this request..Can I ask what is the purpose of using the modulo operator?
Yes definitely scope creep problem, sorry for not providing efficient amount of data.
We're going to use this script to remind our active users to update thier jobs, by sending an email to them.
Modulo going to set a frequency rate for the reminder emails.
In this example we set it to 2, but in reality its going to be 60, to lower the frequency to 1 email every other month, (max 3 emails).
Your code worked great! But! We have a scope problem again i fear...
Problem is we get a single user result, which works in the example coz only 1 user match all the criteria.
If more users would match, still a single user would get selected.
If we would add a user (with same data as Noah):
insert into #users(firstname, lastlogin, inactive) VALUES ('Peter', '20111201',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Robert', '20111202',1)
insert into #users(firstname, lastlogin, inactive) VALUES ('Johan', '20111203',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Noah', '20111204',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('David', '20001205',0)
--This one below
insert into #users(firstname, lastlogin, inactive) VALUES ('Evil Twin', '20111204',0)
And a job (same date as Noah)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (1, 'Manager', 2011, 1, 9999, 9999)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (2, 'Pilot', 2011, 2, 2011, 5)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Clown', 2011, 3, 2011, 11)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Librarian', 2010, 5, 2010, 12)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Accountant', 2010, 6, 2010, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (4, 'Farmer', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (5, 'Driver', 2007, 4, 2008, 5)
--This one below
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Emperor', 2006, 1, 2009, 7)
We would still only get Noah, but not the Evil Twin.
December 9, 2011 at 9:58 am
Hmmm... I have to disagree with you, as I ran the query after adding in your new user and got the below results:
id firstname jobname DaysSinceLastJob
----------- -------------------- -------------------- ----------------
4 Noah Farmer 891
6 Evil Twin Emperor 891
This is the expected result set is it not?
I had to omit the modulo however as it was keeping both results out. Also, can't you just set the report to run once a fortnight instead of having to mess round with modulo functions??
December 12, 2011 at 1:04 am
Jim-720070 (12/9/2011)
Hmmm... I have to disagree with you...
Oh, i just run the code without removing the "WHERE RecentJob = 1" part.
The code works exellent and has alot better execution time then the one i managed to write (which is spit into 4 different queries).
However i noticed a problem again...
If a user have several jobs, the query returns all those jobs.
Example: Evil Twin has following work experiences.
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Emperor', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Conqueror', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Diver', 2003, 1, 2004, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Captain', 2002, 1, 2005, 7)
The query returns
id firstname jobname DaysSinceLastJob
----------- -------------------- -------------------- ----------------
4 Noah Farmer 894
6 Evil Twin Emperor 894
6 Evil Twin Conqueror 894
6 Evil Twin Diver 894
6 Evil Twin Captain 894
Here's the complete code:
BEGIN TRAN
CREATE TABLE #workexperience(id int identity(1, 1),userid int,jobname VARCHAR(20) NULL,timestartyear int,timestartmonth int,timeendyear int,timeendmonth int)
CREATE TABLE #users (id int identity(1, 1), firstname VARCHAR(20), lastlogin DATE, inactive int)
insert into #users(firstname, lastlogin, inactive) VALUES ('Peter', '20111201',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Robert', '20111202',1)
insert into #users(firstname, lastlogin, inactive) VALUES ('Johan', '20111203',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Noah', '20111204',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('David', '20001205',0)
insert into #users(firstname, lastlogin, inactive) VALUES ('Evil Twin', '20111204',0)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (1, 'Manager', 2011, 1, 9999, 9999)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (2, 'Pilot', 2011, 2, 2011, 5)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Clown', 2011, 3, 2011, 11)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Librarian', 2010, 5, 2010, 12)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (3, 'Accountant', 2010, 6, 2010, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (4, 'Farmer', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (5, 'Driver', 2007, 4, 2008, 5)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Emperor', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Conqueror', 2006, 1, 2009, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Diver', 2003, 1, 2004, 7)
insert into #workexperience(userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth)VALUES (6, 'Captain', 2002, 1, 2005, 7)
SELECT id, firstname, jobname, DaysSinceLastJob
FROM
(SELECT u.id, u.firstname, we.jobname, DATEDIFF(DD,lastJob, GETDATE()) DaysSinceLastJob,
--The below ROW_NUMBER function lets you get the most recent job
ROW_NUMBER() OVER(ORDER BY u.id,we.timeendyear DESC,we.timeendmonth DESC) AS RecentJob
FROM
(SELECT id, MAX(JobEnd) AS lastJob
FROM
(SELECT u.id, we.id AS WE_Id, we.jobname,
--Here we're combining timestartyear and timestartmonth into a more useable DATETIME.
DATEADD(MONTH,((we.timestartyear-1900)*12)+we.timestartmonth-1,1-1) AS JobStart,
--Here we're converting timeendyear and timeendmonth into a DATETIME
--while creating a NULL if either of the values are 9999
CASE WHEN we.timeendyear = 9999 OR we.timeendmonth = 9999
THEN NULL
ELSE DATEADD(MONTH,((we.timeendyear-1900)*12)+we.timeendmonth-1,1-1)
END AS JobEnd
FROM #users u
INNER JOIN #workexperience we ON we.userid = u.id
WHERE u.lastlogin >= DATEADD(MM,-6,GETDATE()) --Only users that have logged in in the last 6 months
AND u.inactive <> 1 --Only users that are active
) job
GROUP BY job.id --We need to group by the #users.id field to get the user's last job.
--VV Here we need to find all users have a last job more than 6 months ago.
HAVING MAX(ISNULL(JobEnd,GETDATE())) < DATEADD(M,-6,GETDATE())
--VV Here we need to figure out the difference between their last job and today and make sure %2 = 0
AND DATEDIFF(DD,MAX(JobEnd), GETDATE()) % 2 = 0
) oldJobs
INNER JOIN #users u ON u.id = oldJobs.id --Now join back to the #users table to find the names of all the users that fit the criteria.
INNER JOIN #workexperience we ON we.userid = u.id) FinalJobs
ROLLBACK
I feel bad asking for more help, but if you got time, it would help me out alot 🙂
December 12, 2011 at 2:30 am
memymasta (12/12/2011)
Jim-720070 (12/9/2011)
Hmmm... I have to disagree with you...Oh, i just run the code without removing the "WHERE RecentJob = 1" part.
The code works exellent and has alot better execution time then the one i managed to write (which is spit into 4 different queries).
However i noticed a problem again...
If a user have several jobs, the query returns all those jobs.
I feel bad asking for more help, but if you got time, it would help me out alot 🙂
You haven't said which one of the jobs you want to return, so I've coded it to return the "MAX" jobname.
SELECT userid, firstname, lastEndDate AS lastJob, DATEDIFF(dd, lastEndDate, GETDATE()) AS daysSinceLastJob,
MAX(jobname) AS jobname
FROM (SELECT a.id AS userid, a.firstname, a.lastlogin, a.inactive, b.jobname,
DATEADD(m, CASE WHEN timestartmonth = 9999
THEN NULL
ELSE timestartmonth-1 END, CONVERT(DATETIME, CASE WHEN timestartyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timestartyear) END)) AS startDate,
DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth-1 END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END)) AS endDate,
MAX(DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth-1 END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END))) OVER (PARTITION BY a.id) AS lastEndDate
FROM #users a
INNER JOIN #workexperience b ON a.id = b.userid
WHERE a.inactive <> 1 AND lastlogin >= DATEADD(mm, - 6, GETDATE())) workQuery
WHERE lastEndDate = endDate
AND DATEDIFF(DD, endDate, GETDATE()) % 2 = 0
GROUP BY userid, firstname, lastEndDate, DATEDIFF(dd, lastEndDate, GETDATE())
Be aware, this code is different to Jim's because I think he was hitting the tables more than he needed to.
Below is a comparison of our versions: -
BEGIN TRAN
SET NOCOUNT ON
CREATE TABLE #workexperience (id INT identity(1, 1), userid INT, jobname VARCHAR(20) NULL, timestartyear INT, timestartmonth INT, timeendyear INT, timeendmonth INT)
CREATE TABLE #users (id INT identity(1, 1), firstname VARCHAR(20), lastlogin DATE, inactive INT)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Peter', '20111201', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Robert', '20111202', 1)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Johan', '20111203', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Noah', '20111204', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('David', '20001205', 0)
INSERT INTO #users (firstname, lastlogin, inactive) VALUES ('Evil Twin', '20111204', 0)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (1, 'Manager', 2011, 1, 9999, 9999)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (2, 'Pilot', 2011, 2, 2011, 5)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Clown', 2011, 3, 2011, 11)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Librarian', 2010, 5, 2010, 12)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (3, 'Accountant', 2010, 6, 2010, 7)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (4, 'Farmer', 2006, 1, 2009, 7)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (5, 'Driver', 2007, 4, 2008, 5)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (6, 'Emperor', 2006, 1, 2009, 7)
INSERT INTO #workexperience (userid, jobname, timestartyear, timestartmonth, timeendyear, timeendmonth) VALUES (6, 'Conqueror', 2006, 1, 2009, 7)
PRINT '========== Jim-720070 version =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT id, firstname, jobname, DaysSinceLastJob, lastJob
FROM (SELECT u.id, u.firstname, we.jobname, DATEDIFF(DD, lastJob, GETDATE()) DaysSinceLastJob, --The below ROW_NUMBER function lets you get the most recent job
ROW_NUMBER() OVER (ORDER BY u.id, we.timeendyear DESC, we.timeendmonth DESC) AS RecentJob, lastJob
FROM (SELECT id, MAX(JobEnd) AS lastJob
FROM (SELECT u.id, we.id AS WE_Id, we.jobname, --Here we're combining timestartyear and timestartmonth into a more useable DATETIME.
DATEADD(MONTH, ((we.timestartyear - 1900) * 12) + we.timestartmonth - 1, 1 - 1) AS JobStart, --Here we're converting timeendyear and timeendmonth into a DATETIME
--while creating a NULL if either of the values are 9999
CASE WHEN we.timeendyear = 9999 OR we.timeendmonth = 9999
THEN NULL
ELSE DATEADD(MONTH, ((we.timeendyear - 1900) * 12) + we.timeendmonth - 1, 1 - 1) END AS JobEnd
FROM #users u
INNER JOIN #workexperience we ON we.userid = u.id
WHERE u.lastlogin >= DATEADD(MM, - 6, GETDATE()) --Only users that have logged in in the last 6 months
AND u.inactive <> 1 --Only users that are active
) job
GROUP BY job.id --We need to group by the #users.id field to get the user's last job.
--VV Here we need to find all users have a last job more than 6 months ago.
HAVING MAX(ISNULL(JobEnd, GETDATE())) < DATEADD(M, - 6, GETDATE()) --VV Here we need to figure out the difference between their last job and today and make sure %2 = 0
AND DATEDIFF(DD, MAX(JobEnd), GETDATE()) % 2 = 0
) oldJobs
INNER JOIN #users u ON u.id = oldJobs.id --Now join back to the #users table to find the names of all the users that fit the criteria.
INNER JOIN #workexperience we ON we.userid = u.id
) FinalJobs
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Cadavre version =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT userid, firstname, lastEndDate AS lastJob, DATEDIFF(dd, lastEndDate, GETDATE()) AS daysSinceLastJob,
MAX(jobname) AS jobname
FROM (SELECT a.id AS userid, a.firstname, a.lastlogin, a.inactive, b.jobname,
DATEADD(m, CASE WHEN timestartmonth = 9999
THEN NULL
ELSE timestartmonth-1 END, CONVERT(DATETIME, CASE WHEN timestartyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timestartyear) END)) AS startDate,
DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth-1 END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END)) AS endDate,
MAX(DATEADD(m, CASE WHEN timeendmonth = 9999
THEN NULL
ELSE timeendmonth-1 END, CONVERT(DATETIME, CASE WHEN timeendyear = 9999
THEN NULL
ELSE CONVERT(VARCHAR(4),timeendyear) END))) OVER (PARTITION BY a.id) AS lastEndDate
FROM #users a
INNER JOIN #workexperience b ON a.id = b.userid
WHERE a.inactive <> 1 AND lastlogin >= DATEADD(mm, - 6, GETDATE())) workQuery
WHERE lastEndDate = endDate
AND DATEDIFF(DD, endDate, GETDATE()) % 2 = 0
GROUP BY userid, firstname, lastEndDate, DATEDIFF(dd, lastEndDate, GETDATE())
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
ROLLBACK
This returns
========== Jim-720070 version ==========
id firstname jobname DaysSinceLastJob lastJob
----------- -------------------- -------------------- ---------------- -----------------------
4 Noah Farmer 894 2009-07-01 00:00:00.000
6 Evil Twin Emperor 894 2009-07-01 00:00:00.000
6 Evil Twin Conqueror 894 2009-07-01 00:00:00.000
Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#workexperience_____________________________________________________________________________________________________0000000000E5'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#users______________________________________________________________________________________________________________0000000000E6'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
================================================================================
========== Cadavre version ==========
userid firstname lastJob daysSinceLastJob jobname
----------- -------------------- ----------------------- ---------------- --------------------
4 Noah 2009-07-01 00:00:00.000 894 Farmer
6 Evil Twin 2009-07-01 00:00:00.000 894 Emperor
Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Worktable'. Scan count 3, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#workexperience_____________________________________________________________________________________________________0000000000E5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#users______________________________________________________________________________________________________________0000000000E6'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
================================================================================
Ignore the results, as Jim can easily fix his code to return the same and instead check out the IO statistics.
========== Jim-720070 version ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#workexperience'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#users'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
================================================================================
========== Cadavre version ==========
Table 'Worktable'. Scan count 3, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#workexperience'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#users'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
================================================================================
I believe that moving the IO into the "Worktable" will produce faster results, but make sure you test with your own data.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply