August 15, 2014 at 4:07 pm
Hello all
I really need some help with the following.
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
Basically what I have is 2 tables (payroll and history). In the payroll table I have the following items, date pay username timeon timeoff. and in the history table the columns are the months of the year.
When a user enters his timeon and timeoff details he also enters a value into the pay column(if there is one, 0 if none) along with the date. SO a typical row of the table will be:
Date username timeon timeoff pay
14/08/2014 xxxxxxx 08:00 09:00 80
etc...
When the user has submitted the details the script above reads the total pay and updates the history table with the total figure for the answer. (this is done in a separate script) the information from this script is collected by an array. One of the reason why I need help
Also our payroll runs from the 11th of the month to the 10th of the next month and that's what the above SQL code does. It looks for all pay items from the 11th to the 10th and sums as total. It also gives me the values for the previous month. The output view looks something like this:
PeriodStart total
2014-07-11 180 (Previous month)
2014-08-11 45 (This month)
Previous month being 11/07/2014 - 10/08/2014
This month being 11/08/2014 - 10/09/2014
What I need is for the above code to give me 4 separate variables ie. previous_month, ptotal, this_month, ttotal. The reason for this is a need to get away from collecting the information be an array.
Hope someone can help me.
If you need anymore info please let me know.
Thanks in advance.
Rickkap
August 15, 2014 at 9:58 pm
What I need is for the above code to give me 4 separate variables ie. previous_month, ptotal, this_month, ttotal. The reason for this is a need to get away from collecting the information be an array.
The "need" is not clear to me, and the quote does not define "ptotal" and "ttable". The first T-SQL example does not appear to be needed for the desired outcome. This T-SQL:create database test
go
use test
create table
outputview
(
PeriodStart date not null
, Total int
)
insert outputview select '2014-07-11', 180 union select '2014-08-11', 45
select * from outputview
gives the results:
PeriodStart Total
----------- -----------
2014-07-11 180
2014-08-11 45
which matches what I think is the start of your question.
I am guessing the following T-SQL:
SELECT STUFF((SELECT distinct ',' + CONVERT(VARCHAR(32),PeriodStart) + ',' + CONVERT(VARCHAR(32),Total)
FROM outputview c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
is a possible answer:
2014-07-11,180,2014-08-11,45
On the other hand, given the following assumption:alter table outputview add constraint pk primary key (PeriodStart)And assuming the intent of your first query (which has a syntax error at the last apostrophe) is to always return two rows (no more, no less), perhaps this:
declare
@previous_month date
,@ptotal int
,@this_month date
,@ttotal int
;
SELECT
@previous_month = PeriodStart
,@ptotal = Total
FROM
outputview
WHERE
PeriodStart = (SELECT MIN(PeriodStart) FROM outputview)
;
SELECT
@this_month = PeriodStart
,@ttotal = Total
FROM
outputview
WHERE
PeriodStart = (SELECT MAX(PeriodStart) FROM outputview)
;
-- Aliased SELECT of the 4 variables was added for clarity:
SELECT
@previous_month AS '@previous_month'
,@ptotal AS '@ptotal'
,@this_month AS '@this_month'
,@ttotal AS '@ttotal' which returns this:
@previous_month @ptotal @this_month @ttotal
--------------- ----------- ----------- -----------
2014-07-11 180 2014-08-11 45
is the answer being sought. But the T-SQL that generated this result might not return the expected result when the number of rows returned by your output view is not equal to 2.
You can edit and replace the string "outputview" (in my examples) with a "derived table" that is based upon your first T-SQL example. A derived table starts with an open parentheses, followed by a paste of your first T-SQL example (once its syntax error has been corrected), and ends with a close parentheses.
August 16, 2014 at 1:07 am
I forgot to mention that the 4 separate variables will be used in my classic ASP coding.
I've also had a thought overnight, if the above coding was spilt into 2 sql statements that would be fine.
August 16, 2014 at 1:18 am
If a batch of statements is acceptable, you can also wrap a CREATE PROCEDURE statement around my last example, defining 4 OUTPUT parameters. See example C at http://msdn.microsoft.com/en-us/library/ms187926.aspx. Doing so would require less code to be submitted by the client, and would allow the code to be changed at the server when/if needed (within the stored procedure), perhaps desirably without needing to alter client ASP code.
August 16, 2014 at 1:06 pm
Sorry I'm very new to SQL and I don't know how to do that.
If someone could split the above code I would appreciate it
August 16, 2014 at 4:33 pm
There are some things you need to know about the fundamentals of relational database design, before you go much further. Otherwise you risk creating databases that contain logical inconsistencies, or databases that unnecessarily consume additional disk space. The basic fundamentals you need to understand are the primary key, column relationships, foreign key constraints, and database normalization (to at least 3rd normal form, preferably beyond). There will be a bit of a learning curve, but it will quickly pay for itself, and make your experience with T-SQL much smoother.
As an example, you mentioned you have two tables named [payroll] and [history]. You mentioned you use a script to move the data from [payroll] to [history]. The first thing to note is that [payroll] is, itself, a 'history' of sorts. The second thing to note is that by moving records from [payroll] to [history], you are creating two locations that contain the same fundamental data. A future reader of those two tables may uncover a discrepancy, where the data within the history table could not possibly be derived from the payroll table. That future reader would be stuck with a logical inconsistency ("which is correct, [payroll] or [history]?"). While you may believe that risk is worth the convenience because you alone control access to the data, I am fairly certain most database designs outlive the designer. A third thing to note is that further designs may depend upon [history], which means that making changes to the design (or the correction of an logical inconsistency) very difficult to do, later on. This is a major reason why having a firm grasp on the fundamentals of relational database design is important, at the very outset of a project. The fourth thing to note is that moving data from [payroll] to [history] will require disk I/O, which is the most costly operation a database server must do. The fifth thing to note is that if there is a concern about performance (hence why the [history] table was created), it is important to understand that indexing is designed to decimate the I/O costs of reading. It would be prudent to grasp the fundamentals of indexing, before creating a [history] of history already within [payroll]. Perhaps an assurance of logical consistency is worth a slight performance penalty.
There are perhaps more elegant ways to write what follows. I am trying to show you a concept - I am not trying to optimize the code. Keep in mind that SQL is not a procedural language (it is not like BASIC, C, etc). SQL Server's query optimizer can be expected to choose how SQL Server should execute statements within the following batch.
CREATE PROCEDURE
GetHistory
(
@previous_month date OUTPUT
,@ptotal int OUTPUT
,@this_month date OUTPUT
,@ttotal int OUTPUT
)
AS
SELECT
@previous_month = PeriodStart
,@ptotal = Total
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyDerivedTable
WHERE
PeriodStart =
(
SELECT
MIN(PeriodStart)
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MySecondDerivedTable
)
;
SELECT
@this_month = PeriodStart
,@ttotal = Total
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyThridDerivedTable
WHERE
PeriodStart =
(
SELECT
MAX(PeriodStart)
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyFourthDerivedTable
)
GO
Notice the 4 aliases named %DerivedTable.
I cannot help you with ASP code and how it handles parameters, but submitting:
EXEC GetHistory
to SQL Server will cause SQL Server to return four parameters: @previous_month;@ptotal;@this_month;@ttotal
Notice that the above code does not need a [history] table. The code is based upon [payroll] alone. You may become concerned about performance (depending upon the number of rows within [payroll]). To address a performance problem, take the body of the sproc and add the declarations back, as so:
declare
@previous_month date
,@ptotal int
,@this_month date
,@ttotal int
;
SELECT
@previous_month = PeriodStart
,@ptotal = Total
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyDerivedTable
WHERE
PeriodStart =
(
SELECT
MIN(PeriodStart)
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MySecondDerivedTable
)
;
SELECT
@this_month = PeriodStart
,@ttotal = Total
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyThridDerivedTable
WHERE
PeriodStart =
(
SELECT
MAX(PeriodStart)
FROM
(
select
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
as PeriodStart, SUM(pay) as total
from
Payroll
where
[Date] between
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20001211') and
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10
,CURRENT_TIMESTAMP))
,'20010210') AND (email = 'xx@.xx.com')
group by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
) AS MyFourthDerivedTable
)
Save above code as GetHistory.sql. Open SQL Server's Database Tuning Advisor, and submit GetHistory.sql for analysis. I am fairly certain the DTA will suggest the creation of indexes upon [date] and . Create the DTA's suggested indexes and again consider performance. If there is currently a trivial number of rows within [payroll], the benefits of the indexes may not be observable. If you expect the number of rows within [payroll] to increase, keep the indexes in place (for later).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply