Range from Thursday to Wednesday pivot break down to two rows

1

Hi.
I need to range a week from Thursday to Wednesday on a pivot. The issue I am having is that with my current tsql, if a date falls into a different week, it will split to two rows.
So if for example I have a date of 16 that falls to a previous week and a data of 18 that falls into this week. They will split to 2 rows.
What I want to do is to get this days to fall into the Thursday to Wednesday range. They may be on different weeks but if a week starts from Thursday and ends at Wednesday then they fall into the "same week" so they should be in one row.
Here is my TSQL.

SELECT WeekinMonthStart,WeekinMonthEnd,
      [MarketFilmCode]
      ,[MarketFilmName]
	    ,[MarketArea]            
       ,[Thursday] 
       ,[Friday]
	   ,[Saturday]
       ,[Sunday]
       ,[Monday] 
       ,[Tuesday] 
       ,[Wednesday]  
FROM  
(select  marketfilmname,MarketBO,[MarketFilmCode],[MarketArea],datename(WEEKDAY,marketcinemadate) as DAY
,DATEADD(wk, DATEDIFF(wk, 3, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)-2), 3)  as WeekinMonthStart,
DATEADD(wk, DATEDIFF(wk, 2, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)- 1), 2)  as WeekinMonthEnd
  from vwDWMarketData) as p
      PIVOT ( 
        SUM (MarketBO) 
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt

I can see that this is wrong as it will only stay correct if they days fall in to the same "real" week but I can't find some example to fix this.
Thanks.

2

HI.
In addition and since we are in pivots,
I want to add more columns with another pivot.
The below will bring me null for marketbo.

SELECT WeekinMonthStart,WeekinMonthEnd,
      [MarketFilmCode]
      ,[MarketFilmName]
	    ,[MarketArea]
	--	,MarketBO
	--	,MarketBO as MArketBO1            
       ,[Thursday] 
       ,[Friday]
	   ,[Saturday]
       ,[Sunday]
       ,[Monday] 
       ,[Tuesday] 
       ,[Wednesday]
	   ,[ThursdayA] 
       ,[FridayA]
	   ,[SaturdayA]
       ,[SundayA]
       ,[MondayA] 
       ,[TuesdayA] 
       ,[WednesdayA]    
FROM  
(select  marketfilmname,MarketAdmissions,[MarketFilmCode],[MarketArea],MarketBO ,datename(WEEKDAY,marketcinemadate) as DAY,datename(WEEKDAY,marketcinemadate) as DAY1
,DATEADD(wk, DATEDIFF(wk, 3, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)-2), 3)  as WeekinMonthStart,
DATEADD(wk, DATEDIFF(wk, 2, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)- 1), 2)  as WeekinMonthEnd
  from vwDWMarketData) as p
      PIVOT ( 
        SUM (MarketAdmissions)
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt 

	   PIVOT 
	   (
	   SUM (MArketBO)
	   FOR DAY1 IN (
	    [ThursdayA], [FridayA],[SaturdayA], [SundayA], [MondayA], [TuesdayA], [WednesdayA]))  pvt2
3

Something like:

 SELECT *
	--	,MarketBO
	--	,MarketBO as MArketBO1            
   /*    ,[Thursday] 
       ,[Friday]
	   ,[Saturday]
       ,[Sunday]
       ,[Monday] 
       ,[Tuesday] 
       ,[Wednesday]
	   ,[ThursdayA] 
       ,[FridayA]
	   ,[SaturdayA]
       ,[SundayA]
       ,[MondayA] 
       ,[TuesdayA] 
       ,[WednesdayA]    */
FROM  
(select  marketfilmname,MarketAdmissions,[MarketFilmCode],[MarketArea],MarketBO ,datename(WEEKDAY,marketcinemadate) as DAY, 'p' + datename(WEEKDAY,marketcinemadate) as DAY1
,DATEADD(wk, DATEDIFF(wk, 3, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)-2), 3)  as WeekinMonthStart,
DATEADD(wk, DATEDIFF(wk, 2, '1/1/' + datename(YEAR,marketcinemadate)) + (datename(WEEK,marketcinemadate)- 1), 2)  as WeekinMonthEnd
  from vwDWMarketData) as p
      PIVOT ( 
        SUM (MarketAdmissions)
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt 

	   PIVOT 
	   (
	   SUM (MArketBO)
	   FOR DAY1 IN (
	    [pThursday], [pFriday],[pSaturday], [pSunday], [pMonday], [pTuesday], [pWednesday]))  pvt2

but this ungroups everything, and I need to group it again.

4

So.
As I can't find something that will do the from-to dateadd and will not split to more rows, I will be inserting a reference starting date (that is always Thursday) into a column and I split three needed tables into different pivots and insert to different temp tables.
I then join the tables so I could get the result.
If there is a better solution (as I am forced to call the main table 3 times) please let me know.

This is the code:

declare @datefrom as date
	declare @dateto as date
	set @datefrom = '20180913'
	set @dateto = '20180919'
	
	SELECT * INTO #TempMarketBO FROM(
	  SELECT
      [MarketFilmCode]
      ,[MarketFilmName]
	  ,[MarketArea]
	  ,[MarketFilmFormat]
      ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]            
       ,[Thursday] as  BOThursday
       ,[Friday] as BOFriday
	   ,[Saturday] as BOSaturday
       ,[Sunday] as BOSunday
       ,[Monday] as BOMonday
       ,[Tuesday] as BOTuesday
       ,[Wednesday]  as BOWednesday
