Database

Filip Dossche wrote:

but I wanted to do it with less code if it was possible

SELECT * FROM Whatever WHERE date>dbo.FloorDate('DD',GetDate())

INSERT INTO somewhere (someInfo,someDate) VALUES ('abc',dbo.FloorDate('DD',GetDate()))

CREATE FUNCTION  [Lib].[DateTruncate] (@Subject DateTime , @Granularity varchar(4)) 
RETURNS DateTime AS  
BEGIN 
    DECLARE @DW int
    DECLARE @DF int

    SET @DW = DatePart(dw,@Subject) - 1

    IF @Granularity = 'YY'
    BEGIN
        SET @Subject = dateadd(mm,-datepart(mm,@Subject)+1,@Subject)
        SET @Granularity = 'MM'
    END

    IF @Granularity = 'MM'
    BEGIN
        SET @Subject = dateadd(dd,-datepart(dd,@Subject)+1,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'SU'
    BEGIN
        SET @DF = 7-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'MO'
    BEGIN
        SET @DF = 1-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'TU'
    BEGIN
        SET @DF = 2-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'WE'
    BEGIN
        SET @DF = 3-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'TH'
    BEGIN
        SET @DF = 4-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'FR'
    BEGIN
        SET @DF = 5-@DW
        IF @DF >= 0 SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'SA'
    BEGIN
        SET @DF = 6-@DW
        IF @DF >= 0  SET @DF = @DF-7
        SET @Subject = dateadd(dw,@DF,@Subject)
        SET @Granularity = 'DD'
    END

    IF @Granularity = 'DD'
    BEGIN
        SET @Subject = dateadd(hh,-datepart(hh,@Subject),@Subject)
        SET @Granularity = 'HH'
    END

    IF @Granularity = 'HH'
    BEGIN
        SET @Subject = dateadd(mi,-datepart(mi,@Subject),@Subject)
        SET @Granularity = 'MI'
    END

    IF @Granularity = 'MI'
    BEGIN
        SET @Subject = dateadd(ss,-datepart(ss,@Subject),@Subject)
        SET @Granularity = 'SS'
    END

    IF @Granularity = 'SS'
    BEGIN
        SET @Subject = dateadd(ms,-datepart(ms,@Subject),@Subject)
    END

    RETURN @Subject
END

SELECT DISTINCT dbo.__Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
FROM         dbo.__Mandate INNER JOIN
                      dbo.__MandateType ON dbo.__Mandate.MandateTypeId = dbo.__MandateType.Id LEFT OUTER JOIN
                      dbo.__MandateListing ON dbo.__Mandate.Id = dbo.__MandateListing.MandateId LEFT OUTER JOIN
                      dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
                          (SELECT     Id
                            FROM          dbo.__Feature AS __Feature_1
                            WHERE      (Feature = 'Bedroom')) LEFT OUTER JOIN
                      dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
GROUP BY dbo.__Mandate.Id, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize

LEFT OUTER JOIN
                      dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
                          (SELECT     Id
                            FROM          dbo.__Feature AS __Feature_1
                            WHERE      (Feature = 'Bedroom')) LEFT OUTER JOIN
                      dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id

LEFT OUTER JOIN
                      dbo.__MandateFeature AS BathroomFeature ON dbo.__Mandate.Id = BathroomFeature.MandateId AND BathroomFeature.FeatureId =
                          (SELECT     Id
                            FROM          dbo.__Feature AS __Feature_2
                            WHERE      (Feature = 'Bathroom')) LEFT OUTER JOIN
                      dbo.__Feature AS __Feature_2 ON BathroomFeature.FeatureId = dbo.__Feature.Id