June 14, 2017 at 2:07 pm
My data is like below currently- 7 columns below in first table.
Date Country P1 P2 C1 C2 R1
2017-03-01 IN 0 10 20 0 3
2017-05-01 IN 4 20 10 10 0
2017-03-01 US 2 10 5 2 1
2017-05-01 US 2 0 5 20 1
2017-07-01 US 2 8 15 2 10
I am looking for query that will make the data like below in table 2.It will do the running total by country ,add the missing months and invert the dates to column .It will also accept the start and end month .
Country Mar 2017 Apr 2017 May 2017 June 2017 July 2017
P1 IN 0 0 4 4 4
P2 IN 10 10 30 30 30
C1 IN 20 20 30 30 30
C2 IN 0 0 10 10 10
R1 IN 3 3 3 3 3
P1 US 2 2 4 4 6
P2 US 10 10 10 10 18
C1 US 5 5 10 10 25
C2 US 2 2 22 22 24
R1 US 1 1 2 2 12
pls help.
June 14, 2017 at 2:16 pm
Please post a consumable SQL script
June 14, 2017 at 6:06 pm
Here is the table-
CREATE TABLE #temp4(
Bdate DATE
,Country VARCHAR(7)
,Type VARCHAR(4)
,P1 INT
,P2 INT
,C1 INT
,C2 INT
,R1 INT
);
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','IN','A','20','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','B','0','0','10','10','5');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','A','10','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','US','A','2','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','US','A','2','20','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('7/1/2017','US','A','2','8','0','0','0');
Attached file for the output i want.
Here is what i have triedDECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;
SELECT @StartDate = '2011-03-01 00:00:00.000', @EndDate = '2017-03-01 00:00:00.000';
;WITH d(d) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT
[Month] = DATENAME(MONTH, d.d),
[Year] = YEAR(d.d)
, SUM(P1) AS P1
, SUM(P2) AS P2
, SUM(C1) AS C1
, SUM(C2) AS C2
, SUM(R1) AS R1
,Country
FROM d LEFT OUTER JOIN #TEMP4 AS o
ON o.BDate >= d.d
AND o.BDate < DATEADD(MONTH, 1, d.d)
GROUP BY d.d,Country
ORDER BY d.d,Country;
I hope someone could help me out with this query. Thanks!
June 15, 2017 at 10:55 am
Your attachment is a pivot, which is odd. Is that what you need because that's not what your query shows.
However, you also have a enddate that seems too soon.
I prefer tests, so here's my tsqlt test to run this (get tsqlt here). If you also the insert into #expected to get what you want, I can help debug (or someone will)
EXEC tSQLt.NewTestClass @ClassName = N'Misctests';
GO
CREATE OR ALTER PROC Misctests.[test Getmissing dates]
AS
BEGIN
-- assemble
CREATE TABLE #Source
( Bdate DATE,
Country VARCHAR(7),
Type VARCHAR(4),
P1 INT,
P2 INT,
C1 INT,
C2 INT,
R1 INT
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'3/1/2017', 'IN', 'A', '20', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'IN', 'B', '0', '0', '10', '10', '5'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'IN', 'A', '10', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'3/1/2017', 'US', 'A', '2', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'US', 'A', '2', '20', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'7/1/2017', 'US', 'A', '2', '8', '0', '0', '0'
);
CREATE TABLE #Expected
( Month VARCHAR(20),
Year CHAR(4),
p1 INT,
p2 INT,
c1 INT,
c2 INT,
r1 INT,
Country CHAR(3)
);
SELECT
Month,
Year,
p1,
p2,
c1,
c2,
r1,
Country
INTO #Actual
FROM #Expected
WHERE 1 = 0;
INSERT #Expected
( Month,
Year,
p1,
p2,
c1,
c2,
r1,
Country
)
VALUES
(
'March', '2017', 20, 10, 0, 0, 0, 'US'
),
(
'April', '2017', 20, 10, 0, 0, 0, 'US'
),
(
'May', '2017', 30, 20, 10, 10, 5, 'US'
),
(
'June', '2017', 30, 20, 10, 10, 5, 'US'
),
(
'July', '2017', 30, 20, 10, 10, 5, 'US'
);
-- act
DECLARE
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME;
SELECT
@StartDate = '2011-03-01 00:00:00.000',
@EndDate = '2017-07-01 00:00:00.000';
;WITH d (d)
AS (SELECT
DATEADD(
MONTH,
n,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)
)
FROM
( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
n = ROW_NUMBER() OVER (ORDER BY object_id) - 1
FROM sys.all_objects
ORDER BY object_id
) n
)
INSERT #Actual
SELECT
Month = DATENAME(MONTH, d.d),
Year = YEAR(d.d),
P1 = SUM(P1),
P2 = SUM(P2),
C1 = SUM(C1),
C2 = SUM(C2),
R1 = SUM(R1),
Country
FROM
d
LEFT OUTER JOIN #Source o
ON o.Bdate >= d.d
AND o.Bdate < DATEADD(MONTH, 1, d.d)
GROUP BY
d.d,
Country
ORDER BY
d.d,
Country;
-- assert
EXEC tSQLt.AssertEqualsTable
@Expected = N'#expected',
@Actual = N'#actual',
@Message = N'error';
END;
GO
EXEC tSQLt.Run '[Misctests].[test Getmissing dates]';
June 15, 2017 at 8:16 pm
Steve Jones - SSC Editor - Thursday, June 15, 2017 10:55 AMYour attachment is a pivot, which is odd. Is that what you need because that's not what your query shows.
However, you also have a enddate that seems too soon.I prefer tests, so here's my tsqlt test to run this (get tsqlt here). If you also the insert into #expected to get what you want, I can help debug (or someone will)
EXEC tSQLt.NewTestClass @ClassName = N'Misctests';
GOCREATE OR ALTER PROC Misctests.[test Getmissing dates]
AS
BEGIN-- assemble
CREATE TABLE #Source
( Bdate DATE,
Country VARCHAR(7),
Type VARCHAR(4),
P1 INT,
P2 INT,
C1 INT,
C2 INT,
R1 INT
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'3/1/2017', 'IN', 'A', '20', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'IN', 'B', '0', '0', '10', '10', '5'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'IN', 'A', '10', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'3/1/2017', 'US', 'A', '2', '10', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'5/1/2017', 'US', 'A', '2', '20', '0', '0', '0'
);
INSERT INTO #Source
( Bdate,
Country,
Type,
P1,
P2,
C1,
C2,
R1
)
VALUES
(
'7/1/2017', 'US', 'A', '2', '8', '0', '0', '0'
);
CREATE TABLE #Expected
( Month VARCHAR(20),
Year CHAR(4),
p1 INT,
p2 INT,
c1 INT,
c2 INT,
r1 INT,
Country CHAR(3)
);
SELECT
Month,
Year,
p1,
p2,
c1,
c2,
r1,
Country
INTO #Actual
FROM #Expected
WHERE 1 = 0;
INSERT #Expected
( Month,
Year,
p1,
p2,
c1,
c2,
r1,
Country
)
VALUES
(
'March', '2017', 20, 10, 0, 0, 0, 'US'
),
(
'April', '2017', 20, 10, 0, 0, 0, 'US'
),
(
'May', '2017', 30, 20, 10, 10, 5, 'US'
),
(
'June', '2017', 30, 20, 10, 10, 5, 'US'
),
(
'July', '2017', 30, 20, 10, 10, 5, 'US'
);-- act
DECLARE
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME;
SELECT
@StartDate = '2011-03-01 00:00:00.000',
@EndDate = '2017-07-01 00:00:00.000';
;WITH d (d)
AS (SELECT
DATEADD(
MONTH,
n,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)
)
FROM
( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
n = ROW_NUMBER() OVER (ORDER BY object_id) - 1
FROM sys.all_objects
ORDER BY object_id
) n
)
INSERT #Actual
SELECT
Month = DATENAME(MONTH, d.d),
Year = YEAR(d.d),
P1 = SUM(P1),
P2 = SUM(P2),
C1 = SUM(C1),
C2 = SUM(C2),
R1 = SUM(R1),
Country
FROM
d
LEFT OUTER JOIN #Source o
ON o.Bdate >= d.d
AND o.Bdate < DATEADD(MONTH, 1, d.d)
GROUP BY
d.d,
Country
ORDER BY
d.d,
Country;-- assert
EXEC tSQLt.AssertEqualsTable
@Expected = N'#expected',
@Actual = N'#actual',
@Message = N'error';
END;
GOEXEC tSQLt.Run '[Misctests].[test Getmissing dates]';
I did not understand how to debug your code.
Yes the output is a pivot ,I am also not getting running totals so need help in getting the same output as my attached sheet.
June 15, 2017 at 8:44 pm
Try this...
IF OBJECT_ID('tempdb..#temp4', 'U') IS NOT NULL
DROP TABLE #temp4;
GO
CREATE TABLE #temp4(
Bdate DATE
,Country VARCHAR(7)
,Type VARCHAR(4)
,P1 INT
,P2 INT
,C1 INT
,C2 INT
,R1 INT
);
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','IN','A','20','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','B','0','0','10','10','5');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','A','10','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','US','A','2','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','US','A','2','20','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('7/1/2017','US','A','2','8','0','0','0');
SELECT * FROM #temp4 t
--=========================================================================================
SELECT
OrigCol = STUFF(up.OrigCol, 1, 1, ''),
t.Type,
[Mar-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 3 THEN up.Value ELSE 0 END),
[Apr-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 4 THEN up.Value ELSE 0 END),
[May-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 5 THEN up.Value ELSE 0 END),
[Jun-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 6 THEN up.Value ELSE 0 END),
[Jul-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 7 THEN up.Value ELSE 0 END)
FROM
#temp4 t
CROSS APPLY ( VALUES ('1P1', t.P1), ('2P2', t.P2), ('3C1', t.C1), ('4C2', t.C2), ('5R1', t.R1) ) up (OrigCol, Value)
CROSS APPLY ( VALUES (YEAR(t.Bdate), MONTH(t.Bdate)) ) dp ([Year], [Month])
GROUP BY
t.Type,
up.OrigCol
ORDER BY
t.Type,
up.OrigCol;
June 15, 2017 at 9:44 pm
Jason A. Long - Thursday, June 15, 2017 8:44 PMTry this...
IF OBJECT_ID('tempdb..#temp4', 'U') IS NOT NULL
DROP TABLE #temp4;
GO
CREATE TABLE #temp4(
Bdate DATE
,Country VARCHAR(7)
,Type VARCHAR(4)
,P1 INT
,P2 INT
,C1 INT
,C2 INT
,R1 INT
);INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','IN','A','20','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','B','0','0','10','10','5');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','IN','A','10','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('3/1/2017','US','A','2','10','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('5/1/2017','US','A','2','20','0','0','0');
INSERT INTO #temp4(Bdate,Country,Type,P1,P2,C1,C2,R1) VALUES ('7/1/2017','US','A','2','8','0','0','0');SELECT * FROM #temp4 t
--=========================================================================================
SELECT
OrigCol = STUFF(up.OrigCol, 1, 1, ''),
t.Type,
[Mar-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 3 THEN up.Value ELSE 0 END),
[Apr-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 4 THEN up.Value ELSE 0 END),
[May-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 5 THEN up.Value ELSE 0 END),
[Jun-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 6 THEN up.Value ELSE 0 END),
[Jul-17] = SUM(CASE WHEN dp.Year = 2017 AND dp.Month = 7 THEN up.Value ELSE 0 END)FROM
#temp4 t
CROSS APPLY ( VALUES ('1P1', t.P1), ('2P2', t.P2), ('3C1', t.C1), ('4C2', t.C2), ('5R1', t.R1) ) up (OrigCol, Value)
CROSS APPLY ( VALUES (YEAR(t.Bdate), MONTH(t.Bdate)) ) dp ([Year], [Month])
GROUP BY
t.Type,
up.OrigCol
ORDER BY
t.Type,
up.OrigCol;
It is somewhat close to what i need. But i also need the country as well in the output-OrigCol,Country,Type(Type B belongs to only C1,C2 &R),Type A belongs to P1,P2. looking for Running totals between start and end date. And the dates will not be hard coded they will be entered as parameter for start and end date.
June 16, 2017 at 4:51 am
You're going to need dynamic sql to get the month/year columns. Here's a query for picking up the values:
-- list of dates
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
For the main part of the query, start with hard-coded for testing then recode to dynamic sql when you're done. I think this is pretty close:SELECT x.[Desc], t.Type, t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
ORDER BY t.Country, t.Type, x.ReportOrder
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2017 at 9:03 am
ChrisM@Work - Friday, June 16, 2017 4:51 AMYou're going to need dynamic sql to get the month/year columns. Here's a query for picking up the values:-- list of dates
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(Bdate), EndMonth = MAX(Bdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
For the main part of the query, start with hard-coded for testing then recode to dynamic sql when you're done. I think this is pretty close:
SELECT x.[Desc], t.Type, t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
ORDER BY t.Country, t.Type, x.ReportOrder
SELECT [Bdate] = DATEADD(MONTH,n,StartMonth)
FROM (SELECT StartMonth = MIN(billingstartdate), EndMonth = MAX(billingstartdate) FROM #temp4) m
CROSS APPLY (
SELECT TOP(1+DATEDIFF(MONTH,m.StartMonth,m.EndMonth)) n
FROM (VALUES (0),(1),(2),(3),(4),(5)) d (n)
) x
This code gives only upto 6 dates. if my date range if from '2014-08-01 00:00:00.000' to '2016-01-01 00:00:00.000' how will i get all the dates?
Also i have attached my expected output can you pls look at attached sheet. Type A belongs to only P1 & P2 ,Type B belongs to C1,C2,R1.
June 16, 2017 at 9:10 am
Please read Chris' post again, particularly this:
For the main part of the query, start with hard-coded for testing then recode to dynamic sql when you're done.
He's giving you a starting point. You need to use dynamic SQL to handle the dynamic columns.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 16, 2017 at 9:33 am
Ta Phil 🙂
Here's a correction to the hard-coded query, check that it's what you're looking for from the hard-coded version. Let us know if it is, and someone will help you with converting it to dynamic sql to cope with the dates:
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrder
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2017 at 10:14 am
ChrisM@Work - Friday, June 16, 2017 9:33 AMTa Phil 🙂Here's a correction to the hard-coded query, check that it's what you're looking for from the hard-coded version. Let us know if it is, and someone will help you with converting it to dynamic sql to cope with the dates:
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrder
Thanks Chris. It is somewhat close. Is it possible for the C1,C2 ,R1 to repeat for US even if there are no values. I have attached the same output for your reference. I ll need help with running total (attached)and dynamic dates too.
June 19, 2017 at 7:06 am
Papil - Friday, June 16, 2017 10:14 AMChrisM@Work - Friday, June 16, 2017 9:33 AMTa Phil 🙂Here's a correction to the hard-coded query, check that it's what you're looking for from the hard-coded version. Let us know if it is, and someone will help you with converting it to dynamic sql to cope with the dates:
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrderThanks Chris. It is somewhat close. Is it possible for the C1,C2 ,R1 to repeat for US even if there are no values. I have attached the same output for your reference. I ll need help with running total (attached)and dynamic dates too.
Is the rowcount always fixed to 10, like this?
Desc Type Country
P1 A IN
P2 A IN
C1 B IN
C2 B IN
R1 B IN
P1 A US
P2 A US
C1 B US
C2 B US
R1 B US
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2017 at 7:44 am
ChrisM@Work - Monday, June 19, 2017 7:06 AMPapil - Friday, June 16, 2017 10:14 AMChrisM@Work - Friday, June 16, 2017 9:33 AMTa Phil 🙂Here's a correction to the hard-coded query, check that it's what you're looking for from the hard-coded version. Let us know if it is, and someone will help you with converting it to dynamic sql to cope with the dates:
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrderThanks Chris. It is somewhat close. Is it possible for the C1,C2 ,R1 to repeat for US even if there are no values. I have attached the same output for your reference. I ll need help with running total (attached)and dynamic dates too.
Is the rowcount always fixed to 10, like this?
Desc Type Country
P1 A IN
P2 A IN
C1 B IN
C2 B IN
R1 B IN
P1 A US
P2 A US
C1 B US
C2 B US
R1 B US
No the row count is not fixed. I have data for other countries too.
June 19, 2017 at 8:30 am
Papil - Monday, June 19, 2017 7:44 AMChrisM@Work - Monday, June 19, 2017 7:06 AMPapil - Friday, June 16, 2017 10:14 AMChrisM@Work - Friday, June 16, 2017 9:33 AMTa Phil 🙂Here's a correction to the hard-coded query, check that it's what you're looking for from the hard-coded version. Let us know if it is, and someone will help you with converting it to dynamic sql to cope with the dates:
SELECT x.[Desc], t.[Type], t.Country,
'MAR-17' = SUM(CASE WHEN t.Bdate = '2017-03-01' THEN x.[Value] ELSE 0 END),
'APR-17' = SUM(CASE WHEN t.Bdate = '2017-04-01' THEN x.[Value] ELSE 0 END),
'MAY-17' = SUM(CASE WHEN t.Bdate = '2017-05-01' THEN x.[Value] ELSE 0 END),
'JUN-17' = SUM(CASE WHEN t.Bdate = '2017-06-01' THEN x.[Value] ELSE 0 END),
'JUL-17' = SUM(CASE WHEN t.Bdate = '2017-07-01' THEN x.[Value] ELSE 0 END)
FROM #temp4 t
CROSS APPLY (
VALUES ('P1',P1,1),('P2',P2,2),('C1',C1,3),('C2',C2,4),('R1',R1,5)
) x ([Desc], [Value], ReportOrder)
GROUP BY t.Country, t.Type, x.ReportOrder, x.[Desc]
HAVING (t.[Type] = 'A' AND x.[Desc] IN ('P1','P2'))
OR (t.[Type] = 'B' AND x.[Desc] IN ('C1','C2','R1'))
ORDER BY t.Country, t.[Type], x.ReportOrderThanks Chris. It is somewhat close. Is it possible for the C1,C2 ,R1 to repeat for US even if there are no values. I have attached the same output for your reference. I ll need help with running total (attached)and dynamic dates too.
Is the rowcount always fixed to 10, like this?
Desc Type Country
P1 A IN
P2 A IN
C1 B IN
C2 B IN
R1 B IN
P1 A US
P2 A US
C1 B US
C2 B US
R1 B USNo the row count is not fixed. I have data for other countries too.
I think i dint read your question properly. Yes the row count will be fixed like that. but there will be more data . I have attached expected output i am looking for.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply