***SQL SERVER 2005-IMP QUERIES***

Update a table
=============
update cd
set cd.PrimaryContainerVolumeQty = pp.vol_per_physical_case,
cd.UnitsPerCaseQty = 1,
cd.UnitOfMeasure = 4
from #v_Source_ConversionData cd
inner join #OutletProducts pp on
cd.SalesProductID = pp.SalesProductID
and pp.VarietyPackIndicator = 'Y'
and pp.vol_per_physical_case != 0;
Duplicate Query
=============
SELECT DISTINCT l0_id ,COUNT(*) FROM
GROUP BY l0_id HAVING COUNT(l0_id) > 1

CASE Example
=============
select
case when manual_close = 'true' then 1 else
case when auto_close_without_est = 'false' then 2 else
case when auto_close_without_est = 'true' then 3 else 0 End
End
End
From
t_division

Usage Of Cursor
=============
--Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Nth Highest Salary
=============
SELECT TOP 1 division_id
FROM (
SELECT DISTINCT TOP 3 division_id
FROM t_division
ORDER BY division_id DESC) a
ORDER BY division_id


Date Functions
=============

1. select convert(varchar,datepart(yyyy,getdate())) + '-' + convert(varchar,datepart(mm,getdate())) + '-' + convert(varchar,datepart(dd,getdate()))
2. select datepart(yyyy,getdate()) as CYear,datepart(mm,getdate()) as CMonth,datepart(dd,getdate()) as CDate

3. select (MONTH(cast('Jan 01 2004' as datetime))) as YR
4. select (YEAR(cast('Jan 01 2004' as datetime))) as YR
5. select (DAY(cast('Jan 01 2004' as datetime))) as YR
Usage Of ROWNUMBER
=============

SELECT * FROM
(
select row_number() over( Partition By l0_id ORDER BY l0_id,row_ID) ROWNUMBER, l0_id,row_ID FROM local_hier.t_local_hier_edit
WHERE division_id=4 AND local_hier_id = 3 AND l0_id in ('-1','5415')
) A
where rownumber = 1







Data validation between Two tables with More JOINS
=============
/*Source */
select month,code, description,sum(sales) SourceSales
into #tempa
from sourcesales with (nolock) inner join
calendar_445 with (nolock)
sourcesales. daycode = calendar_445.day_key inner join
Sourcebot with (nolock)
on sourcesales.sourcebotcode= sourcebots.sourcebotcode
where month >=@monthid
and month <=@monthid
group by month,code, description
order by month,code, description

/*Olap*/

select month,code, description,
sum(sales) as PS ,sum(ucases) as US
into #tempb
from o_sales with (nolock) inner join
calendar_445 with (nolock)
on o_sales.dayid = calendar_445.day_id inner join
sourcebot with (nolock)
where month >=@monthid
and month <=@monthid
group by month,code, description
order by month,code, description
/*Temp Table Operation*/
select b.Month_id, b.sourcebotcd, b.description, b.PC, b.UC, a.SourceSales
,c.MonthlyPC, c.MonthlyUC
from #tempb b
join #tempc c
on b.Month_id= c.monthid
and b.sourcebotcd = c.sourcebotcd
left join #tempa a
on b.Month_id = a.Month_id
and b.sourcebotcd = a.sourcebotcd
order by 1,2

/*