Modify Script - with no Temp Table

Question

Post reply

Modify Script - with no Temp Table

EjSQLme

SSCrazy

Points: 2172
More actions
August 4, 2010 at 9:01 am

#223971

Nevyn helped me big time on the script below and thank you for that.
I was wondering if the script can be written without a temp table?
CREATE TABLE #MeasureProd
(MeasurID INT
,ClientID INT
,ProductName VARCHAR(12)
,ProdSize DECIMAL(9,2)
,ProdCreatedDate DATETIME
,Compatibility_Level INT
,MeasuredDate DATETIME)
INSERT INTO #MeasureProd
SELECT 39,107,'DPTPCSCB',989.83,'2009-12-14',85,'2010-02-23' UNION
SELECT 58,107,'DPTPCSCB',1012.75,'2009-12-14',85,'2010-03-24' UNION
SELECT 63,107,'DPTPCSCB',1205.47,'2009-12-14',85,'2010-04-08' UNION
SELECT 74,107,'DPTPCSCB',1288.13,'2009-12-14',85,'2010-04-24' UNION
SELECT 90,107,'DPTPCSCB',1388.94,'2009-12-14',85,'2010-05-25' UNION
SELECT 108,107,'DPTPCSCB',1491.25,'2009-12-14',85,'2010-06-29' UNION
SELECT 129,107,'DPTPCSCB',1659.25,'2009-12-14',85,'2010-07-14' UNION
SELECT 43,114,'bkcsdz',102.86,'2010-05-28',90,'2010-02-23' UNION
SELECT 62,114,'bkcsdz',109.28,'2010-05-28',90,'2010-03-24' UNION
SELECT 51,114,'bkcsdz',112.34,'2010-05-28',90,'2010-04-07' UNION
SELECT 78,114,'bkcsdz',115.69,'2010-05-28',90,'2010-04-24' UNION
SELECT 94,114,'bkcsdz',122.12,'2010-05-28',90,'2010-05-25' UNION
SELECT 116,114,'bkcsdz',135.56,'2010-05-28',90,'2010-06-29' UNION
SELECT 138,114,'bkcsdz',144.19,'2010-05-28',90,'2010-07-14' UNION
SELECT 48,120,'xvwfm',49152.89,'2008-12-22',80,'2010-03-23' UNION
SELECT 66,120,'xvwfm',50235.12,'2008-12-22',80,'2010-04-24' UNION
SELECT 82,120,'xvwfm',51155.65,'2008-12-22',80,'2010-05-24' UNION
SELECT 122,120,'xvwfm',52646.69,'2008-12-22',80,'2010-06-29' UNION
SELECT 144,120,'xvwfm',78787.63,'2008-12-22',80,'2010-07-14' UNION
SELECT 84,100,'bfcxb',49538.88,'2009-04-10',90, '2/15/2010' UNION
SELECT 101,100,'bfcxb',50842.88,'2009-04-10',90,'3/22/2010' UNION
SELECT 123,100,'bfcxb',51984.88,'2009-04-10',90,'4/14/2010' UNION
SELECT 148,100,'bfcxb',52349.88,'2009-04-10',90,'5/14/2010' UNION
SELECT 149,100,'bfcxb',35962.88,'2009-04-10',90,'6/8/2010' UNION
SELECT 150,100,'bfcxb',37749.88,'2009-04-10',90,'7/4/2010' UNION
SELECT 124,101,'tkczfbag',2106.19,'2008-05-08',85,'7/14/2010' UNION
SELECT 34,101,'tkczfbag',1036.69,'2008-05-08',85,'6/23/2010' UNION
SELECT 102,101,'tkczfbag',1089.89,'2008-05-08',85,'6/29/2010' UNION
SELECT 85,101,'tkczfbag',1075.96,'2008-05-08',85,'6/25/2010' UNION
SELECT 69,101,'tkczfbag',1051.42,'2008-05-08',85,'6/24/2010' UNION
SELECT 50,101,'tkczfbag',1051.42,'2008-05-08',85,'6/24/2010' UNION
SELECT 52,101,'tkczfbag',1052.69,'2008-05-08',85,'6/24/2010' UNION
SELECT 30,101,'tkczfbag',1036.69,'2008-05-08',85,'6/23/2010' UNION
SELECT 22,101,'tkczfbag',1036.69,'2008-05-08',85,'6/23/2010' UNION
SELECT 24,101,'tkczfbag',1036.69,'2008-05-08',85,'6/23/2010' UNION
SELECT 16,101,'tkczfbag',1039.99,'2008-05-08',85,'6/23/2010' UNION
SELECT 18,102,'bfcxb',15518.06,'2009-03-19',100,'6/23/2010' UNION
SELECT 19,102,'bfcxb',15518.06,'2009-03-19',100,'6/23/2010' UNION
SELECT 25,102,'bfcxb',15624.06,'2009-03-19',100,'6/23/2010' UNION
SELECT 32,102,'bfcxb',27585.06,'2009-03-19',100,'6/23/2010' UNION
SELECT 54,102,'bfcxb',28584.06,'2009-03-19',100,'6/24/2010' UNION
SELECT 36,102,'bfcxb',28595.56,'2009-03-19',100,'6/23/2010' UNION
SELECT 70,102,'bfcxb',29952.35,'2009-03-19',100,'6/24/2010' UNION
SELECT 86,102,'bfcxb',30856.58,'2009-03-19',100,'6/25/2010' UNION
SELECT 103,102,'bfcxb',32586.23,'2009-03-19',100,'6/29/2010' UNION
SELECT 126,102,'bfcxb',32587.99,'2009-03-19',100,'7/14/2010' UNION
SELECT 104,103,'tkcmp',1022.15,'2007-04-20',90,'7/14/2010' UNION
SELECT 125,103,'tkcmp',1029.56,'2007-04-20',90,'6/29/2010' UNION
SELECT 105,104,'tkcmp',1732.56,'2008-07-23',80,'6/29/2010' UNION
SELECT 87,104,'tkcmp',1732.56,'2008-07-23',80,'6/25/2010' UNION
SELECT 71,104,'tkcmp',1732.56,'2008-07-23',80,'6/24/2010' UNION
SELECT 56,104,'tkcmp',1792.38,'2008-07-23',80,'6/24/2010' UNION
SELECT 38,104,'tkcmp',1823.63,'2008-07-23',80,'6/23/2010' UNION
SELECT 31,104,'tkcmp',1823.63,'2008-07-23',80,'6/23/2010'
--SELECT * FROM dbo.#MeasureProd ORDER BY Clientid, MeasuredDate
--drop table #MeasureProd
SELECT MeasurID, ClientID, ProductName, ProdSize, MeasuredDate
into #MeasureProd_temp
FROM #MeasureProd MP
ORDER BY Clientid
select ClientID, ProductName, CAST(Avg(ProdSize) AS DECIMAL(9,2)) as ProdSize,
convert(varchar(4),dateadd(month, datediff(month, 0, MeasuredDate),0),100) + convert(varchar(4),year(dateadd(month, datediff(month, 0, MeasuredDate),0))) as MeasuredDate
into #table
from #MeasureProd_temp
group by ClientID, ProductName, dateadd(month, datediff(month, 0, MeasuredDate),0);
WITH MonthClientCTE (ClientID,ProductName,ProdSizeAvg,QueryMonth)
AS
(SELECT ClientID,ProductName,Avg(ProdSize) AS ProdSizeAvg, dateadd(month, datediff(month, 0, MeasuredDate),0) AS QueryMonth
FROM #table
GROUP BY ClientID,ProductName,dateadd(month, datediff(month, 0, MeasuredDate),0))
SELECT c.ClientID,c.ProductName, 'AvgProdSizeChange' =
CASE WHEN isnull(Avg(d.ProdSizeAvg),0) >= 1024.00
THEN CAST(CAST(isnull(Avg(d.ProdSizeAvg),0)/1024 AS DECIMAL(9,2)) AS varchar(10)) +' GB'
ELSE CAST(CAST(isnull(Avg(d.ProdSizeAvg),0) AS DECIMAL(9,2)) as varchar(10))+' MB'
END,
isnull(CAST(Avg(d.ProdSizeAvg) AS DECIMAL(9,2)),0) AvgProdSizeChangeMB
FROM MonthClientCTE AS c
LEFT OUTER JOIN
(SELECT a.ClientID,a.ProductName,avg(b.ProdSizeAvg-a.ProdSizeAvg) AS ProdSizeAvg
FROM MonthClientCTE AS a
INNER JOIN MonthClientCTE AS b
ON a.ClientID = b.ClientID
and dateadd(month,1,a.QueryMonth)=b.QueryMonth
and b.ProdSizeAvg >= a.ProdSizeAvg
group by a.ProductName,a.clientID)
AS d
ON c.ClientID = d.ClientID
group by d.ProdSizeAvg,c.ProductName,c.clientID
order by c.ClientID
drop table #table
drop table #MeasureProd_temp
drop table #MeasureProd

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

Yes. Did you even try to do this?

WITH TableCTE (ClientID, ProductName, ProdSize, MeasuredDate) AS

(

select ClientID, ProductName, CAST(Avg(ProdSize) AS DECIMAL(9,2)) as ProdSize,

convert(varchar(4),dateadd(month, datediff(month, 0, MeasuredDate),0),100) + convert(varchar(4),year(dateadd(month, datediff(month, 0, MeasuredDate),0))) as MeasuredDate

from #MeasureProd

group by ClientID, ProductName, dateadd(month, datediff(month, 0, MeasuredDate),0)

)

,MonthClientCTE (ClientID,ProductName,ProdSizeAvg,QueryMonth)

AS

(SELECT ClientID,ProductName,Avg(ProdSize) AS ProdSizeAvg, dateadd(month, datediff(month, 0, MeasuredDate),0) AS QueryMonth

FROM TableCTE

GROUP BY ClientID,ProductName,dateadd(month, datediff(month, 0, MeasuredDate),0))

SELECT c.ClientID,c.ProductName, 'AvgProdSizeChange' =

CASE WHEN isnull(Avg(d.ProdSizeAvg),0) >= 1024.00

THEN CAST(CAST(isnull(Avg(d.ProdSizeAvg),0)/1024 AS DECIMAL(9,2)) AS varchar(10)) +' GB'

ELSE CAST(CAST(isnull(Avg(d.ProdSizeAvg),0) AS DECIMAL(9,2)) as varchar(10))+' MB'

END,

isnull(CAST(Avg(d.ProdSizeAvg) AS DECIMAL(9,2)),0) AvgProdSizeChangeMB

FROM MonthClientCTE AS c

LEFT OUTER JOIN

(SELECT a.ClientID,a.ProductName,avg(b.ProdSizeAvg-a.ProdSizeAvg) AS ProdSizeAvg

FROM MonthClientCTE AS a

INNER JOIN MonthClientCTE AS b

ON a.ClientID = b.ClientID

and dateadd(month,1,a.QueryMonth)=b.QueryMonth

and b.ProdSizeAvg >= a.ProdSizeAvg

group by a.ProductName,a.clientID)

AS d

ON c.ClientID = d.ClientID

group by d.ProdSizeAvg,c.ProductName,c.clientID

order by c.ClientID

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2