How to mark a date stamp and also 2 fields as yes?

[dougancil]

I have the following query:

SELECT DISTINCT
[ScratchPad5].EmployeeNumber ,
SUM( case when [sumhours]>40
THEN 40
ELSE [sumhours]
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN [sumhours]-40
ELSE 0
END ) AS TotalOT

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber ,
sumhours

what I want to do is when this is ran, to create a timestamp for the field payrolldate and also mark a field payrollran as yes and to mark a field called approved as yes as well. Can someone please assist?

Thank you

[ck9663]

Are you planning to create a new column or update an existing column in the same or separate table? What would be the default value of your PayrollDate?

~~ CK

[dougancil]

This is an existing column in a table called Payroll. The default value of payrolldate is null, but I think I can just change that to [date] and that should put a timestamp there. This is working in conjuction with an app that when a user runs the app, it will timestamp the sql data as the queries are ran.

[ck9663]

It's actually a little more complicated than that. That PayrollDate should be populated, only if your payroll run is successful. For that to happen you should be able to handle errors during processing. You will only populate it, if everything runs as it should be.

Another factor is the Payroll Date should be based on the payroll run you are processing. You should consider if your payroll run is semi-monthly, every other week, monthly etc...

Good Luck!!!

~~ CK

[dougancil]

CK,

Those errors (making sure that the payroll is ran correctly) are handled in the application itself. The only thing that the server does is to run an SP based on information that I feed it. The payroll is being ran weekly. The problem being that there are two ways to do this, one requires some code re-working on the application side and the other by adding some values into the sql query. I'd rather go with altering the sql side. Because the app has a catch if the sql stored procedure fails, the rest of the code wouldn't be completed.

If I wanted to add these values with the sql query, how would I do that?

[ck9663]

Then just use Getdate() to default it to today's date. You can change it as necessary.

Good Luck!!!

~~ CK

[dougancil]

CK,

I'm not sure what you're saying in regards to Getdate. This program is ran once a week for payroll but is the query I have at the beginning checks to see
1. Next available Sunday (because our Payroll starts on Sunday) and
2. If Payroll ran is = 'no'

[ck9663]

Looks like your query is part of an application/system. I will be able to help you more if you give more detail. Like, your posted query is not a checked query. It's an aggregation query.

If you could give more specifics on the steps, etc. I will be able to help you more...

Good Luck!!!

~~ CK

[dougancil]

Ck,

Ok the first query I run in my app is this:

select MAX(payrolldate ) AS [payrolldate], dateadd(s, 518399, max(payrolldate )) AS [Sunday] from dbo.payroll" where payrollran = 'no'

then I run another query that is simply a query pulling data and posting it to a datagridview. Then on the next form I run 5 sql stored procedures that have all been tested and work. The last query is the one that you're seeing.

What the application is doing is running a payroll for a week. It checks to see the last time the payroll was ran, presents the next available Sunday (+7 days) for the user to start running the payroll.The last step I want the program to do is to post the above data into a table called Payroll. So there will be an 'insert into' statement that preceeds that query. What I'd like then is for the stored procedure to mark the date of when the payroll is ran (with a timestamp) then to mark payrollran as yes and approved as yes.

I hope that makes more sense now.