SqlServer: grouping a date(time) column by month/year

Using AdventureWorks Рsearch elsewhere in my blog for references to that.

The reference to the US locale is that I should be using an explicit conversion – this style is certainly bad practice, but not important for the example. The focus on the 141 value is to show that this was the smallest number of sales in a month/year. The grouping tells me this was for June 2011. So to prove my logic is right, I then select all the rows for June 2011… and they total 141. QED.

-- count sales for each month/year, ordering by 
-- highest sales in a month/year, descending

with salesByMonthYear as (
select 
 DATEPART(month,modifiedDate) salesDateMonth ,
 DATEPART(year,modifiedDate) salesDateYear ,
 count(1) SalesForThisMonthYear
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
group by DATEPART(month,modifiedDate) ,
 DATEPART(year,modifiedDate) 
)
select * from salesByMonthYear
order by SalesForThisMonthYear desc

-- (assuming US locale... bad practice...)
SELECT *
 FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] where 
 ModifiedDate between '6/1/2011' and '06/30/2011'
Advertisements

SQLServer: analytic function – RowNumber()

Using AdventureWorks as before…

analfunc01

 

analfunc02

drop table t_product_persistent;
drop table t_product_staging;
select ProductModelID, Name, 'Persistent' as CatalogDescription into t_product_persistent
 FROM [AdventureWorks2014].[Production].[ProductModel]
select ProductModelID, Name, 'Staging' as CatalogDescription into t_product_staging
 FROM [AdventureWorks2014].[Production].[ProductModel]
-- delete all even keys from persistent
delete from t_product_persistent where ProductModelID%2 = 0
 select * from t_product_persistent order by ProductModelID
 select * from t_product_staging order by ProductModelID
-- Apply the rule: if the key exists on the persistent table, then use that row, else use the row on the 
-- staging table. Net expected result: the ProductModelId is unique across the combined set.
-- Small steps towards the goal...
-- Grab the superset from both tables
select * from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Grab the superset from both tables, adding a column to hold the ranking
select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Finally just keep the Priority 1s..
;with ModelWithPriority as (
 select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
 from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
 ) x
)
select * from ModelWithPriority where xx = 1