Evaluate or Count if new model was added

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

Transact-SQL (2008)

Evaluate or Count if new model was added

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-13 : 02:46:23

Hi,

I have a requirements to evaluate or check if new model was added to an existing model per RequiredPart. My sample DDL was the result of my query created getting the model and requiredpart. My main query getting the requiredpart and finding or obtaining the model used in the requiredpart.

Hoping my requirements is clear to understand. thank you in advance.

Below sample data and DDL

Create table #Sample
(Model nvarchar(35), RequiredPart nvarchar(35))
Insert into #Sample(Model,RequiredPart )
values ('2XRED','TTC1045'),('2XBLU','TTC1045'),
	('2XBLK','TTC1045'),('2XRED','TTC1045'),
	('8XBLU','BTC1045'),('8XRED','BTC1045'),
	('8XRED','COR30044118XRED'),('2XRED','TTC1046'),('2XBLU','TTC1046'),
	('8XRED','TTC1050'),('8XBLU','TTC1050')

Select 
	t.RequiredPart,
	Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart  for xml path ('')), 1 , 1 ,'') as Model
From #Sample t
Group by t.REQUIREDPART
Order by t.REQUIREDPART 


Desired Result: need to add column Count

RequiredPart-----Model-------------COUNT
BTC1045----------8XBLU,8XRED--------2
COR30044118XRED--8XRED--------------1
TTC1045----------2XBLK,2XBLU,2XRED--3
TTC1046----------2XBLU,2XRED--------2
TTC1050----------8XBLU,8XRED--------2

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-13 : 03:11:12

[code]
Select
t.RequiredPart,
Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart for xml path ('')), 1 , 1 ,'') as Model
,Count(DISTINCT Model) AS noCount
From #Sample t
Group by t.REQUIREDPART
Order by t.REQUIREDPART
[/code]

[code]
RequiredPart Model noCount
BTC1045 8XBLU,8XRED 2
COR30044118XRED 8XRED 1
TTC1045 2XBLK,2XBLU,2XRED 3
TTC1046 2XBLU,2XRED 2
TTC1050 8XBLU,8XRED 2
[/code]

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-15 : 21:23:29

Thank you very much Stepson.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 00:48:40

You're welcome

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-16 : 01:12:51

I forgot to ask this question. Its possible or what should i do to track if new model was added in the list.
let say when i run this query today there's a new model added under requiredpart "BTC1045", the model name is 8XGRN. With a lot of records how could i say or find if has a new model. Thanks.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 01:59:47

Base only on existent fields is little hard.

If you run this query once a day , you can have a job that save the result into a Result_Archive table (truncate/delete every day before repopulate it) and then compare with the actual result.

It is possible to add some new column ? like when this part was added datetime or an ID ?


SELECT TODAY.Model, RA.Model 
	FROM #Result_Archive AS RA
	FULL JOIN 
	(
		Select 
			t.RequiredPart,
			Stuff((Select distinct ',' + model from #Sample where RequiredPart =t.RequiredPart  for xml path ('')), 1 , 1 ,'') as Model
			,Count(DISTINCT Model) AS noCount
		From #Sample t
		Group by t.REQUIREDPART
	) AS TODAY
	ON RA.RequiredPart = TODAY.RequiredPart
WHERE RA.noCount <> TODAY.noCount

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-02-16 : 02:12:22

an edit to this:


WHERE RA.noCount <> TODAY.noCount OR RA.noCount IS NULL

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-02-27 : 04:01:05

Do i need to create a temporary table to save the result of the report called #Result_Archive.
then every time i run this codes after getting the result i need to delete or removed the existing records.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-02 : 00:44:22

#Result_Archive could be a normal table. The idea is that you have something(table) were you store yesterday result to compare with today result.
And yes, that "working" table will hold only yesterday result

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources