May 6, 2015 at 2:58 am
I current have a query like this...
select *
from ( select case sacoy
when 1000 then 'PRIME'
WHEN 4000 THEN 'RECRUIT'
WHEN 6000 THEN 'PREMIER'
END AS Brand,
sapaywk as [Week],
dahahours as Statutory_Holiday_Hours
FROM
table 1
WHERE
sapaywk < 7
) as s
PIVOT
(
sum(s.Statutory_Holiday_Hours)
for S.[Week] IN ([01],[02],[03],[04],[05],[06])
) as [pivot];
and it gives me the following....(this calculates only for the one column sum(Statutory_Holiday_Hours)
Brand 01 02 03 04 05 06
Premier 29 34 45 89 76 65
Prime 78 65 54 67 34 90
Recruit 67 34 76 48 98 76
I need a output like this.....(for each brand)
total hours for Prime statutory hours for PRIME AWR holiday hours for Prime
01 65657 565656 565656
02 656565 656565 565656
03 6565665 656565 7878787
04 4543345 454354 554643
05 755465 83653 7436543
06 98754 324324 3242344
the other two columns are datohours(total hours) and daawrhours(AWR Hours)
Help will be appreciated
May 6, 2015 at 3:04 am
Can you please post table scripts, sample data and expected output in a readable format? See this post for guidance: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-- Gianluca Sartori
May 6, 2015 at 3:36 am
USE [CAS_Data]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CAST_Holiday_Accrual_hphaclog](
[sacoy] [char](4) NULL,
[sapaywk] [char](2) NULL,
[dahahours] [float] NULL,
[daawrhours] [float] NULL,
[datothours] [float] NULL,
[sa_empno] AS (case when charindex('.',[saempno],(0))>(0) then CONVERT([varchar](20),[saempno],0) when isnumeric([saempno])=(1) then CONVERT([varchar](20),CONVERT([int],[saempno],(0)),(0)) else CONVERT([varchar](20),[saempno],0) end) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CAS_Data]
GO
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','01','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','02','4.33','1.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','03','4.34','0.29')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','04','2.94','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','05','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','06','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('1000','01','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('4000','02','4.33','1.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('4000','03','4.34','0.29')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('4000','04','2.94','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('4000','05','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('4000','06','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','01','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','02','4.33','1.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','03','4.34','0.29')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','04','2.94','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','05','4.54','0.19')
INSERT INTO [dbo].[CAST_Holiday_Accrual_hphaclog]([sacoy], [sapaywk],[dahahours],[daawrhours],[datothours])
VALUES('6000','06','4.54','0.19')
GO
May 6, 2015 at 3:49 am
Errors:
Msg 207, Level 16, State 1, Line 31
Invalid column name 'saempno'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'saempno'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'saempno'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'saempno'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'saempno'.
more errors:
Msg 109, Level 15, State 1, Line 49
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Did you read the article I suggested? What's the expected output?
-- Gianluca Sartori
May 6, 2015 at 3:49 am
Could try:
SELECT
sapaywk as [Week]
,SUM(CASE WHEN sacoy = 1000 THEN daawrhours ELSE NULL END) AS [Total Hours for Prime]
,SUM(CASE WHEN sacoy = 1000 THEN dahahours ELSE NULL END) AS [Statutory Hours for Prime]
FROM YourTable
GROUP BY sapaywk
Etc.
This is assuming you are defining a brand by the value of column [Sacoy] though.
For example, let's say I have 2 brands called, 'Envy' and 'Lust'. Let's also say I want to find out the total revenue and sales per brand in the same fashion by day in a transaction based table.
SELECT
[Date]
,COUNT(CASE WHEN [Brand] = 'Envy' THEN 1 ELSE NULL END) AS [Envy Total Sales]
,SUM(CASE WHEN [Brand] = 'Envy' THEN [Revenue] ELSE NULL END) AS [Envy Total Revenue]
,COUNT(CASE WHEN [Brand] = 'Lust' THEN 1 ELSE NULL END) AS [Lust Total Sales]
,SUM(CASE WHEN [Brand] = 'Lust' THEN [Revenue] ELSE NULL END) AS [Lust Total Revenue]
FROM MyTable
GROUP BY
[Date]
ORDER BY
[Date]
May 6, 2015 at 4:53 am
sorry reason for the errors is cause i didnt add any value for the last column. You enter numeric number for that field.
suggested outcome is at the top, "output"
May 6, 2015 at 5:19 am
Try this:
select [Week],
SUM(CASE Brand WHEN 'PRIME' THEN AWR_holiday_hours END) AS [total hours for PRIME],
SUM(CASE Brand WHEN 'PRIME' THEN Statutory_Holiday_Hours END) AS [statutory hours for PRIME],
SUM(CASE Brand WHEN 'PRIME' THEN AWR_holiday_hours END) AS [AWR holiday hours for PRIME],
SUM(CASE Brand WHEN 'RECRUIT' THEN AWR_holiday_hours END) AS [total hours for RECRUIT],
SUM(CASE Brand WHEN 'RECRUIT' THEN Statutory_Holiday_Hours END) AS [statutory hours for RECRUIT],
SUM(CASE Brand WHEN 'RECRUIT' THEN AWR_holiday_hours END) AS [AWR holiday hours for RECRUIT],
SUM(CASE Brand WHEN 'PREMIER' THEN AWR_holiday_hours END) AS [total hours for PREMIER],
SUM(CASE Brand WHEN 'PREMIER' THEN Statutory_Holiday_Hours END) AS [statutory hours for PREMIER],
SUM(CASE Brand WHEN 'PREMIER' THEN AWR_holiday_hours END) AS [AWR holiday hours for PREMIER]
from (
select case sacoy
when 1000 then 'PRIME'
WHEN 4000 THEN 'RECRUIT'
WHEN 6000 THEN 'PREMIER'
END AS Brand,
sapaywk as [Week],
dahahours as Statutory_Holiday_Hours,
[daawrhours] AS AWR_holiday_hours,
[datothours] AS total_hours
FROM [CAST_Holiday_Accrual_hphaclog]
WHERE sapaywk < 7
) as s
GROUP BY [Week]
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply