(I moved this thread from datagrid area)
I have a sql database that has individual records consisting of name, date, hours worked among other fields.
Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date.
My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.
TABLE:
| dan | 12/13/2012 12:00:00 AM | 9.123 |
| dan | 12/14/2012 12:00:00 AM | 3.123123 |
| cara | 12/12/2012 12:00:00 AM | 4.222 |
| cara | 12/16/2012 12:00:00 AM | 3.3333 |
| cara | 12/17/2012 12:00:00 AM | 2 |
CODE:
Select distinct(name),
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Wednesday')as Wednesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Thursday')as Thursday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Saturday')as Saturday,
(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Total
from dbo.testtime YT
group by date, name, hours
RESULTS:
cara NULL NULL NULL 4.222 NULL NULL NULL 4.222
cara NULL 2 NULL NULL NULL NULL NULL 2
cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333
dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123
dan NULL NULL NULL NULL 9.123 NULL NULL 9.123
Like I said, I am SO close, I just need it to look like;
NAME SUN MIN TU WED TH FR SA TOTAL
cara 3.333 2 4.222 9.555
dan 9.125 3.125 12.5
TIA
dan
You need to create a table variable with columns of name, Sun-Sat columns to accumulate the data into. You can then select from that table the required summary.
|||
OK, sounds good.
Um, what is a table variable?
Is that like a view?
can you give me psudo-code to work with?
thanks MUCH!
Dan
|||
With table create script of SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TimeData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[Hours] [decimal](8, 6) NOT NULL,
CONSTRAINT [PK_TimeData] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Table insert script of
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'13/Dec/2012 12:00:00 AM'), 9.123) -- I am in the UK, hence the change of date format
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'14/Dec/2012 12:00:00 AM'), 3.123123)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'12/Dec/2012 12:00:00 AM'), 4.222)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'16/Dec/2012 12:00:00 AM'), 3.3333)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'17/Dec/2012 12:00:00 AM'), 2)
|||
The TSQL
DECLARE @.MyTableVar table(
[Name] VARCHAR(5) NOT NULL,
Sun [decimal](8, 6) NOT NULL DEFAULT ((0)),
Mon [decimal](8, 6) NOT NULL DEFAULT ((0)),
Tue [decimal](8, 6) NOT NULL DEFAULT ((0)),
Wed [decimal](8, 6) NOT NULL DEFAULT ((0)),
Thu [decimal](8, 6) NOT NULL DEFAULT ((0)),
Fri [decimal](8, 6) NOT NULL DEFAULT ((0)),
Sat [decimal](8, 6) NOT NULL DEFAULT ((0)),
Total [decimal](8, 6) NOT NULL DEFAULT ((0))
);
DECLARE @.NAME VARCHAR(50)
DECLARE @.DATE DATETIME
DECLARE @.HOURS decimal(8, 6)
DECLARE xCURSOR CURSOR FOR
SELECT Name, Date, Hours FROM TimeData
OPEN xCURSOR
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM @.MyTableVar WHERE [Name] = @.NAME)
INSERT INTO @.MyTableVar([Name]) VALUES (@.NAME)
IF datename(dw, @.DATE)='Sunday'
UPDATE @.MyTableVar SET Sun = Sun + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Monday'
UPDATE @.MyTableVar SET Mon = Mon + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Tuesday'
UPDATE @.MyTableVar SET Tue = Tue + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Wednesday'
UPDATE @.MyTableVar SET Wed = Wed + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Thursday'
UPDATE @.MyTableVar SET Thu = Thu + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Friday'
UPDATE @.MyTableVar SET Fri = Fri + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Saturday'
UPDATE @.MyTableVar SET Sat = Sat + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
END
SELECT * FROM @.MyTableVar
CLOSE xCURSOR
DEALLOCATE xCURSOR
gives
Name Sun Mon Tue Wed Thu Fri Sat Total
-- --- --- --- --- --- --- --- ---
dan 0.000000 0.000000 0.000000 0.000000 9.123000 3.123123 0.000000 12.246123
cara 3.333300 2.000000 0.000000 4.222000 0.000000 0.000000 0.000000 9.555300
Obviously a Cursor is not particularly efficient and needs to be eliminated. Also the Name column would need to be indexed (if possible) if there are more than 10 rows.
|||
TAT~
THat is awsome!
Thank you SO much for your efforts.
I ventured out on my own and came up with the following (I actually changed it to look at a test/prod table, so name is UserName)
But the code actually WORKEd!
Here it is, if youd care to comment:
--make var tqable
Declare @.tempweek TABLE
(UserName nvarchar(50), Sunday DECIMAL(8,6), Monday DECIMAL(8,6), Tuesday DECIMAL(8,6), Wednesday DECIMAL(8,6), Thursday DECIMAL(8,6), Friday DECIMAL(8,6), Saturday DECIMAL(8,6), Total DECIMAL(8,6))
--fill table
INSERT INTO @.tempweek
SELECT UserName,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Sunday')AS Sunday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Monday')AS Monday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Tuesday')AS Tuesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Wednesday')AS Wednesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Thursday')AS Thursday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Friday')AS Friday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Saturday')AS Saturday,
(SELECT SUM(HoursWorked)from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.HoursWorked = YT.HoursWorked) AS Total
from db_owner.PS_HR_Hrs YT
--select data
select UserName , sum(sunday)as Sunday, sum(monday) as Monday, sum(tuesday)as Tuesday, sum(wednesday)asWednesday, sum(thursday)as Thursday, sum(friday)as Friday, sum(saturday)as Saturday, sum(total) as Total
from @.tempweek
group by UserName
|||
Your solution will probably be faster as you do not use a CURSOR! Both solutions will be gluttons for memory for the few milliseconds they run, so as always, never stint on the RAM for a server hosting SQL Server.
