0

Please login or register to vote for this query.

(click on this box to dismiss)

how long before I get tag badges?

Q&A for professional and enthusiast programmers

CREATE TABLE #pricelist
    (startHour datetime2(0), 
     endHour datetime2(0), 
     price int
    ) ;

INSERT INTO #pricelist
	(startHour, endHour, price)
VALUES
    (convert(datetime2(0), '00:00:00:000', 108), 
     convert(datetime2(0), '08:00:00:000', 108), 
     10),
 
	(convert(datetime2(0), '08:00:00:000', 108), 
     convert(datetime2(0), '16:00:00:000', 108),
     20),
 
	(convert(datetime2(0), '16:00:00:000', 108), 
     DATEADD(day, 1, convert(datetime2(0), '00:00:00:000', 108)) ,  
      5) ;


CREATE TABLE #calendar
	(calendar_date DATE NOT NULL PRIMARY KEY
    ) ;

INSERT INTO #calendar
	(calendar_date)
VALUES

	('2014-12-10'),
	('2014-12-11'),
	('2014-12-12') ;

CREATE TABLE #actualUse
(startDate datetime2(0) NOT NULL, 
endDate datetime2(0) NOT NULL, 
     jobId int NOT NULL
    ) ;
	
INSERT INTO #actualUse
	(startDate, endDate, jobId)
VALUES
	('2014-12-10 08:30:00', '2014-12-10 15:20:00', 1),
	('2014-12-10 07:30:00', '2014-12-10 18:20:00', 2) ;
--	('2014-12-10 07:30:00', '2015-01-10 16:20:00', 3),
--	('2014-12-10 09:30:00', '2015-01-10 00:20:00', 4) ;
    
    
        SELECT 
        startDate = CONCAT(' ',CASE WHEN a.startDate < x.startD THEN x.startD ELSE a.startDate END),  -- max
        endDate = CONCAT(' ',CASE WHEN a.endDate < x.endD THEN a.endDate ELSE x.endD END),            -- min
        a.jobID,
        x.price,
        CONCAT(' ',startD),    CONCAT(' ',endD),
        CONCAT(' ',startDate), CONCAT(' ',endDate)
    FROM #actualUse AS a
      CROSS APPLY
        ( SELECT
              startD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.startHour),
              endD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour),
              p.price
          FROM #calendar AS c
            CROSS JOIN #pricelist AS p
   --       WHERE a.startDate < DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour)
   --         AND a.endDate > DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour)
        ) AS x ; 
        
        select *
        from #pricelist ;

fork query

edited nov 28 14 anonymous

created may 24 10 sam.saffron