SQLTeam.com | Weblogs | Forums

Need help in restructuring a SQL Query

Hi All,

I am trying to calculate a aggregated measure value "quantity" only for the maximum date coming for each "source"(dimension) values from a table daily_snapshot. for ex- the column source has three dfferent values "source1","source2" and "source3" and for each of it the maximum date fetched from col1(Date) is different and i want the aggregation of quantity field only for max date fetched out for each source field values. I have wriiten the script below but it is quite long and slow. Any way of restructing it and making it better. please help.

SELECT col1, id, description, quantity, a.source, DEPT, location FROM

(SELECT source, MAX(col1) AS max_source_date FROM
(SELECT *,
CASE WHEN source IN ('source1') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) not in ('2019','2020') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) in ('2019','2020') THEN PARSE_DATE('%Y/%m/%d',substr(c_day,0,10))
ELSE PARSE_DATE('%Y-%m-%d',c_day) END AS col1
FROM daily_snapshot WHERE
NOT c_day LIKE '%.%' AND c_day <> "") GROUP BY 1) a,
(SELECT source, quantity,
col1,id, description, local_manufacturer AS DEPT,
location
FROM (SELECT *,
CASE WHEN source IN ('source1') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) not in ('2019','2020') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source3' and substr(c_day,0,4) in ('2019','2020') THEN PARSE_DATE('%Y/%m/%d',substr(c_day,0,10))
ELSE PARSE_DATE('%Y-%m-%d',c_day) END AS col1
FROM daily_snapshot WHERE
NOT c_day LIKE '%.%' AND c_day <> "")) b
WHERE a.source = b.source AND a.id = b.col1
GROUP BY 1,2,3,4,5,6,7

For starters, using a CTE should speed things up.

WITH CTE AS(
		SELECT *
			,CASE 
				WHEN source IN ('source1')
					THEN PARSE_DATE('%m/%d/%Y', c_day)
				WHEN source = 'source2'
					AND substr(c_day, 0, 4) NOT IN ('2019', '2020')
					THEN PARSE_DATE('%m/%d/%Y', c_day)
				WHEN source = 'source2'
					AND substr(c_day, 0, 4) IN ('2019', '2020'						)
					THEN PARSE_DATE('%Y/%m/%d', substr(c_day, 0, 10))
				ELSE PARSE_DATE('%Y-%m-%d', c_day)
				END AS col1
		FROM daily_snapshot
		WHERE NOT c_day LIKE '%.%'
			AND c_day <> ""
)
SELECT col1
	,id
	,description
	,quantity
	,a.source
	,DEPT
	,location
FROM (
	SELECT source
		,MAX(col1) AS max_source_date
	FROM CTE
	GROUP BY 1
	) a
	,(
		SELECT source
			,quantity
			,col1
			,id
			,description
			,local_manufacturer AS DEPT
			,location
		FROM CTE) b
WHERE a.source = b.source
	AND a.id = b.col1
GROUP BY 1,2,3,4,5,6,7

Did you ever consider adding an extra date column to the table, convert the dates into that column and use that new date column from there on?

It would help if you could provide us with the CREATE TABLE and some INSERT INTO scripts for example data and the result you expect from a system containing that example data.

please provide sample data, otherwise it will be just wild guess. and then you will come back and say "that did not work" etc

provide sample DDL and DML

something like this but near real data

if OBJECT_ID('tempdb..#daily_snapshot') is not null
	drop table #daily_snapshot 
	go


create table #daily_snapshot(id int,_source varchar(150), col1 date , c_day varchar(150), quantity int,  description  varchar(150), local_manufacturer  varchar(150), location varchar(150) )


insert into #daily_snapshot
select 1, 'Beef', getdate(), '2020-01-12', 3, 'Nice burger', 'Mc Dlanod', 'Ohio' union
select 2, 'Chicken', getdate(), '2020-01-12', 3, 'Nice fillter', 'Mc Kitchen', 'Cali' union
select 3, 'Pork', getdate(), '2020-01-12', 77, 'Lean', 'Mc Piggly Wiggly', 'Texas' union
select 4, 'Tofu', getdate(), '2020-01-12', 4, 'Bland', 'Mc Soyo', 'Seattle' union
select 5, 'Fish', getdate(), '2020-01-12', 23, 'Fresh', 'APHRODITE', 'Florida'