May 10, 2011 at 2:18 pm
I have the following query:
SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT
into scratchpad7
FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
order by employeenumber asc
[/code
and I need to include this as part of my query
select * from scratchpad3 where where code in ('Vacation','Holiday','ETO','Sicktime')
Here is the data from scratchpad7 that shows the current results
Employee# TotalRegHours TotalOt
8245 18.02 0
8247 12.99 0
8330 7.64 0
8389 18.67 0
8428 13.07 0
and my sample data from scratchpad3 looks like this:
Employee# Exceptiondate Starttime Endtime Code Duration
8244 1/4/2011 1/4/2011 9:00:00 AM1/4/2011 5:00:00 PMVacation 480
8245 1/2/2011 1/2/2011 1:00:00 PM1/2/2011 1:30:00 PMCoaching Session 30
The results I'm trying to achieve would look like this:
Employee# TotalRegHours TotalOt Vacation SickTime ETO Holiday
8245 18.02 0 0 0 0 0
here is my ddl for scratchpad3
CREATE TABLE [dbo].[SCRATCHPAD3]
( [EMPLOYEENUMBER] VARCHAR(50)NULL, [EXCEPTIONDATE]DATETIME NULL, [STARTTIME]DATETIME NULL, [ENDTIME] DATETIME VARCHAR(50)NULL, [DURATION] INT, NULL )
here is my ddl for scratchpad7.
CREATE TABLE [dbo].[SCRATCHPAD7] ( [EMPLOYEENUMBER] VARCHAR(50) NOT NULL, [TOTALREGHOURS] NUMERIC(38,2)NULL, [TOTALOT] NUMERIC(38,2) NULL)
May 11, 2011 at 1:47 pm
Edited - forgot it was a sql 2000 forum
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 6:34 pm
Check out "Cross-Tab Reports" topic in BOL.
Is it what do you need?
_____________
Code for TallyGenerator
May 12, 2011 at 7:35 am
Here's a dynamic version for SQL 2000 version
drop table #scratchpad7
drop table #scratchpad3
create table #scratchpad7 ( [EMPLOYEENUMBER] smallint,
TotalRegHours decimal(10,2),
TotalOt decimal(10,2))
insert into #scratchpad7
select 8245, 18.02, 3.50 UNION ALL
Select 8247, 12.99, 0 UNION ALL
Select 8330, 7.64, 0 UNION ALL
Select 8389, 18.67, 0 UNION ALL
Select 8428, 13.07, 0
create table #scratchpad3
( [EMPLOYEENUMBER] VARCHAR(50)NULL,
[EXCEPTIONDATE]DATETIME NULL,
[STARTTIME]DATETIME NULL,
[ENDTIME] DATETIME ,
VARCHAR(50) NULL,
[DURATION] INT NULL )
insert into #scratchpad3
select 8428,'1/4/2011','1/4/2011 9:00:00 AM','1/4/2011 5:00:00 PM','Vacation', 320 union all
select 8245,'1/2/2011','1/2/2011 1:00:00 PM','1/2/2011 1:30:00 PM','Coaching Session', 30 union all
select 8245,'1/3/2011','1/3/2011 9:00:00 AM','1/3/2011 5:00:00 PM','Vacation', 480 union all
select 8245,'1/3/2011','1/4/2011 10:00:00 AM','1/4/2011 4:00:00 PM','Holiday', 360 union all
select 8245,'1/3/2011','1/5/2011 8:00:00 AM','1/5/2011 3:00:00 PM','ETO', 420 union all
select 8245,'1/3/2011','1/6/2011 9:00:00 AM','1/6/2011 2:00:00 PM','SickTime', 300
--PIVOT
DECLARE @Colslist NVARCHAR(MAX)
DECLARE @Syntax Nvarchar(max)
Declare @rows int
declare @count int
DECLARE @Cols TABLE (ID int identity(1,1), Head NVARCHAR(MAX))
set @count = 1
INSERT @Cols (Head)
SELECT DISTINCT
FROM #scratchpad3
-- Get number of rows entered into @Cols Table
select @rows = @@ROWCOUNT
-- Don't sum up RegHours and OT since they are already summed up. If we sum them up,
-- then we will get those numbers multiplied by how many hours are logged against
-- Vacation, Holiday, etc. RegHours nad OT should probably be Separate Codes stored
-- the same way as Holiday, Vacation, etc.
set @syntax = 'SELECT a.EmployeeNumber, b.TotalRegHours, b.TotalOT,'
while @count <= @rows
begin
-- get next code from table
select @ColsList = Head
from @Cols
where ID = @count
-- add proper syntax to string
set @Syntax = @Syntax + 'Sum(case when Code =''' + @ColsList + ''' then Duration else 0 end) as [' + @ColsList + '], '
-- increment counter
set @count = @count + 1
end
-- strip off last comma
set @Syntax = LEFT(@syntax, Len(@syntax) - 1)
-- add from and where clause
set @Syntax = @Syntax + ' FROM #scratchpad3 a inner join #scratchpad7 b
on a.EMPLOYEENUMBER = b.EMPLOYEENUMBER
group by a.EmployeeNumber, b.TotalRegHours, b.TotalOT'
exec sp_ExecuteSQL @Statement = @Syntax
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2011 at 9:17 am
Ten,
Since I'm not using Dynamic here .. and this is part of an app, I'm not controlling what is being input into the query. (It will change every time)
This is what I have now:
SELECT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT
FROM ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
union
select employeenumber, null, null
from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')
order by employeenumber asc
and this is what I'd like to see as the end result
EmployeeNumber TotalRegHours TotalOT Vacation Holiday Sicktime ETO
8244 NULL NULL .00 .00 .00 .00
8247 12.99 .00 .00 .00 .00 .00
8330 7.64 .00 .00 .00 .00 .00
8389 18.67 .00 .00 .00 .00 .00
8433 9.74 .00 .00 .00 .00 .00
8442 17.91 .00 .00 .00 .00 .00
8451 3.72 .00 .00 .00 .00 .00
8455 4.72 .00 .00 .00 .00 .00
8467 37.48 .00 .00 .00 .00 .00
8471 40.00 8.68 .00 .00 .00 .00
8472 27.07 .00 .00 .00 .00 .00
8475 25.55 .00 .00 .00 .00 .00
8477 28.74 .00 .00 .00 .00 .00
8482 6.69 .00 .00 .00 .00 .00
May 26, 2011 at 10:24 am
Perhaps this is a better question,
Since I don't know what data is going to be populated into this result, what's the best way that I can achieve the results I'm looking for?
Thank you
Doug
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply