#CASE OF SUM OF TWO DIFFERENT COLUMN
|
1 2 3 4 5 6 7 8 9 10 |
select sm.Model,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty, sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty from WSMS.dbo.Requisition r left join WSMS.dbo.RequisitionDetail rd on r.RequisitionID=rd.RequisitionID inner join WSMS.dbo.ServiceMaster sm on r.ServiceId=sm.ServiceID where rd.ItemCode not in ('SPAR.MOBL.00000','SPAR.MOBL.99999') and rd.ItemStatus='Used' group by sm.Model |
#ROW NUMBER OVER PARTITION BY
|
1 2 3 4 5 6 |
select DATEDIFF(day, ReleaseDate, GETDATE()) AGES from ( select Model,ReleaseDate , Row_number() OVER( partition BY Model ORDER BY ReleaseDate) RowNumber from tblCellPhoneDepriciationPrice p ) X where X.RowNumber=1 |
#update data from table
|
1 2 3 4 5 6 7 8 |
UPDATE tblDealerInfo SET tblDealerInfo.City = ['Sheet 1$'].CITY, tblDealerInfo.Division = ['Sheet 1$'].Division From ['Sheet 1$'] WHERE tblDealerInfo.DealerCode = ['Sheet 1$'].CUSTOMER_NUMBER |
#CTE with ROW number
;
WITH CTE
AS
(
SELECT [ServiceID]
,[ServicePointID]
,[IME]
,[Model]
,[ServicePlaceDate]
,[IsBilling]
,ROW_NUMBER() OVER (PARTITION BY IME order by IME desc ) AS Rn
FROM [WSMS].[dbo].[ServiceMaster]
–where IME is not null
)
select * from CTE where Rn>=2
#Reseed From Zero
DBCC CHECKIDENT (‘[TV].[dbo].[SparePartsRequision]’, RESEED, 0) GO
#XML path Query
select (
select ‘,’+ItemCode
from RequisitionDetail rd
where r.RequisitionID=rd.RequisitionID
for XML PATH(”)
)
from Requisition r