polynominal
asked on
Adding a Date to Daily run query
I have developed a query below, which does a count by company.
SELECT company.companyname, COUNT(job.jobid) AS COUNT
FROM job LEFT OUTER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
company ON allocation.companyid = company.companyid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.
WHERE (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (job.contractid = 7 OR
job.contractid = 14) AND (complete.jobid IS NULL)
GROUP BY company.companyname
The result is
Company Count
a 5
b 6
c 10
d 11
I want to add a colum to it which gives it todays date and retain the values for today
Company Count Date
a 5 15/05/2005
b 6 15/05/2005
c 10 15/05/2005
d 11 15/05/2005
I want to retain the values for today and then when I run it tommorrow, I wil get toadys values and tomorrow
Company Count Date
a 5 15/05/2005
b 6 15/05/2005
c 10 15/05/2005
d 11 15/05/2005
a 6 16/05/2005
b 14 16/05/2005
c 18 16/05/2005
d 5 16/05/2005
How could I do this, would it be a table or can I write a stored procedure to do this
Thanks Poly
SELECT company.companyname, COUNT(job.jobid) AS COUNT
FROM job LEFT OUTER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
company ON allocation.companyid = company.companyid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.
WHERE (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (job.contractid = 7 OR
job.contractid = 14) AND (complete.jobid IS NULL)
GROUP BY company.companyname
The result is
Company Count
a 5
b 6
c 10
d 11
I want to add a colum to it which gives it todays date and retain the values for today
Company Count Date
a 5 15/05/2005
b 6 15/05/2005
c 10 15/05/2005
d 11 15/05/2005
I want to retain the values for today and then when I run it tommorrow, I wil get toadys values and tomorrow
Company Count Date
a 5 15/05/2005
b 6 15/05/2005
c 10 15/05/2005
d 11 15/05/2005
a 6 16/05/2005
b 14 16/05/2005
c 18 16/05/2005
d 5 16/05/2005
How could I do this, would it be a table or can I write a stored procedure to do this
Thanks Poly
if i understant this corectly you do not ha date column in your source tables, if this is the case, you will have to insert the records into a new table
ASKER
Yes there is no date to compare against
How would I create a new table and put the above in it?
How would I create a new table and put the above in it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
U can create a new table with the fields required namely companyName, Countjobid, date.
Create a trigger which runs at the end of the day. something like this:
INSERT INTO NEWTABLE
SELECT company.companyname, COUNT(job.jobid) AS COUNT, GETDATE()
FROM job LEFT OUTER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
company ON allocation.companyid = company.companyid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.
WHERE (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (job.contractid = 7 OR
job.contractid = 14) AND (complete.jobid IS NULL)
GROUP BY company.companyname
Now you can use the newly created table for your queries rather than depending on the main table.
I think it is better rather than creating a new field in the main table. But ofcoz it depnds on how the table design is and how much data it is holding.
Regards,
RKM
Create a trigger which runs at the end of the day. something like this:
INSERT INTO NEWTABLE
SELECT company.companyname, COUNT(job.jobid) AS COUNT, GETDATE()
FROM job LEFT OUTER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
company ON allocation.companyid = company.companyid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.
WHERE (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (job.contractid = 7 OR
job.contractid = 14) AND (complete.jobid IS NULL)
GROUP BY company.companyname
Now you can use the newly created table for your queries rather than depending on the main table.
I think it is better rather than creating a new field in the main table. But ofcoz it depnds on how the table design is and how much data it is holding.
Regards,
RKM
Sorry.. I meant job instead of trigger..
---Create a job which runs at the end of the day.---
Regards,
RKM
---Create a job which runs at the end of the day.---
Regards,
RKM
I have included getdate() in the query
SELECT company.companyname, COUNT(job.jobid) AS COUNT,getdate() as Date
FROM job LEFT OUTER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
jobcat ON job.jobcatid = jobcat.jobcatid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
company ON allocation.companyid = company.companyid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.
WHERE (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (job.contractid = 7 OR
job.contractid = 14) AND (complete.jobid IS NULL)
GROUP BY company.companyname