| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2014-12-03 : 06:10:26
|
Hi. I haven't used pivot so i am not sure if this is the way to go.
I have a result such like
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 17:00
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 19:00
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 21:00
etc
So this will bring titles and their playtime.
What i need to do is Have everything as is but the time.
So it will be something like:
27.11.2014 - 13.03.2014, CinemaX, TitleX ,17:00,19:00,21:00
This should be per hour so it will have 12 hour columns(or 24 but i am not yet sure about that, anyhow that is the least of the issue).
So is there a way to accomplish that?
Thanks.
declare @datefrom datetime,@dateto datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'
SELECT
convert(varchar, @datefrom, 104) + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName,
convert(varchar,Session_dtmRealShow,106) as Show_date,
convert(char(5), Session_dtmRealShow, 108) as Show_time
-- S.Screen_bytNum ,
--S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
--D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
and (convert(char(5), Session_dtmRealShow, 108) >= '15:00:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59:00')
and co.cinoperator_strcode = 'FM'
and s.screen_bytNum in(4,5)
Group by
-- S.Screen_bytNum ,
Screen_strName,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname
--ORDER BY Session_dtmRealShow ,S.Screen_bytNum
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2014-12-04 : 05:11:00
|
I was thinking, if that is not possible, to create 2 steps.
One to bring everything, till the tickets and the other to pivot the tickets.
Then, somehow to merge the two sets but this is out of my league.
Thanks. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2014-12-04 : 08:55:24
|
or doing:
[CODE]
declare @datefrom datetime,@dateto datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'
SELECT
convert(varchar, @datefrom, 104) + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName,
convert(varchar,Session_dtmRealShow,106) as Show_date,
convert(char(5), Session_dtmRealShow, 108) as Show_time1,
convert(char(5), Session_dtmRealShow, 108) as Show_time2,
convert(char(5), Session_dtmRealShow, 108) as Show_time3,
convert(char(5), Session_dtmRealShow, 108) as Show_time4,
convert(char(5), Session_dtmRealShow, 108) as Show_time5,
convert(char(5), Session_dtmRealShow, 108) as Show_time6,
convert(char(5), Session_dtmRealShow, 108) as Show_time7,
convert(char(5), Session_dtmRealShow, 108) as Show_time8,
convert(char(5), Session_dtmRealShow, 108) as Show_time9,
convert(char(5), Session_dtmRealShow, 108) as Show_time10,
-- S.Screen_bytNum ,
--S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
--D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
and (convert(char(5), Session_dtmRealShow, 108) >= '15:00:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59:00')
and co.cinoperator_strcode = 'FM'
and s.screen_bytNum in(4,5)
Group by
-- S.Screen_bytNum ,
Screen_strName,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname
--ORDER BY Session_dtmRealShow ,S.Screen_bytNum
[/CODE]
and then calculate if showtimes. Get the smallest showtime and then find if there is a next showtime and if so, put it to the next column, then take that showtime and if a next showtime exists then put it to the next column, etc.
Possible?
|
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2014-12-05 : 03:53:37
|
I was advice to do this:
declare @datefrom datetime,@dateto datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'
Create Table #PL (Session_lngSessionId int, ProvSeq int, weekd varchar(100),
Cinema_strname nvarchar(100)
,showdate varchar(100)
,timein nvarchar(100),
primary key (Session_lngSessionId))
Insert into #PL
select Session_lngSessionId,row_number() over (partition by Session_lngSessionId order by convert(char(5), Session_dtmRealShow, 108))
,convert(varchar, @datefrom, 104) + ' - ' + convert(varchar, @dateto, 104) as week
,TC.Cinema_strname
,convert(varchar,Session_dtmRealShow,106) as Show_date
,convert(char(5), Session_dtmRealShow, 108) as Show_time
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
-- excel has time between 3 to 11:59??
and (convert(char(5), Session_dtmRealShow, 108) >= '15:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59')
---poli specific edo all etsi to exei sto excel..
and co.cinoperator_strcode = 'FALM'
and s.screen_bytNum in(4,5)
Group by
-- S.Screen_bytNum ,
Screen_strName,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,
session_lngSessionId
SELECT s.Session_lngSessionId,
convert(varchar, @datefrom, 104) + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName,
convert(varchar,Session_dtmRealShow,106) as Show_date,
--convert(char(5), Session_dtmRealShow, 108) as Show_time
PL1.timein
,PL2.timein
,PL3.timein
,PL4.timein
,PL5.timein
,PL6.timein
,PL7.timein
,PL8.timein
,PL9.timein
-- ,S.Screen_bytNum
--, S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode
--,D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
left join #PL PL1 on PL1.Session_lngSessionId=S.Session_lngSessionId and PL1.ProvSeq=1
left Join #PL PL2 on PL2.Session_lngSessionId=S.Session_lngSessionId and PL2.ProvSeq=2
Left Join #PL PL3 on PL3.Session_lngSessionId=S.Session_lngSessionId and PL3.ProvSeq=3
Left Join #PL PL4 on PL4.Session_lngSessionId=S.Session_lngSessionId and PL4.ProvSeq=4
Left Join #PL PL5 on PL5.Session_lngSessionId=S.Session_lngSessionId and PL5.ProvSeq=5
Left Join #PL PL6 on PL6.Session_lngSessionId=S.Session_lngSessionId and PL6.ProvSeq=6
Left Join #PL PL7 on PL7.Session_lngSessionId=S.Session_lngSessionId and PL7.ProvSeq=7
Left Join #PL PL8 on PL8.Session_lngSessionId=S.Session_lngSessionId and PL8.ProvSeq=8
Left Join #PL PL9 on PL9.Session_lngSessionId=S.Session_lngSessionId and PL9.ProvSeq=9
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
-- excel has time between 3 to 11:59??
and (convert(char(5), Session_dtmRealShow, 108) >= '15:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59')
---poli specific edo all etsi to exei sto excel..
and co.cinoperator_strcode = 'FALM'
and s.screen_bytNum in(4,5)
Group by
-- S.Screen_bytNum ,
PL1.timein,PL2.timein
,PL3.timein
,PL4.timein
,PL5.timein
,PL6.timein
,PL7.timein
,PL8.timein
,PL9.timein,
Screen_strName,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,
s.session_lngSessionId
--ORDER BY Session_dtmRealShow ,S.Screen_bytNum
drop table #PL
put i get null,null,null in the extra columns.Also even if i got the times, there is a date value that will create extra rows,
|
|
|
|
|
|
|
pivot specific rows