FROM  
(select  marketfilmname,MarketBO,[MarketFilmCode],[MarketArea] ,[MarketFilmFormat]
      ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]   ,datename(WEEKDAY,marketcinemadate) as DAY	
  from vwDWMarketData 
    where marketcinemadate between @datefrom and @dateto
	) as p
      PIVOT ( 
        SUM (MarketBO) 
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt ) as x

--	  	

	select * into #TempMarketAD FROM(
	   	  SELECT
      [MarketFilmCode]
      ,[MarketFilmName]
	  ,[MarketArea]
	  ,[MarketFilmFormat]
	  ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]            
       ,[Thursday] as  ADThursday
       ,[Friday] as ADFriday
	   ,[Saturday] as ADSaturday
       ,[Sunday] as ADSunday
       ,[Monday] as ADMonday
       ,[Tuesday] as ADTuesday
       ,[Wednesday]  as ADWednesday
FROM  
(select  marketfilmname,MarketAdmissions,[MarketFilmCode],[MarketArea] ,[MarketFilmFormat]
      ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]   ,datename(WEEKDAY,marketcinemadate) as DAY
  from vwDWMarketData
    where marketcinemadate between @datefrom and @dateto
  ) as p
      PIVOT ( 
        SUM (MarketAdmissions) 
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt) as x


	
		select * into #TempMarketVL FROM(
	   	  SELECT
      [MarketFilmCode]
      ,[MarketFilmName]
	  ,[MarketArea]
	  ,[MarketFilmFormat]
	  ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]            
       ,[Thursday] as  VAThursday
       ,[Friday] as VAFriday
	   ,[Saturday] as VASaturday
       ,[Sunday] as VASunday
       ,[Monday] as VAMonday
       ,[Tuesday] as VATuesday
       ,[Wednesday]  as VAWednesday
FROM  
(select  marketfilmname,VAdmissions,[MarketFilmCode],[MarketArea] ,[MarketFilmFormat]
      ,[MarketOVDUB]
      ,[MarketWeekFilm]
      ,[MarketScreens]   ,datename(WEEKDAY,marketcinemadate) as DAY
  from vwDWMarketData
    where marketcinemadate between @datefrom and @dateto
  ) as p
      PIVOT ( 
        SUM (VAdmissions) 
        FOR DAY IN ( 
       [Thursday], [Friday],[Saturday], [Sunday], [Monday], [Tuesday], [Wednesday]) ) pvt ) as x


	  select  BO.MarketFilmCode,BO.MarketFilmName,BO.MarketArea,BO.MarketFilmFormat,BO.MarketOVDUB,BO.MarketWeekFilm,BO.MarketScreens
              ,BOThursday,BOFriday,BOSaturday,BOSunday,BOMonday,BOTuesday,BOWednesday
			  ,ADThursday,ADFriday,ADSaturday,ADSunday,ADMonday,ADTuesday,ADWednesday
			  ,VAThursday,VAFriday,VASaturday,VASunday,VAMonday,VATuesday,VAWednesday
	          from #TempMarketBO BO inner join #TempMarketAD AD on BO.MarketFilmCode= AD.MarketFilmCode and BO.MarketArea= AD.MarketArea
			  and BO.MarketFilmFormat= AD.MarketFilmFormat and BO.MarketOVDUB = AD.MarketOVDUB
			  inner join #TempMarketVL VL on AD.MarketFilmCode = VL.MarketFilmCode and AD.MarketArea = VL.MarketArea 
			  and AD.MarketFilmFormat = VL.MarketFilmFormat and AD.MarketOVDUB = VL.MarketOVDUB

			  drop table #TempMarketBO
			  drop table #TempMarketAD
			  drop table #TempMarketVL
5

we don't know what vwDWMarketData is. Can you supply DDL, data and expected results?

6

Hi.
It is a view that contains the aforementioned and other view joins.

select
 MT.MarketFilmCode, MF.MarketFilmName, MT.MarketCinemaDate, MT.MarketArea, 
            MT.MarketFilmFormat, MT.MarketOVDUB, 
            MT.MarketWeekFilm,MarketReferenceDate,
            SUM(MT.MarketScreens) AS MarketScreens, 
            SUM(MT.MarketAdmissions) AS MarketAdmissions, 
            SUM(MT.MarketBO) AS MarketBO,
            SUM(VT.Admissions) AS VAdmissions         
    FROM [dbo].[tblDWMarketTicket] AS MT
    INNER JOIN [dbo].[tblDWMarketFilm] AS MF ON MT.[MarketFilmCode] = MF.[MarketFilmCode] 
    LEFT OUTER JOIN [dbo].[vwDWTicketsForMarket] AS VT ON VT.MarketFilmCode = MT.MarketFilmCode
                                                         AND VT.MarketFilmFormat = MT.MarketFilmFormat
                                                         AND VT.MarketArea = MT.MarketArea
                                                         AND VT.MarketOVDUB = MT.MarketOVDUB
                                                         AND VT.ShowCinemaDate = MT.MarketCinemaDate
    GROUP BY MT.MarketFilmCode, MF.MarketFilmName, MT.MarketCinemaDate, MT.MarketArea, 
            MT.MarketFilmFormat, MT.MarketOVDUB, 
            MT.MarketWeekFilm,MarketReferenceDate

I don't think it makes a difference. If i have the main idea-plan of how to do what I try I can convert.

7

it makes a difference to people trying to help you. It doesn't matter if it's a view or table, you can provide ddl and sample data as well as expected results and we can try to help. Otherwise, we have to try to create that for you and, since this a volunteer site and we all (presumably) have other jobs, it's less likely you'll get any help without it

8

Right,
No problem. I will not use this for now.
So I will create a new thread if I have an issue.
Thanks.