Pivot Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot Table

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-10-22 : 14:30:04

I need to create a pivot table through a procedure code with a column called “Type” and in that column it has to have Weekly, Monthly, Yearly in it. I started with one, but it is not giving me exactly what I need. I don't need the Month and Year column.

Can some help me?

BEGIN
SET NOCOUNT ON --added to prevent extra result sets from

DECLARE @curYear int
DECLARE @curMonth int
DECLARE @i int
DECLARE @Year int
Delete From sumALLClaimsSettlementWMY

SET @curYear = YEAR(GetDate())
SET @curMonth = MONTH(GetDate())
SET @Year = @curYear - 1

SET @i = 1

---Weekly....

WHILE @i <= 12
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Weekly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Weekly', @Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))

--(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)

PRINT 'Record Inserted'
SET @i = @i + 1
END --END While Last Year...

SET @i = 1
SET @Year = @CurYear --Set year to this year for query below...
--DELETE Current year/Month record in order for this to be updated
--with the latest information data from IMPACT
DELETE FROM sumALLClaimsSettlementWMY
WHERE [year] = @curYear AND [Month] = @curMonth

WHILE @i <= @curMonth
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Weekly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Weekly', @Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))
--(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

-----------------------------Monthly...
SET @Year = @curYear - 1

SET @i = 1
WHILE @i <= 12
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Monthly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Monthly', @Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (MONTH(CLM_DOUT) = MONTH(GETDATE()))
--(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While Last Year...

SET @i = 1
SET @Year = @CurYear --Set year to this year for query below...
--DELETE Current year/Month record in order for this to be updated
--with the latest information data from IMPACT
--DELETE FROM sumALLClaimsSettlementWMY
--WHERE [year] = @curYear AND [Month] = @curMonth

WHILE @i <= @curMonth
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Monthly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Monthly', @Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (MONTH(CLM_DOUT) = MONTH(GETDATE()))
--(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

-------------------------------------Yearly....
SET @Year = @curYear - 1

SET @i = 1
WHILE @i <= 12
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Yearly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Yearly',@Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE()))--(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While Last Year...

SET @i = 1
SET @Year = @CurYear --Set year to this year for query below...
--DELETE Current year/Month record in order for this to be updated
--with the latest information data from IMPACT
--DELETE FROM sumALLClaimsSettlementWMY
--WHERE [year] = @curYear AND [Month] = @curMonth

WHILE @i <= @curMonth
BEGIN --BEGIN WHILE
PRINT ''
PRINT 'Year: ' + str(@year)
PRINT 'Month: ' + str(@i)
IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE [Year] = @Year AND [Month] = @i and [type] = 'Yearly') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type], [Year], [Month], clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Yearly', @Year, @i,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST(sum(clm_sppo) / sum(clm_tchg) * 100 AS decimal(4, 2)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST i ----IMPACT_Prod.dbo.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE()))
-- (YEAR(i.CLM_DOUT) = @Year) AND (MONTH(i.CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

END --END SP

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 14:43:03

If you are using sql 2005, there's a Pivot feature now...