Help Needed in Group by
-
Hi, Below is the TestData,
With SalaryReport as (Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 2 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 3 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 4 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 5 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate
)
Pareamter to be passed to query:
Declare @ IDMainCompany int = 100, @Process_Date datetime = '2014-01-01'
Expected output:
Am very confused about how to use group by and bring the expected output as like above. any sample query please
-
KGJ-Dev (8/23/2014)
Hi, Below is the TestData,With SalaryReport as (Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 2 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 3 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 4 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 5 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate
)
Pareamter to be passed to query:
Declare @ IDMainCompany int = 100, @Process_Date datetime = '2014-01-01'
Expected output:
Am very confused about how to use group by and bring the expected output as like above. any sample query please
This looks like something that should be done in the front end, SSRS or Excel; not in the SQL itself.
-
It can be done but I agree with Lynn, kind of awkward doing this in SQL.
😎
DECLARE @IDMainCompany int = 100;
DECLARE @Process_Date datetime = '2014-01-01';
;With SalaryReport as (
Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 1 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 1 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 1 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate union all
Select 1 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate
)
SELECT
CAST(SR.IdSalary AS VARCHAR(12)) AS IdSalary
,CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND SR.SalaryDate = @Process_Date
UNION ALL
SELECT
'' AS IdSalary
,'' AS IDMainCompany
,'' AS IDSubCompany
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'' AS SalaryDate
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND SR.SalaryDate = @Process_Date
Results
IdSalary IDMainCompany IDSubCompany Salary Incentive NoofEmployees SalaryDate------------ ------------- ------------ ------------------- ------------------- ------------------- ---------------
1 100 1000 200000 20000 500 2014-01-01
1 100 1001 300000 40000 600 2014-01-01
1 100 1002 400000 40000 1500 2014-01-01
Total: 900000 Total: 100000 Total: 2600
-
thank you lynn for your reply.
Hi Eric,
Wonderful and thanks for your help. I understand this is little awkward to think. But i have some reason to this in Database. A small mistake from my side that i haven't included another column which looks for the Average values. As my post subject "Help needed in Groupby" i should have mentioned about the average value. I apologize for that.
Here is the modified test data and the try i gave
DECLARE @IDMainCompany int = 100;DECLARE @Process_Date datetime = '2014-01-01';
;With SalaryReport as (
Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate ,70 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate ,45 as Creditscore union all
Select 1 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate,30 as Creditscore
)
SELECT
CAST(SR.IdSalary AS VARCHAR(12)) AS IdSalary
,CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST(avg(SR.Creditscore) AS VARCHAR(15)) AS average
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND SR.SalaryDate = @Process_Date
UNION ALL
SELECT
'' AS IdSalary
,'' AS IDMainCompany
,'' AS IDSubCompany
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'Total: ' + CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS AvgCreditscore
,'' AS SalaryDate
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
group by IDMainCompany, DATEPART(MM, SR.SalaryDate)
[Instead of passing the exact date, i need to get the data based on month. os that i don't need to rely on which date it is processed on the month. thats why i used DATEPART)
Any help please
-
Here is a quick attempt, please check the results;-)
😎
DECLARE @IDMainCompany int = 100;DECLARE @Process_Date datetime = '2014-01-01';
;With SalaryReport as (
Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate ,70 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate ,45 as Creditscore union all
Select 1 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate,30 as Creditscore
)
SELECT
CAST(SR.IdSalary AS VARCHAR(12)) AS IdSalary
,CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST(avg(SR.Creditscore) AS VARCHAR(15)) AS average
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND SR.SalaryDate = @Process_Date
GROUP BY SR.IdSalary
,SR.IDMainCompany
,SR.IDSubCompany
,SR.Salary
,SR.Incentive
,SR.NoofEmployees
,SR.SalaryDate
UNION ALL
SELECT
'' AS IdSalary
,'' AS IDMainCompany
,'' AS IDSubCompany
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'' AS SalaryDate
,'Total: ' + CAST(SUM(SR.Creditscore) AS VARCHAR(12)) AS AvgCreditscore
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
group by IDMainCompany, DATEPART(MM, SR.SalaryDate)
Results
IdSalary IDMainCompany IDSubCompany Salary Incentive NoofEmployees SalaryDate average------------ ------------- ------------ ------------------- ------------------- ------------------- --------------- -------------------
1 100 1000 200000 20000 500 2014-01-01 60
1 100 1001 300000 40000 600 2014-01-01 70
1 100 1002 400000 40000 1500 2014-01-01 45
Total: 900000 Total: 100000 Total: 2600 Total: 175
-
Hi Eric,
thank you and it worked perfect after i changed the sum to avg on the last statement. Quick question, If am not wrong that we don't need group by statement on the first block.
Because the Average, Sum we are calculating on the second block after union all. Here is my try which worked,
DECLARE @IDMainCompany int = 100;DECLARE @Process_Date datetime = '2014-01-01';
;With SalaryReport as (
Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate ,70 as Creditscore union all
Select 1 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate ,45 as Creditscore union all
Select 1 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate ,60 as Creditscore union all
Select 1 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate,30 as Creditscore
)
SELECT
CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST((SR.Creditscore) AS VARCHAR(15)) AS average
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
UNION ALL
SELECT
'' AS IDMainCompany
,'' AS IDSubCompany
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'' AS SalaryDate
,'Total: ' +CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS Totalaverage
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
group by IDMainCompany, DATEPART(MM, SalaryDate)
the mistake what i made is due to copy paste, i copied the average function on the credit core to the first block as well. that why it thrown error.
am i correct?
-
You are right, since the aggregation is on the outer/later query, no grouping is needed on the inner one.
😎
-
Thanks Eric for the confirmation and one more question,
the sample data i posted is having less columns. actually i have 20 columns in the select list. Also the parameters to the where condition i gave two i.e IDMainCompany, SalaryDate. I will also need to use IDSubCompany for some case. My skeleton will be
Create Procedure Test(@IDMainCompany int, @IDSubCompany int = null, @SalaryDate datetime)as
BEGIN
IF(@IDMainCompany is not null && @IDMainCompany > 0 && @SalaryDate is not null && @SalaryDate <> ' ' &&
@IDSubCompany is null)
BEGIN
--The logic what discussed on previous posts will falls here with @IDMainCompany, @SalaryDate as parameter to the where condition
END
IF(@IDMainCompany is not null && @IDMainCompany > 0 && @SalaryDate is not null && @SalaryDate <> ' ' &&
@IDSubCompany is null && @IDSubCompany > 0 )
BEGIN
--The logic what discussed on previous posts will falls here with @IDMainCompany, @SalaryDate,@IDSubCompany as parameter to the where condition
END
END
As said i need to include 20 columns in the select list and as union all used on the previous sample there are 20 columns on the first query and 20 columns on the second query after the union all. The SPC will become big. If i repeat this for second if condition, it will be too big. How can i reduce this writing more lines.
I am aware of the concept dynamic sql. Is there any other way to do this? or if dynamic sql is the only option, can you help me rewriting this select statements as dynamic dql
Please assume CTE as my main table so need to write that as Dynamic sql.
Could you please assist me
To reduce your time i removed the CTE and made it as sample table but with less columns
;create table SalaryReport(IdSalary int primary key identity(1,1),IDMainCompany int,IDSubCompany int,Salary money,Incentive int,NoofEmployees int, SalaryDate datetime, Creditscore int );
insert into SalaryReport (IDMainCompany,IDSubCompany,Salary,Incentive,NoofEmployees,SalaryDate,Creditscore)
( Select 100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate, 60 as Creditscore union all
Select 100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate , 70 as Creditscore union all
Select 100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate , 45 as Creditscore union all
Select 101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate, 60 as Creditscore union all
Select 102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate, 30 as Creditscore)
-
Good job with the data sample. I wouldn't be too concerned about the size of the SP code, the main concern is to get it working;-)
The following code toggles between two blocks of code depending on the presence of the @IDSubCompany value. It also demonstrates that the column names are not required in the latter part of the union, somewhat shortening the code.
😎
USE tempdb;GO
/*
;create table dbo.SalaryReport(IdSalary int primary key identity(1,1),
IDMainCompany int,IDSubCompany int,Salary money,Incentive int,NoofEmployees int, SalaryDate datetime, Creditscore int );
insert into dbo.SalaryReport (IDMainCompany,IDSubCompany,Salary,Incentive,NoofEmployees,SalaryDate,Creditscore)
( Select 100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2014-01-01' as SalaryDate, 60 as Creditscore union all
Select 100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
'2014-01-01' as SalaryDate , 70 as Creditscore union all
Select 100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
'2014-01-01' as SalaryDate , 45 as Creditscore union all
Select 101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
'2014-01-01' as SalaryDate, 60 as Creditscore union all
Select 102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
'2014-01-01' as SalaryDate, 30 as Creditscore
);
*/
DECLARE @IDSubCompany int = NULL;--1001;--
DECLARE @IDMainCompany int = 100;
DECLARE @SalaryDate datetime = '2014-01-01';
IF (@IDSubCompany IS NULL)
BEGIN
SELECT
CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST((SR.Creditscore) AS VARCHAR(15)) AS average
,'' AS COL_01
,'' AS COL_02
,'' AS COL_03
,'' AS COL_04
,'' AS COL_05
,'' AS COL_06
,'' AS COL_07
,'' AS COL_08
,'' AS COL_09
,'' AS COL_10
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @SalaryDate)
UNION ALL
SELECT
'',''
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'' AS SalaryDate
,'Total: ' +CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS Totalaverage
,'','','','','','','','','',''
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @SalaryDate)
GROUP BY IDMainCompany, DATEPART(MM, SalaryDate)
END
IF (@IDSubCompany IS NOT NULL)
BEGIN
SELECT
CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST((SR.Creditscore) AS VARCHAR(15)) AS average
,'' AS COL_01
,'' AS COL_02
,'' AS COL_03
,'' AS COL_04
,'' AS COL_05
,'' AS COL_06
,'' AS COL_07
,'' AS COL_08
,'' AS COL_09
,'' AS COL_10
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany AND SR.IDSubCompany = @IDSubCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @SalaryDate)
UNION ALL
SELECT
'' ,''
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,''
,'Total: ' +CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS Totalaverage
,'','','','','','','','','',''
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany AND SR.IDSubCompany = @IDSubCompany
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @SalaryDate)
GROUP BY IDMainCompany, DATEPART(MM, SalaryDate)
END
Edit: fixed missing parameter.
-
Hi Eric,
Thank you so much for your precious time on this post. I agree the proc should work first then considering about the structure
Here is the my try to minimize the proc size without dynamic sql, Please try giving @IdSubCompany = 1000 for to test another case. both are working fine
DECLARE @IDMainCompany int = 100;Declare @IdSubCompany int = null;
DECLARE @Process_Date datetime = '2014-01-01';
declare @Query nvarchar(max);
SELECT
CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
,CAST(SR.IDSubCompany AS VARCHAR(12)) AS IDSubCompany
,CAST(SR.Salary AS VARCHAR(12)) AS Salary
,CAST(SR.Incentive AS VARCHAR(12)) AS Incentive
,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
,CAST(SR.SalaryDate AS VARCHAR(15)) AS SalaryDate
,CAST((SR.Creditscore) AS VARCHAR(15)) AS average
FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND (SR.IDSubCompany = @IDSubCompany OR @IDSubCompany IS NULL OR @IDSubCompany <= 0)
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
UNION ALL
SELECT
'' AS IDMainCompany
,'' AS IDSubCompany
,'Total: ' + CAST(SUM(SR.Salary) AS VARCHAR(12)) AS Salary
,'Total: ' + CAST(SUM(SR.Incentive) AS VARCHAR(12)) AS Incentive
,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
,'' AS SalaryDate
,'Total: ' +CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS Totalaverage
FROM SalaryReport SR
WHERE
SR.IDMainCompany = @IDMainCompany
AND (SR.IDSubCompany = @IDSubCompany OR @IDSubCompany IS NULL OR @IDSubCompany <= 0)
group by IDMainCompany, DATEPART(MM, SalaryDate)
Do you feel any gap in this logic?
-
From my point of view there's a risk to get unwanted results from the previous year since there's only a comparison against the month value.
Speaking of it: applying a function to a column will prevent the usage of an index.
I'd recommed to change the WHERE clause to allow the usage of an index:
--using two additional variables:DECLARE @Process_Date_CurMonth datetime = DATEADD(MM,DATEDIFF(MM,0,@Process_Date),0);
DECLARE @Process_Date_NextMonth datetime = DATEADD(MM,1,@Process_Date_CurMonth);
--AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
AND SR.SalaryDate >= @Process_Date_CurMonth
AND SR.SalaryDate < @Process_Date_NextMonth
Furthermore, I recommend to use the ISO format for a date (YYYYMMDD) instead of YYYY-MM-DD since the second one might return unexpected results when there's a different setting for DATEFORMAT.
-
Hi Lutz,
Thanks for your reply and i couldn't imagine what issue it may cause? But I am more willing to use your suggestion, before that will the below condition work without issue?
AND DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)AND DATEPART(YY, SR.SalaryDate) = DATEPART(YY, @Process_Date)
Any suggestions please
-
I'm not sure what you referred to regarding " issue it may cause".
So let's talk about all aspects:
1) WHERE DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date) without a check for a matching year:
Add the following row and rerun the query with the above WHERE condition.
insert into SalaryReport (IDMainCompany,IDSubCompany,Salary,Incentive,NoofEmployees,SalaryDate,Creditscore)Select 100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
'2013-01-01' as SalaryDate, 60 as Creditscore
You might notice that the result has changed (one more row with the value from 2013 even though you're looking for a prcess date from 2014.
2) Applying a function to a column, e.g. DATEPART(MM, SR.SalaryDate)
When a function is applied, SQL Server will have to apply this function to all rows from the table (= a table scan) and cannot use any index that might be available and much more efficient.
3) using the ISO-format instead of YYYY-MM-DD
Try the following code:
SET DATEFORMAT YDMDECLARE @Process_Date datetime = '2014-01-05';
SELECT @Process_Date
SET DATEFORMAT YMD
DECLARE @Process_Date2 datetime = '2014-01-05';
SELECT @Process_Date2
As you can see, the date value will be different depending on the setting of DATEFORMAT.
Rerun this code and change 2014-01-05 to 20140105 and the result will be identical (Jan. 5th, 2014)
Edit: Yes, your query will prevent issue #1 from the list from above. But not the 2nd one.
-
Hi LutZ,
I agree and thanks for your details explanation with example. I will follow the standard as you suggested.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply