Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.lastallocation
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
0
polynominal
Asked:
polynominal
1 Solution
 
zupi5Commented:
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

0
 
polynominalAuthor Commented:
Yes there is no date to compare against

How would I create a new table and put the above in it?
0
 
zupi5Commented:
first you have to create the table using
CREATE TABLE [Jobs] (
      [Company] nchar(1) NOT NULL ,
      [Count] int NOT NULL ,
      [Date] datetime NOT NULL
) ON [PRIMARY]
GO

and then you do:

INSERT INTO Jobs (company, [count], [date]) (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.lastallocation
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)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
roshkmCommented:
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.lastallocation
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
0
 
roshkmCommented:
Sorry.. I meant job instead of trigger..

---Create a job which runs at the end of the day.---

Regards,
RKM
0
 
JulianvaCommented:

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.lastallocation
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now