Sql Help

[spidereddy]

Table_name:

date_key(YYYYDD),emp_num

 

 

 

I want avg of emp_num's in each year..from last 6 years

 

pls help...

 

will provide any info if needed

[spidereddy]

ila try chesa..

 

 

idhi boothu aithe solution chepadi

 

select * from (

select substr(date_key,1,4) as year,substr(date_key,5,6) as month,emp_num,count(1) as cnt

from table_name group by date_key,emp_num

)

pivot

(

avg(cnt)

for month in (01,02,03,04,05,06,07,08,09,10,11,12)

)

as pivot

[spidereddy]

soon pls

[spidereddy]

:(

[Spartan]

ltt..

[loveindia]

Use this man... no need of substring... Extra burden on the query...

 

CREATE TABLE MyEmp
(
datekey INT,
emp_num INT
)
GO
 
INSERT INTO MyEmp VALUES (201309, 50), (201310, 60), (201311, 70),(201312, 80)
, (201401, 10), (201402, 20), (201403, 30), (201404, 40), (201405, 50), (201406, 60), (201407, 70), (201408, 80)
 
; WITH MyCTE AS
(
SELECT LEFT(Datekey, 4) AS YYYY_CTE, SUM(emp_num) AS SUM_CTE
FROM MyEmp
GROUP BY LEFT(DateKey, 4)
)
SELECT pvt.* FROM (
SELECT LEFT(Datekey, 4) AS YYYY, RIGHT(DateKey, 2) AS MM, CAST((emp_num*1.0) / SUM_CTE AS DECIMAL(5,2)) AS AVG_EMP
FROM MyCTE a 
INNER JOIN MyEmp b ON a.YYYY_CTE = LEFT(b.Datekey, 4)
) AS a
PIVOT
(SUM(AVG_EMP) FOR MM IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS Pvt

[spidereddy]

 

Use this man... no need of substring... Extra burden on the query...

CREATE TABLE MyEmp
(
datekey INT,
emp_num INT
)
GO
 
INSERT INTO MyEmp VALUES (201309, 50), (201310, 60), (201311, 70),(201312, 80)
, (201401, 10), (201402, 20), (201403, 30), (201404, 40), (201405, 50), (201406, 60), (201407, 70), (201408, 80)
 
; WITH MyCTE AS
(
SELECT LEFT(Datekey, 4) AS YYYY_CTE, SUM(emp_num) AS SUM_CTE
FROM MyEmp
GROUP BY LEFT(DateKey, 4)
)
SELECT pvt.* FROM (
SELECT LEFT(Datekey, 4) AS YYYY, RIGHT(DateKey, 2) AS MM, CAST((emp_num*1.0) / SUM_CTE AS DECIMAL(5,2)) AS AVG_EMP
FROM MyCTE a 
INNER JOIN MyEmp b ON a.YYYY_CTE = LEFT(b.Datekey, 4)
) AS a
PIVOT
(SUM(AVG_EMP) FOR MM IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS Pvt

thanks bhayya..

 

 

kani konni doubts unnai...

MyEmp lo values evo insert chesav... ardam kaale..

 

and

 

CAST((emp_num*1.0) / SUM_CTE AS DECIMAL(5,2) kuda..

[spidereddy]

Use this man... no need of substring... Extra burden on the query...

CREATE TABLE MyEmp
(
datekey INT,
emp_num INT
)
GO
 
INSERT INTO MyEmp VALUES (201309, 50), (201310, 60), (201311, 70),(201312, 80)
, (201401, 10), (201402, 20), (201403, 30), (201404, 40), (201405, 50), (201406, 60), (201407, 70), (201408, 80)
 
; WITH MyCTE AS
(
SELECT LEFT(Datekey, 4) AS YYYY_CTE, SUM(emp_num) AS SUM_CTE
FROM MyEmp
GROUP BY LEFT(DateKey, 4)
)
SELECT pvt.* FROM (
SELECT LEFT(Datekey, 4) AS YYYY, RIGHT(DateKey, 2) AS MM, CAST((emp_num*1.0) / SUM_CTE AS DECIMAL(5,2)) AS AVG_EMP
FROM MyCTE a 
INNER JOIN MyEmp b ON a.YYYY_CTE = LEFT(b.Datekey, 4)
) AS a
PIVOT
(SUM(AVG_EMP) FOR MM IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS Pvt

Clarify chey bhayya pls

[loveindia]

Clarify chey bhayya pls

 

number of employees for that month ani teeskunna man as a value for that datekey... 

 

regarding that cast, integer / integer gives you only integer... It doesn't give you the decimal value. so you have to multiply the first integer with 1.0 which makes it decimal and it gives you a long decimal value out of which I am only taking the precision and scale for 5,2

 

I hope you got it.

 

Thanks

[SwamyRaRa]

number of employees for that month ani teeskunna man as a value for that datekey...

regarding that cast, integer / integer gives you only integer... It doesn't give you the decimal value. so you have to multiply the first integer with 1.0 which makes it decimal and it gives you a long decimal value out of which I am only taking the precision and scale for 5,2

I hope you got it.

Thanks

Great job...

[spidereddy]

number of employees for that month ani teeskunna man as a value for that datekey... 

 

regarding that cast, integer / integer gives you only integer... It doesn't give you the decimal value. so you have to multiply the first integer with 1.0 which makes it decimal and it gives you a long decimal value out of which I am only taking the precision and scale for 5,2

 

I hope you got it.

 

Thanks

Cool..thanks bhayya..