Weekly Monthly Yearly - 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

Weekly Monthly Yearly

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-10-23 : 11:26:59

Okay I created this pivot table for weekly Monthly yearly. Now, in my actually table the order of Type (not set to read weekly monthly, yearl) How can I do that. Right now in my report it is saying......Monthly, Weekly, Yearly.

Here is what one of my statements looks like.

INSERT INTO sumALLClaimsSettlementWMY ([type],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT Distinct 'Weekly',
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()))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 11:30:50

use this

SELECT *
FROM
(
SELECT Distinct 'Weekly',1 AS OrderVal,
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()))
...

SELECT Distinct 'Monthly',2,...
..
SELECT Distinct 'Yearly',3,...
..

)t
ORDER BY OrderVal

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-10-23 : 13:36:30

I think this is going to work, but I am getting an error.
the error says.....

Msg 156, Level 15, State 1, Line 56
Incorrect syntax near the keyword 'INSERT'.
Msg 156, Level 15, State 1, Line 82
Incorrect syntax near the keyword 'INSERT'.
Msg 156, Level 15, State 1, Line 94
Incorrect syntax near the keyword 'PRINT'.

INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

SELECT *
FROM
(
SELECT Distinct 'Weekly',1 AS OrderVal,
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())))

--PRINT 'Record Inserted'

--END --END While Last Year...
-------------------------------------------------------------------------------------------------
-----------Monthly-----------------------------

--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'
--CONTINUE
--END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT *
FROM
(
SELECT Distinct 'Monthly',2 AS OrderVal,
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())))

--PRINT 'Record Inserted'

--END --END While THIS Year...

---------------------------------------------------------------------------------------------------
-----------Yearly-----------------------------

--IF (SELECT Count(*) FROM sumALLClaimsSettlementWMY WHERE /**[Year] = @Year AND [Month] = @i AND**/ [type] = 'Yearly') > 0
--BEGIN
--PRINT 'Record Already Exists'
--CONTINUE
--END --END IF

INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal], /**[Year], [Month], **/clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)
SELECT *
FROM
(
SELECT Distinct 'Yearly',3 AS OrderVal,
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())))
PRINT 'Record Inserted'

END --END While THIS Year...

--END --END SP

quote:

Originally posted by visakh16

use this

SELECT *
FROM
(
SELECT Distinct 'Weekly',1 AS OrderVal,
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()))
...

SELECT Distinct 'Monthly',2,...
..
SELECT Distinct 'Yearly',3,...
..

)t
ORDER BY OrderVal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 14:13:54

use only single INSERT. nest SELECTs inside it


INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

SELECT Type,OrderVal,clmNumber,TotalCharges,AdjustedTotalSavings,PercentSavings,DHSIncome
FROM
(
SELECT Distinct 'Weekly' AS Type,1 AS OrderVal,
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())))

UNION ALL
SELECT Distinct 'Monthly',2 AS OrderVal,
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())))

UNION ALL

SELECT Distinct 'Yearly',3 AS OrderVal,
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())))
PRINT 'Record Inserted'

)t



SELECT * FROM sumALLClaimsSettlementWMY ORDER BY OrderVal

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-10-24 : 08:08:50

Thanks you for the help, but I am still getting to error message and I am not sure why.

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 60
Incorrect syntax near ')'.

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

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[sumALLClaimsSettlementWMY]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[sumALLClaimsSettlementWMY]
--------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[sumALLClaimsSettlementWMY](
[clmNumber] [varchar](50) NOT NULL,
[Type] [varchar](50) NULL,
[OrderVal] [varchar](50) NULL,
[TotalCharges] [decimal](10, 2) NULL,
[AdjustedTotalSavings] [decimal](10, 2) NULL,
[PercentSavings] [decimal](10, 2) NULL,
[DHSIncome] [decimal](10, 2) NULL)

---------------------------------------------------------------------------------------------------

INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

SELECT Type,OrderVal,clmNumber,TotalCharges,AdjustedTotalSavings,PercentSavings,DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
(
SELECT Distinct 'Weekly' AS Type,1 AS OrderVal,
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())))

--UNION ALL
SELECT Distinct 'Monthly',2 AS OrderVal,
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()))

--UNION ALL

SELECT Distinct 'Yearly',3 AS OrderVal,
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()))
PRINT 'Record Inserted'

)t

quote:

Originally posted by visakh16

use only single INSERT. nest SELECTs inside it


INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

SELECT Type,OrderVal,clmNumber,TotalCharges,AdjustedTotalSavings,PercentSavings,DHSIncome
FROM
(
SELECT Distinct 'Weekly' AS Type,1 AS OrderVal,
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())))

UNION ALL
SELECT Distinct 'Monthly',2 AS OrderVal,
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())))

UNION ALL

SELECT Distinct 'Yearly',3 AS OrderVal,
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())))
PRINT 'Record Inserted'

)t



SELECT * FROM sumALLClaimsSettlementWMY ORDER BY OrderVal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:30:19

why cant you use query that i provided/ whats the purpose of code in red?? WHy did you comment out UNION ALL? try query as given or explain where you want to integrate this? Remove code in red below and try running.

INSERT INTO sumALLClaimsSettlementWMY ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

SELECT Type,OrderVal,clmNumber,TotalCharges,AdjustedTotalSavings,PercentSavings,DHSIncome
FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
(
SELECT Distinct 'Weekly' AS Type,1 AS OrderVal,
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())))

UNION ALL

SELECT Distinct 'Monthly',2 AS OrderVal,
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()))

UNION ALL

SELECT Distinct 'Yearly',3 AS OrderVal,
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()))
PRINT 'Record Inserted'

)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources