T-SQL QA Query 1

[smedvid]

Maybe my brain thinks it is Monday... I can not devise a solution to following SQL puzzle...

I have a primary data table and a lookup table. I am trying to validate that the primary data Year is contained for each and every category in the lookup table. In the example below, I am missing Category AAA for SaleYear 2008.

Sales Data:
ID SaleDate
123 01/01/2010
345 01/10/2009
678 01/20/2008

Lookup Table Data:
Category SaleYear GroupYear
AAA 2010 2010
AAA 2009 2010
BBB 2010 2010
BBB 2009 2010
BBB 2008 2008
CCC 2010 2010
CCC 2009 2010
CCC 2008 2008

I thought of gettting a distinct count of Category then some count query on the primary data and ensuring the coutn matches the distinct count of Category in lookup table... but alas... I can not envision the answer this morning...

Any help is appreciated.

Steve


Steve Medvid
IT Consultant & Web Master

 

[gmmastros]

Try this:

Declare @Sales Table(ID Int, SaleDate DateTime)

Insert Into @Sales Values(123,'01/01/2010')
Insert Into @Sales Values(345,'01/10/2009')
Insert Into @Sales Values(678,'01/20/2008')

Declare @Lookup Table(Category VarChar(20), SaleYear Int, GroupYear Int)
Insert Into @Lookup Values('AAA', 2010, 2010)
Insert Into @Lookup Values('AAA', 2009, 2010)
Insert Into @Lookup Values('BBB', 2010, 2010)
Insert Into @Lookup Values('BBB', 2009, 2010)
Insert Into @Lookup Values('BBB', 2008, 2008)
Insert Into @Lookup Values('CCC', 2010, 2010)
Insert Into @Lookup Values('CCC', 2009, 2010)
Insert Into @Lookup Values('CCC', 2008, 2008)

Select  A.Category, B.SaleYear
From    (Select Distinct Category From @Lookup) As A
        Cross Join (Select Year(SaleDate) As SaleYear From @Sales) As B
        Left Join @Lookup As [Lookup]
          On A.Category = [Lookup].Category
          And B.SaleYear = [Lookup].SaleYear
Where   [Lookup].Category Is NULL

The cross join is there to get every combination of category and year, I then left join to the lookup table to get what is missing.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

Ahhhhhhhh --- Cross Join....
did briefly cross my mond, but did not stick!
LOL

Excellent solution.... Star for U...

Thanks!

Steve


Steve Medvid
IT Consultant & Web Master

 

[gmmastros]

I'm curious to know...

If I had simply said, "Cross join a distinct list of categories with your sales date and then left join to the lookup table", would that have been enough information for you to construct the query? I mean... was the code sample necessary for you to understand the process?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

The "Cross join a distinct list of categories with your sales date and then left join to the lookup table", would have clicked my brain into high gear. I have used Cross Joins in the past...

However the code sample was excellent... plus others may reference this code now... and clearly see the problem, the data and the solution.

Thanks Again!!!!

I did fail to have some caffine this morning, which may have been a leading factor to why I did not even having any ideas on this puzzle you solved quickly.


Steve Medvid
IT Consultant & Web Master

 

[simian336]

Hey George,

I took a little simpler approach and just created a year table and left join... but I am not getting the missing year.

any thoughts.

Simi

create table lstyear (
syear char(4))

insert into lstyear values ('2008')
insert into lstyear values ('2009')
insert into lstyear values ('2010')

create table lookupt (
Category char(3),
SaleYear char(4),
GroupYear char(4))

Category SaleYear GroupYear
insert into lookupt values ('AAA','2010','2010')
insert into lookupt values ('AAA','2009','2010')
insert into lookupt values ('BBB','2010','2010')
insert into lookupt values ('BBB','2009','2010')
insert into lookupt values ('BBB','2008','2008')
insert into lookupt values ('CCC','2010','2010')
insert into lookupt values ('CCC','2009','2010')
insert into lookupt values ('CCC','2008','2008')

select * from lookupt

select *
from lstyear ly
left outer join lookupt l
on ly.syear=l.saleYear
order by l.category, l.saleyear

 

[gmmastros]

You're not getting the missing year because your left join is satisfied by other categories. You really do need the cross join so that you can get a list of all the data you want, and then join to the table to get what's missing.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

Yep... I already programmed the solution and it works A-Ok for me...

Steve Medvid
IT Consultant & Web Master