Re: Weekdays - a solution

• Previous message: Colin Basterfield: "DTS"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 25 Jun 2004 20:01:52 +1200

This works, can anyone think of a more efficient way? Or perhaps by adding
some indexes?

select Q1.revenue_id, MonCount, MonTotal, TueCount, TueTotal, WedCount,
WedTotal
       ThuCount, ThuTotal, FriCount, FriTotal, SatCount, SatTotal, SunCount,
SunTotal
from
(select d1.revenue_id, d1.site_id,
        d1.revenue_count as MonCount, d1.revenue_total as MonTotal,
        Q2.TueCount, Q2.TueTotal, Q2.WedCount, Q2.WedTotal, Q2.ThuCount,
Q2.ThuTotal,
        Q2.FriCount, Q2.FriTotal, Q2.SatCount, Q2.SatTotal, Q2.SunCount,
Q2.SunTotal
   from daily_total d1,
        (select d2.revenue_id as tue_rev_id, d2.site_id as tue_site_id,
                d2.revenue_count as TueCount, d2.revenue_total as TueTotal,
                Q3.WedCount, Q3.WedTotal, Q3.ThuCount, Q3.ThuTotal,
                Q3.FriCount, Q3.FriTotal, Q3.SatCount, Q3.SatTotal,
                Q3.SunCount, Q3.SunTotal
   from daily_total d2,
              (select d3.revenue_id as wed_rev_id, d3.site_id as
wed_site_id,
                      d3.revenue_count as WedCount, d3.revenue_total as
WedTotal,
                      Q4.ThuCount, Q4.ThuTotal, Q4.FriCount, Q4.FriTotal,
                      Q4.SatCount, Q4.SatTotal, Q4.SunCount, Q4.SunTotal
           from daily_total d3,
                      (select d4.revenue_id as thu_rev_id, d4.site_id as
thu_site_id,
                              d4.revenue_count as ThuCount, d4.revenue_total
as ThuTotal,
                              Q5.FriCount, Q5.FriTotal, Q5.SatCount,
Q5.SatTotal,
         Q5.SunCount, Q5.SunTotal
                         from daily_total d4,
                              (select d5.revenue_id as fri_rev_id,
d5.site_id as fri_site_id,
                                      d5.revenue_count as FriCount,
d5.revenue_total as FriTotal,
                                      Q6.SatCount, Q6.SatTotal, Q6.SunCount,
Q6.SunTotal
                                 from daily_total d5,
                                      (select d6.revenue_id as sat_rev_id,
d6.site_id as sat_site_id,
                                              d6.revenue_count as SatCount,
d6.revenue_total as SatTotal,
                                              Q7.SunCount, Q7.SunTotal
                                         from daily_total d6,
                                              (select d7.revenue_id as
sun_rev_id, d7.site_id as sun_site_id,
                                                      d7.revenue_count as
SunCount, d7.revenue_total as SunTotal
                                                 from daily_total d7
                                                where d7.total_date =
CONVERT(DATETIME,'06/20/2004')) Q7
                                        where d6.total_date =
CONVERT(DATETIME,'06/19/2004')
                                          and d6.revenue_id = Q7.sun_rev_id
                                          and d6.site_id = Q7.sun_site_id)
Q6
                                where d5.total_date =
CONVERT(DATETIME,'06/18/2004')
                                  and d5.revenue_id = Q6.sat_rev_id
                                  and d5.site_id = Q6.sat_site_id) Q5
                        where d4.total_date = CONVERT(DATETIME,'06/17/2004')
                          and d4.revenue_id = Q5.fri_rev_id
                          and d4.site_id = Q5.fri_site_id) Q4
               where d3.total_date = CONVERT(DATETIME,'06/16/2004')
                 and d3.revenue_id = Q4.thu_rev_id
                 and d3.site_id = Q4.thu_site_id) Q3
  where d2.total_date = CONVERT(DATETIME,'06/15/2004')
           and d2.revenue_id = Q3.wed_rev_id
           and d2.site_id = Q3.wed_site_id) Q2
  where d1.total_date = CONVERT(DATETIME,'06/14/2004')
    and d1.revenue_id = Q2.tue_rev_id
    and d1.site_id = Q2.tue_site_id
    and d1.site_id = 5) Q1
order by Q1.revenue_id

"Colin Basterfield" <colin.basterfield@xtra.co.nz> wrote in message
news:lzOCc.3784$LT3.144362@news.xtra.co.nz...
> I have a table which holds Revenue Items against a Site:
>
> [DAILY_TOTAL] (
> [SITE_ID] [smallint] NOT NULL ,
> [REVENUE_ID] [smallint] NOT NULL ,
> [TOTAL_DATE] [datetime] NOT NULL ,
> [WEEK_END_DATE] [datetime] NOT NULL ,
> [REVENUE_COUNT] [smallint] NOT NULL ,
> [REVENUE_TOTAL] [smallmoney] NOT NULL
> )
>
> I want to build a query that gives me the following columns assuming the
> week_end_date used is 06/20/2004 and whatever site is chosen:
>
> 06/14/2004 Count,06/14/2004 Total,06/15/2004 Count,06/15/2004
> Total,06/16/2004 Count,06/16/2004 Total etc, up til 06/20/2004.
>
> The rows will be each Revenue item associated with the site entered,
> indicated by the SITE_ID/REVENUE_ID pair.
>
> Is this possible?
>
> Thanks in advance
> Colin B
>
>

• Previous message: Colin Basterfield: "DTS"
• Messages sorted by: [ date ] [ thread ]