Variable Dates Coorelation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Variable Dates Coorelation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-27 : 07:44:11

Mark writes "This is a doozy to even try to describe but here goes:

The table: 35 columns
1 ID column (int)
16 columns labeled rb1 through rb16 (int)
16 columns labeled rb1a through rb16a (int)
1 startdate column (smalldate)
1 enddate column (smalldate)

Columns rb1 thru rb16 are either 0 or more, likewise for rb1a thru rb16a.

Population on these columns start on the left based on startdate.

If datediff startdate and enddate = 5 then columns rb1-rb6 are populated with some value above 0, the remaining columns stay at 0.

Hence, column rb1 = a quantity on the start date
column rb2 = a quantity on the day after the start date and so forth until the RB columns have values matching the range of days in the datediff on startdate and enddate.

I think this enough to move on to the actual issue:

I want to show a table beginning on Sunday and run thru to Saturday. The query needs to calculate the day of week based on the startdate for that row and sum the values in rb1 thru rb16 for each day of the week.

The setup:
In all cases rb1 is the value of quantity on the startdate for THAT row, successive RB values are based on how many days are derived based on datediff of start and end values. The remaining (unused) RB values are always 0.

My final table i want to look like this:


S   M   T   W   T   F   S
4   3   1   8   9   5   3

Each row will have a different startdate/enddate value.

The row of values above represent TOTALS of all rows, based on the startdate/rb1 alignment. Rb1 will not always be Sunday.

A typical row show below is shown:
id = 100
rb1 = 5
rb2 = 2
rb3 = 2
rb4 = 6
rb5 = 8
rb6 = 3
rb7 = 4
rb8 = 7
rb9 = 9
rb10 = 0
rb11 = 0
rb12 = 0
rb13 = 0
rb14 = 0
rb15 = 0
rb16 = 0
startdate = 1/1/2003
enddate = 1/10/2003

(for berivity I left out the rb1a - rb16a columns)

For clarity rb1 - rb16 are estimates
rb1a - rb16a are actual values for the same period as rb1-rb16

ANYTHING anyone can do to help me understand how to query our MS SQL 2000 server would be appreciated.

Good Day
Mark"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-02-27 : 09:26:37

Your description does not match your example: you state that "If datediff startdate and enddate = 5 then columns rb1-rb6 are populated", but your example has a datediff of 9 and yet only columns rb1-rb9 are populated.

Assuming the first of these is correct (i.e. the period startdate-enddate is inclusive), rather than the second, and that SET DATEFIRST is at its default setting of Sunday (SET DATEFIRST 7), and Numbers is a zero-based tally table that goes up to at least 15...
The test setup:


DROP TABLE Doozy

CREATE TABLE Doozy (
  id int PRIMARY KEY,
  rb1 int NOT NULL,
  rb2 int NOT NULL,
  rb3 int NOT NULL,
  rb4 int NOT NULL,
  rb5 int NOT NULL,
  rb6 int NOT NULL,
  rb7 int NOT NULL,
  rb8 int NOT NULL,
  rb9 int NOT NULL,
  rb10 int NOT NULL,
  rb11 int NOT NULL,
  rb12 int NOT NULL,
  rb13 int NOT NULL,
  rb14 int NOT NULL,
  rb15 int NOT NULL,
  rb16 int NOT NULL,
  startdate datetime NOT NULL,
  enddate datetime NOT NULL
)

INSERT INTO Doozy VALUES
  (100, 5, 2, 2, 6, 8, 3, 4, 7, 9, 0, 0, 0, 0, 0, 0, 0, '2003-01-01', '2003-01-10')

INSERT INTO Doozy VALUES
  (101, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2003-02-01', '2003-02-01')

INSERT INTO Doozy VALUES
  (102, 1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8, '2003-03-01', '2003-03-15')

The query:


SELECT
  SUM(CASE WHEN dw = 1 THEN total ELSE 0 END) AS S,
  SUM(CASE WHEN dw = 2 THEN total ELSE 0 END) AS M,
  SUM(CASE WHEN dw = 3 THEN total ELSE 0 END) AS T,
  SUM(CASE WHEN dw = 4 THEN total ELSE 0 END) AS W,
  SUM(CASE WHEN dw = 5 THEN total ELSE 0 END) AS T,
  SUM(CASE WHEN dw = 6 THEN total ELSE 0 END) AS F,
  SUM(CASE WHEN dw = 7 THEN total ELSE 0 END) AS S
FROM (
    SELECT DATEPART(dw, dt) AS dw, SUM(rb) AS total
    FROM (
        SELECT DATEADD(d, n, startdate) AS dt,
          CASE n+1
            WHEN 1 THEN rb1
            WHEN 2 THEN rb2
            WHEN 3 THEN rb3
            WHEN 4 THEN rb4
            WHEN 5 THEN rb5
            WHEN 6 THEN rb6
            WHEN 7 THEN rb7
            WHEN 8 THEN rb8
            WHEN 9 THEN rb9
            WHEN 10 THEN rb10
            WHEN 11 THEN rb11
            WHEN 12 THEN rb12
            WHEN 13 THEN rb13
            WHEN 14 THEN rb14
            WHEN 15 THEN rb15
            WHEN 16 THEN rb16
          END AS rb
        FROM Doozy
        INNER JOIN Numbers
          ON n BETWEEN 0 AND DATEDIFF(d, startdate, enddate)
      ) AS A
    GROUP BY DATEPART(dw, dt)
  ) AS A

Actually, it's probably worth taking out the intermediate grouping by day of week since it's not necessary:


SELECT
  SUM(CASE WHEN dw = 1 THEN rb ELSE 0 END) AS S,
  SUM(CASE WHEN dw = 2 THEN rb ELSE 0 END) AS M,
  SUM(CASE WHEN dw = 3 THEN rb ELSE 0 END) AS T,
  SUM(CASE WHEN dw = 4 THEN rb ELSE 0 END) AS W,
  SUM(CASE WHEN dw = 5 THEN rb ELSE 0 END) AS T,
  SUM(CASE WHEN dw = 6 THEN rb ELSE 0 END) AS F,
  SUM(CASE WHEN dw = 7 THEN rb ELSE 0 END) AS S
FROM (
    SELECT DATEPART(dw, DATEADD(d, n, startdate)) AS dw,
      CASE n+1
        WHEN 1 THEN rb1
        WHEN 2 THEN rb2
        WHEN 3 THEN rb3
        WHEN 4 THEN rb4
        WHEN 5 THEN rb5
        WHEN 6 THEN rb6
        WHEN 7 THEN rb7
        WHEN 8 THEN rb8
        WHEN 9 THEN rb9
        WHEN 10 THEN rb10
        WHEN 11 THEN rb11
        WHEN 12 THEN rb12
        WHEN 13 THEN rb13
        WHEN 14 THEN rb14
        WHEN 15 THEN rb15
        WHEN 16 THEN rb16
      END AS rb
    FROM Doozy
    INNER JOIN Numbers
      ON n BETWEEN 0 AND DATEDIFF(d, startdate, enddate)
  ) AS A

Edited by - Arnold Fribble on 02/27/2003 09:48:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources