February 11, 2020 at 12:26 am
I have a table SASSTATS_ALL that holds the column name and Table name. I used another function to gather all these names into this table. I want to loop thru the table and dynamically build a query that gives us max and min of each column name (every column name has date values). The code below runs but it repeats the column names and table names like 6 times before going onto the next record and changing to the next value. Why is it not updating and going to the record.
This is example of the data in SASSTATS_ALL:
TABLENAME FIELD ID
CTT_2019 B_ISP_DEAL_del EFFDATE 1
CTT_2019 B_ISP_DEAL_del STORNUM 2
CTT_2019 B_ISP_DEAL_del VENDNUM 3
CTT_2019 B_ISP_DEAL_del UPC 4
CTT_2019 B_ISP_DEAL_del OPERATION 5
CTT_2019 B_ISP_DEAL_del DEALAMT 6
CTT_2019 B_ISP_DEAL_del EFFFROM 7
CTT_2019 B_ISP_DEAL_del EFFTHRU 8
CTT_2019 B_ISP_DEAL_del ERRNO 9
CTT_2019 B_ISP_ITEM_All EFFDATE 10
DROP TABLE #TableList
SELECT Id,
TableName ,
Field ,
Populated ,
PerPop
INTO #TableList
FROM [SASSTATS_ALL]
WHERE MINIMUMVALUE LIKE '%/%/%'
OR MAXIMUMVALUE LIKE '%/%/%'
ORDER BY ID
DECLARE @TableName VARCHAR(100),
@FieldName VARCHAR(100),
@Populated VARCHAR(50) ,
@PerPop VARCHAR(50)
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @myid INT =1
DECLARE @Max int
Select @Max = MAX(id) FROM #TableList
WHILE (@myid <= @Max)
BEGIN
SELECT @TableName = TABLENAME, @FieldName = Field, @Populated = Populated, @PerPop = PerPop FROM #TableList WHERE Id = @myid
SET @SqlQuery = 'SELECT '''+Trim(@TableName)+''' AS TableName, MAX(CAST(' +Trim(@FieldName)+' as Date)) AS MaxDate, MIN(CAST(' +Trim(@FieldName)+' as Date)) AS MinDate, ''' +Trim(@FieldName)+ ''' as ColName FROM ' + Trim(@TableName)
PRINT @SqlQuery + ' - ' + Trim(Str(@myid))
INSERT INTO DateStats
(
TblName,
MaxDate,
MinDate,
ColName
) --#TempResult
EXECUTE sp_executesql @SqlQuery
Set @myid = @myid + 1
END
DROP TABLE #TableList
This is what it printed out:
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 1
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 2
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 3
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 4
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 5
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_DEAL_del - 6
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFFROM as Date)) AS MaxDate, MIN(CAST(EFFFROM as Date)) AS MinDate, 'EFFFROM' as ColName FROM CTT_2019B_ISP_DEAL_del - 7
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFTHRU as Date)) AS MaxDate, MIN(CAST(EFFTHRU as Date)) AS MinDate, 'EFFTHRU' as ColName FROM CTT_2019B_ISP_DEAL_del - 8
(1 row affected)
SELECT 'CTT_2019B_ISP_DEAL_del' AS TableName, MAX(CAST(EFFTHRU as Date)) AS MaxDate, MIN(CAST(EFFTHRU as Date)) AS MinDate, 'EFFTHRU' as ColName FROM CTT_2019B_ISP_DEAL_del - 9
(1 row affected)
SELECT 'CTT_2019B_ISP_ITEM_All' AS TableName, MAX(CAST(EFFDATE as Date)) AS MaxDate, MIN(CAST(EFFDATE as Date)) AS MinDate, 'EFFDATE' as ColName FROM CTT_2019B_ISP_ITEM_All - 10
February 11, 2020 at 12:35 pm
I found my own answer! I had an issue with the ID column. Fixed that and issue resolved.
February 11, 2020 at 12:50 pm
According to the timestamps you answered your own question half a day before it was even asked!
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 11, 2020 at 1:32 pm
According to the timestamps you answered your own question half a day before it was even asked!
DBCC TIMEWARP at its best.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 27, 2020 at 8:40 am
Steve Collins wrote:According My KFC Experience to the timestamps you answered your own question half a day before it was even asked!
DBCC TIMEWARP at its best.
Thanks for the information.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply