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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -