WE Need SQL SERVER view(Urgent)

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

WE Need SQL SERVER view(Urgent)

Author

Topic

krishnakumarmr
Starting Member

1 Post

Posted - 2015-04-17 : 22:17:12

HI Tem,
Please find the table data below,
ISSUE_ID QTR_YEAR QTR_NUMBER TAX ISSUE_QTR_STATUS
10001 2014 1 10 APPROVED
10001 2014 2 15 Draft
10001 2014 3 20 APPROVED
10001 2014 4 10 APPROVED

We need two reports one is QTD and another one YTD

FOR QTD report is working fine.We are doing summing of YTD report as Q1+Q2+Q3+Q4

ISSUE_QTR_STATUS as APPROVEd
We are going to select the YTD Q1 reports we need the output as 10 Summing of Q1
We are going to select the YTD Q2 reports we need the output as 10 Summing of Q1+Q2
We are going to select the YTD Q3 reports we need the output as 30 Summing OF Q1+Q2+Q3
We are going to select the YTD Q4 reports we need the output as 40 Summing of Q1+Q2+Q3+Q4

ISSUE_QTR_STATUS as DRAFT
We are going to select the YTD Q1 reports we need the output as 0 Summing of Q1
We are going to select the YTD Q2 reports we need the output as 15 Summing of Q1+Q2
We are going to select the YTD Q3 reports we need the output as 15 Summing OF Q1+Q2+Q3
We are going to select the YTD Q4 reports we need the output as 15 Summing of Q1+Q2+Q3+Q4

We need a output view level.We are not using procedure in the report.
Please provide the SQl Query.

krishnakumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-19 : 03:16:37

Use SUM() OVER () if you are using SQL Server 2012 or later.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Maithil
Starting Member

29 Posts

Posted - 2015-04-20 : 01:48:33

Hi,

Your Query can be like this
SELECT CASE WHEN Qtr_number=1 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1) and Issue_Qtr_Status='APPROVED')
WHEN Qtr_number=2 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2) and Issue_Qtr_Status='APPROVED')
WHEN Qtr_number=3 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2,3) and Issue_Qtr_Status='APPROVED')
WHEN Qtr_number=4 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2,3,4) and Issue_Qtr_Status='APPROVED')
END AS 'Total_tax'
FROM test_data

--For --ISSUE_QTR_STATUS as Draft
SELECT ISNULL(CASE WHEN Qtr_number=1 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1) and Issue_Qtr_Status='DRAFT')
WHEN Qtr_number=2 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2) and Issue_Qtr_Status='DRAFT')
WHEN Qtr_number=3 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2,3) and Issue_Qtr_Status='DRAFT')
WHEN Qtr_number=4 then (SELECT DISTINCT SUM(Tax) OVER(Partition by Issue_Qtr_Status) Total_Tax from test_Data where Qtr_number in (1,2,3,4) and Issue_Qtr_Status='DRAFT')
END,0) AS 'Total_tax'
FROM test_data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources