Pivot Table/Flat File - HELP! - 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/Flat File - HELP!

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-16 : 13:12:28

Ok,I revamped my query, I thought I fa different way.....

Select
RecordID,
Type, (Traditional, Teclar, AllClaims)
Year,
Month,
TotalCharges,
AdjustedTotalSavings,
AverageClaim,
PercentSavings,
DHSIncome
From sumClaimsSettlement

What I need the end result to look like...

Type Q1 Q2 Q3 Q4

Traditional
Teclar
All Claims

Under the Quarter cloumn will be Total Charges. What is the better way to do this? Should I go with a Pivot table or make it into a flat file?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 13:20:06

You can also do like this

SELECT Type,
SUM(CASE WHEN Month <=3 THEN TotalCharges ELSE 0 END) AS Q1,
SUM(CASE WHEN Month > 3 AND Month<=6 THEN TotalCharges ELSE 0 END) AS Q2,
SUM(CASE WHEN Month >6 AND Month<=9 THEN TotalCharges ELSE 0 END) AS Q3,
SUM(CASE WHEN Month >19 AND Month <=12 THEN TotalCharges ELSE 0 END) AS Q4
FROM sumClaimsSettlement
Group by Type

I'm assuming Month is as integer field having month number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 13:42:26

quote:
Originally posted by werhardt

THis is great.

Well, the way that I have it is coming from a procedure code See below after my query. Then I created a table

Select

RecordID,
Type,
Year,
Month,
TotalCharges,
AdjustedTotalSavings,
AverageClaim,
PercentSavings,
DHSIncome

From sumClaimsSettlement

I need to pull Qtr1,2,3,4 of 2007 and then Qrt 1,2,3,4 of 2008

Procedure code below

ALTER PROCEDURE [dbo].[p_ClaimsSettleMentDashBoard]
-- Add the parameters for the stored procedure here

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

DECLARE @curYear int
DECLARE @curMonth int
DECLARE @i int
DECLARE @Year int

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

SET @i = 1

--TRADITIONAL....

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

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Traditional', @Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND (Note = 'NG') AND
(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 sumClaimsSettlement
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 sumClaimsSettlement WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Traditional') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Traditional', @Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND (Note = 'NG') AND
(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

--ADVANCED...
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 sumClaimsSettlement WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Advanced') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Advanced', @Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND (Note = 'AF') AND
(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 sumClaimsSettlement
--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 sumClaimsSettlement WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Advanced') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Advanced', @Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND (Note = 'AF') AND
(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

--TOTAL....
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 sumClaimsSettlement WHERE [Year] = @Year AND [Month] = @i AND [type] = 'Total') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Total',@Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND
(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 sumClaimsSettlement
--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 sumClaimsSettlement WHERE [Year] = @Year AND [Month] = @i and [type] = 'Total') > 0
BEGIN --Begin IF Record already exists
PRINT 'Record Already Exists'
SET @i = @i + 1
CONTINUE
END --END IF

INSERT INTO sumClaimsSettlement ([type], [Year], [Month], TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT 'Total', @Year, @i,
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.vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND
(YEAR(CLM_DOUT) = @Year) AND (MONTH(CLM_DOUT) = @i)
PRINT 'Record Inserted'
SET @i = @i + 1
END --END While THIS Year...

END --END SP

quote:
Originally posted by visakh16

You can also do like this

SELECT Type,
SUM(CASE WHEN Month <=3 THEN TotalCharges ELSE 0 END) AS Q1,
SUM(CASE WHEN Month > 3 AND Month<=6 THEN TotalCharges ELSE 0 END) AS Q2,
SUM(CASE WHEN Month >6 AND Month<=9 THEN TotalCharges ELSE 0 END) AS Q3,
SUM(CASE WHEN Month >19 AND Month <=12 THEN TotalCharges ELSE 0 END) AS Q4
FROM sumClaimsSettlement
Group by Type

I'm assuming Month is as integer field having month number.

If you know the years for which you need to generate this in advance you can extend my solution to include values for every year.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources