tool generated query rewriting in simplest form..

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

tool generated query rewriting in simplest form..

Author

Topic

zara_chd
Starting Member

7 Posts

Posted - 2011-10-06 : 10:36:48

I have to convert this Tools generated query in the simplest form so that it takes less time in execution.. I want to avoid Pivot table also.. please help. what is the alternate way can be

Thanks

--DECLARE @start_date VARCHAR(MAX);
--DECLARE @end_date VARCHAR(MAX);

DECLARE @opidlist AS VARCHAR(MAX);
DECLARE @op_column AS VARCHAR(MAX);
DECLARE @op_column_str AS VARCHAR(MAX);
DECLARE @query_for_rawdata AS VARCHAR(MAX);

-- Change variable
--SET @start_date = '20100605';
--SET @end_date = '201000606';

SET @opidlist =
'''SHFASM'',''INSPF'',''PACK'',''KIT'',''FTEST'',''RAID'',''CERT'',''OQM'',''CONFIG'',''INSP1'',''RAIDEX'',''BATS1''';
SET @op_column =
'[SHFASM],[INSPF],[PACK],[KIT],[FTEST],[RAID],[CERT],[OQM],[CONFIG],[INSP1],[RAIDEX],[BATS1]';
SET @op_column_str = '
t.SHFASM AS tSHFASM, p.SHFASM AS pSHFASM,
t.INSPF AS tINSPF, p.INSPF AS pINSPF,
t.PACK AS tPACK, p.PACK AS pPACK,
t.KIT AS tMLOAD, p.KIT AS pMLOAD,
t.FTEST AS tFTEST, p.FTEST AS pFTEST,
t.RAID AS tRAID, p.RAID AS pRAID,
t.CERT AS tCERT, p.CERT AS pCERT,
t.OQM AS tDISKCHK, p.OQM AS pDISKCHK,
t.CONFIG AS tFWLOAD, p.CONFIG AS pFWLOAD,
t.INSP1 AS tINSP1, p.INSP1 AS pINSP1,
t.RAIDEX AS tRAIDEX, p.RAIDEX AS pRAIDEX,
t.BATS1 AS tBATS1, p.BATS1 AS pBATS1';

SET @query_for_rawdata =
'SELECT a.* FROM (SELECT
CAST(DATEPART(yy, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX)) +

CASE WHEN LEN(CAST(DATEPART(mm, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX))) = 1
THEN ''0''
+ CAST(DATEPART(mm, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX))
ELSE
+ CAST(DATEPART(mm, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX)) END +

CASE WHEN LEN(CAST(DATEPART(dd, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX))) = 1
THEN ''0''
+ CAST(DATEPART(dd, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX))
ELSE
+ CAST(DATEPART(dd, DATEADD(HOUR, -0, CAST(SUBSTRING(edate, 1, 4)
+ ''-'' + SUBSTRING(edate, 5, 2) + ''-'' + SUBSTRING(edate, 7, 2) + '' '' +
SUBSTRING(etime, 1, 2) + '':'' + SUBSTRING(etime, 3, 2)
+ '':'' + SUBSTRING(etime, 5, 2) AS DATETIME))) AS VARCHAR(MAX)) END

AS mytime_us,
CASE WHEN sitref = ''1170'' THEN ''UK''
WHEN sitref = ''2170'' THEN ''US''
WHEN sitref = ''3170'' THEN ''Malaysia''
ELSE ''unknown'' END AS ''sitref'',
''NetApp'' AS model,
sernr, partno, edate AS mytime, etime,
RTRIM(SUBSTRING(opid, 0, LEN(opid) - 1)) AS opid
, runcnd, testid, numtim
FROM rptdb.dbo.xopdata WHERE (edate BETWEEN ' + @start_date + ' AND '
+ @end_date + ')
AND RTRIM(SUBSTRING(opid, 1, LEN(opid) - 2)) IN (' + @opidlist + ')
AND LEFT(testid, 2) NOT IN (''fa'',''fw'',''bw'',''am'',''is'',''np'')
AND RTRIM(SUBSTRING(opid, LEN(opid) - 1, 2)) <> ''WA''
AND (product LIKE ''NA%'' OR linloc = ''1170HW'') AND sernr LIKE
''SHX%'' AND prntser = '''' ) a
WHERE a.mytime = ' + @start_date

EXEC(
'DECLARE @prodTbl TABLE
(
model VARCHAR(15),
partno VARCHAR(15),
drvinfo VARCHAR(20)
);

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

INSERT INTO @prodTbl(model, partno, drvinfo) VALUES(''somevalue -
NA'', ''int value'', ''No Drives'');

SELECT DISTINCT m.*, n.model AS prod, n.drvinfo
FROM
(SELECT t.mytime, t.sitref, t.model, t.partno,
' + @op_column_str + '
FROM
(SELECT mytime, sitref, model, partno,
' + @op_column + '
FROM
(SELECT x.mytime, x.sitref, x.model, x.partno, x.opid, x.sernr
FROM (' + @query_for_rawdata + ') x
WHERE x.runcnd = ''P''
) ps
PIVOT (COUNT(sernr)
FOR opid IN (' + @op_column + ')) AS pvt) t LEFT JOIN
(SELECT mytime, sitref, model, partno,
' + @op_column + '
FROM
(SELECT x.mytime, x.sitref, x.model, x.partno, x.opid, x.sernr
FROM (' + @query_for_rawdata + ') x
WHERE x.runcnd = ''P''
) ps
PIVOT (COUNT(sernr)
FOR opid IN (' + @op_column + ')) AS pvt) p
ON t.mytime = p.mytime AND t.partno = p.partno AND t.sitref = p.sitref
AND t.model = p.model) m
LEFT JOIN @prodTbl n ON LEFT(m.partno, 7) = n.partno'
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:26:33

you mean you want somone to g through all this and suggest a better query? not sure how many will patience and time to do that for free.
Better to give some sample data from tables and explain what you want as output from them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources