Database

GROUP BY SUBSTR([COLUMN], 1, 1)

ahmad_yossef wrote:

i want to convert this days (integer) to years months and days

SELECT
    dayss, 
    dayss / 365 As years, 
    Cast(Floor((dayss % 365) * 12 / 365.0) As int) As Months, 
    (dayss % 365) - Cast(Floor((dayss % 365) * 12 / 365.0) * 365.0 / 12 As int) As days
FROM
    dbo.lv_npay_tmp3

Select DISTINCT id, name
FROM stud
INNER JOIN dep on stud.depid=dep.depid
LEFT JOIN class on stud.id=class.id
WHERE dep.rid=@rid 
AND stud.id is null

select id,name
from stud s
where not exists
(
    select c.id
    from class c
    where c.id = s.id
)
and depid in
(
    select depid
    from dep
    where rid=@rid
)

Select sum(GTotal) Grand
From(
SELECT
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
)
    UNION ALL
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
))

WITH sums AS (
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
    UNION ALL
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
    )
SELECT  Sum(GrandTotal) AS GrandTotal
FROM    sums