CREATE TABLE SalesByQuarter
(year INT,
quarter CHAR(2),
amount MONEY
)
CREATE INDEX INX_yq ON SalesByQuarter(year, quarter)


--DROP TABLE SalesByQuarter

CREATE TABLE SalesByQuarterId
(
id int identity primary key,
year INT,
quarter CHAR(2),
amount MONEY
)


select * from salesbyquarter



TRUNCATE TABLE SalesByQuarterId


SET NOCOUNT ON
DECLARE @index INT
DECLARE @q INT
SET @index = 0
DECLARE @year INT
repeat:
SET @index = @index
SET @year = 1985 + ((@index % 84) / 4)
SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
INSERT INTO SalesByQuarterId VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
SET @index = @index + 1
if @index < (4 * 4000) GOTO repeat

select * from SalesByQuarterId

select RAND()


SELECT count(*) from salesbyquarter

SELECT * FROM SalesByQuarter TABLESAMPLE(10)



SELECT * FROM SalesByQuarterID
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC

SELECT year,
Q1 as [Quarter 1],
Q2 as [Quarter 2],
Q3 as [Quarter 3],
Q4 as [Quarter 4]
FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC





SELECT S2.Year,
(SELECT Sum(quarter) From S1 WHERE S1.quarter='Q1') AS Q1
FROM SalesByQuarter S1
JOIN SalesByQuarter S2
ON S1.Year = S2.Year

-- simple query to find Q1 sales 
-- amount, year over year
SELECT Year, SUM(amount) AS Q1
From SalesByQuarter GROUP BY Year, Quarter
HAVING quarter='Q1'
ORDER BY Year DESC


-- previous simple query repeatedly
-- joined for each quarter to
-- find quartly sales results
-- for all quarters
SELECT SQ1.year, Q1, Q2, Q3, Q4
FROM (
SELECT year, SUM(amount) AS Q1
From SalesByQuarter GROUP BY year, quarter
HAVING quarter='Q1') AS SQ1
JOIN (
SELECT year, SUM(amount)  AS Q2
From SalesByQuarter GROUP BY year, quarter
HAVING quarter='Q2') AS SQ2
ON SQ1.year = SQ2.year
JOIN (
SELECT year, SUM(amount) AS Q3
From SalesByQuarter GROUP BY year, quarter
HAVING quarter='Q3') AS SQ3
ON SQ2.year = SQ3.year
JOIN (
SELECT year, SUM(amount) AS Q4
From SalesByQuarter GROUP BY year, quarter
HAVING quarter='Q4'
) AS SQ4
ON SQ3.year = SQ4.year
ORDER BY SQ1.year DESC



SELECT
  year
,  sum (case when quarter = 'Q1' then amount else 0 end) Q1
,  sum (case when quarter = 'Q2' then amount else 0 end) Q2
,  sum (case when quarter = 'Q3' then amount else 0 end) Q3
,  sum (case when quarter = 'Q4' then amount else 0 end) Q4
FROM
  SalesByQuarter
GROUP BY
   year
ORDER BY
   year DESC

-- PIVOT operator to find 
-- quarterly sales results
-- for all quarters
SELECT * FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC

SELECT * FROM SalesByQuarter
PIVOT
(
count (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC

select @@version



SELECT * FROM SalesByQuarterID
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC

SELECT id, year, Q1, Q2, Q3, Q4 FROM SalesByQuarterID
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC

WITH orders
AS
(
SELECT year, quarter, amount FROM SalesByQuarterID
)
SELECT * FROM orders
PIVOT
(
SUM (amount)
FOR quarter in (Q1, Q2, Q3, Q4)
) AS P
ORDER BY year


WITH orders
AS
(
SELECT year, quarter, amount FROM SalesByQuarterID
)
SELECT year, Q1 AS [Quarter 1],
 Q2 AS [Quarter 2],
 Q3 AS [Quarter 3],
 Q4 AS [Quarter 4]
 FROM orders
PIVOT
(
SUM (amount)
FOR quarter in (Q1, Q2, Q3, Q4)
) AS P
ORDER BY year





SELECT year, Q1, Q2, Q3, Q4 FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC




WITH quarters
AS
(
SELECT * FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
),
Rows AS
(
SELECT Rownumber() OVER (ORDER BY year) as rn, year, Q1, Q2, Q3, Q4
FROM quarters
)
SELECT 





WITH Piv
AS
(
SELECT * FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY YEAR DESC
)
SELECT year, Q1, Q2, Q3, Q4
FROM Piv
ORDER BY YEAR DESC


select DISTINCT year from SalesByQuarter S where
not exists 
(SELECT * from SalesByQuarter
WHERE
quarter='Q1'
AND
year = S.year
)

select * from salesbyquarter where year = 2000



SELECT * FROM SalesByQuarter TABLESAMPLE(50 PERCENT)
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P

SELECT COUNT(*) FROM SalesByQuarter

SELECT * FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P


DECLARE @pivoted TABLE
(year int,
Q1 money, Q2 money, Q3 money, Q4 money
)

INSERT INTO @pivoted
SELECT * FROM SalesByQuarter
PIVOT
(
SUM (amount)
FOR quarter IN (Q1, Q2, Q3, Q4)
) AS P
ORDER BY year

SELECT * FROM @pivoted ORDER BY year DESC

SELECT * FROM @pivoted
UNPIVOT
(Amount FOR Quarter in (Q1, Q2, Q3, Q4)
) as U
ORDER BY year DESC
 



SELECT * FROM SalesByQuarter ORDER BY quarter, year


USE [Scratch]
GO
/****** Object:  Table [dbo].[SalesByQuarter]    Script Date: 12/01/2005 19:45:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesByQuarter](
	[year] [int] NULL,
	[quarter] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[amount] [money] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF