You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
There are tens of different ways to do this in SQL. Lets start with the simple correlated subquery (mind the fancy name, once you see and write a few of them, they are very easy to understand):
select -- show
g.name, g.stage, g.score -- all data
from -- from
game as g -- the table
where -- where
not exists -- there isn't
( select * -- any other
from game as g2 -- from the same table
where g2.stage = g.stage -- and the same stage
and g2.score > g.score -- with bigger score
) ;
Another simple way would be to first find the biggest score for each stage using GROUP BY (in a subquery, either a derived table or a CTE) and then JOIN back to the original table:
-- using derived table
select
g.name, g.stage, g.score
from
game as g
join
( select stage, max(score) as score
from game
group by stage
) as m
on m.stage = g.stage
and m.score = g.score ;
-- using CTE
with stage_max as
( select stage, max(score) as score
from game
group by stage
)
select
g.name, g.stage, g.score
from
game as g
join
stage_max as m
on m.stage = g.stage
and m.score = g.score ;
A more modern way would be to use window functions (available in your SQL Server versions), i.e. the RANK() function, so first you get the "rank" of everyone per stage and then select only the ones with rank=1. This can also be done with either a derived table or a CTE:
-- window functions, using derived table
select
w.name, w.stage, w.score
from
( select name, stage, score,
rnk = rank() over (partition by stage
order by score desc)
from game
) as w
where
w.rnk = 1 ;
-- window functions, using CTE
with ranking as
( select name, stage, score,
rnk = rank() over (partition by stage
order by score desc)
from game
)
select
w.name, w.stage, w.score
from
ranking as w
where
w.rnk = 1 ;
Best Answer
Try adding a column name for
COUNT(attendance)- such asCountAttendance.Removing
COUNT(attendance)from theSELECTclause of the subquery should also work (as you found out).Because all columns in a subquery should be named/aliased. Some DBMS (and some versions) don't require this, as they provide arbitrary names themselves. Others, like SQL Server, are more restrictive.
You could even have a single column with
SELECT 1 AS cin the subquery orSELECT NULL AS c. The outer query needs only to count rows withCOUNT(*)so the column list in the subquery is completely irrelevant. What matter are theWHEREandHAVINGclauses.