Converting DatePart and grouping on one line

Question

I'm doing my best to turn the days of the week into X so I can mark them on a weekly schedule - combining them on ONE LINE is my crux.

Expected output:

AgrmntID    Description    RepairID    Su  M  Tu  W  Th  F  Sa
2           Landscaping    2                  X
3           Landscaping    2               X  X   X  X   X  X

Current output:

AgrmntID    Description    RepairID
2           Landscaping    2

Current code:

Select Agreements.AgrmntID, LaborCodeTypes.Description,
  AgreementSchedules.RepairID
From Agreements Inner Join
  AgreementSchedules On Agreements.AgrmntID = AgreementSchedules.AgrmntID
  Inner Join
  LaborCodeTypes On LaborCodeTypes.RepairID = AgreementSchedules.RepairID
  Inner Join
  (Select AgreementSchedules.AgrmntID, AgreementSchedules.RepairID, Case
        When DatePart(dw, AgreementSchedules.SchedDate) = 1 Then 'X'
      End As Sunday
    From AgreementSchedules
    Where AgreementSchedules.RepairID = 2
    Union
    Select AgreementSchedules.AgrmntID, AgreementSchedules.RepairID, Case
        When DatePart(dw, AgreementSchedules.SchedDate) = 2 Then 'X'
      End As Monday
    From AgreementSchedules
    Where AgreementSchedules.RepairID = 2
    Union
    Select AgreementSchedules.AgrmntID, AgreementSchedules.RepairID, Case
        When DatePart(dw, AgreementSchedules.SchedDate) = 3 Then 'X'
      End As Tuesday
    From AgreementSchedules
    Where AgreementSchedules.RepairID = 2) Sched On Sched.AgrmntID =
    Agreements.AgrmntID
Group By Agreements.AgrmntID, LaborCodeTypes.Description,
  AgreementSchedules.RepairID
Having AgreementSchedules.RepairID = 2

I have many examples of how I've failed, if that helps you solve this. Any tips would be greatly appreciated - thank you in advance!!

score 2 · Answer 1 · answered Sep 03 '13 at 23:35

2

If you provide CREATE TABLE scripts and sample data, I can vet this, but at first scratch:

SELECT
    A.AgrmntID,
    LCT.Description,
    S.RepairID,
    Days.Sunday, Days.Monday, Days.Tuesday, Days.Wednesday, Days.Thursday, Days.Friday, Days.Saturday
FROM
    Agreements AS A
    INNER JOIN AgreementSchedules AS S ON A.AgrmntID = S.AgrmntID
    INNER JOIN LaborCodeTypes AS LCT ON S.RepairID = LCT.RepairID
    INNER JOIN
        (
        SELECT
            AgrmntID, RepairID, "1" AS Sunday, "2" AS Monday, "3" AS Tuesday, "4" AS Wednesday, "5" AS Thursday, "6" AS Friday, "7" AS Saturday
        FROM
            (
            SELECT DISTINCT
                AgrmntD, RepairID, DATEPART(WEEKDAY, SchedDate) AS DayOfWeek
            FROM
                AgreementSchedules
            ) AS X
        PIVOT
            (MIN(AgrmntID) FOR DayOfWeek IN ("1", "2", "3", "4", "5", "6", "7")) AS Y
        ) AS Days ON A.AgrmntID = Days.AgrmntID AND S.RepairID = Days.RepairID

answered Sep 03 '13 at 23:35

...any chance you could point me to a good spot to learn how to provide CREATE TABLE and sample data?? I'm not very good at asking questions on here... also, my program is hooking up at Pivot. – Jamie S Sep 04 '13 at 00:12
If you're using SQL Server Management Studio, you can right-click a table and select `Script Table as` > `CREATE To` to get a copy of the table's DDL. This helps us understand what fields you have, and their relationships - not always necessary, for simple queries, but usually helpful. For sample data, `SELECT TOP 10 * FROM` for each table is usually fine. – Sep 04 '13 at 14:33
Hooking up? Is there an error message, or is this something I'll understand when I'm older? `PIVOT` was added in SQL 2K5; did you upgrade to 2K5 from an older version? If so, it's possible you're on an older compatibility level (http://stackoverflow.com/a/1501638/565869). – Sep 04 '13 at 14:36
haha - hooking up is normally great (though irresponsible and often complicated) but I'm getting: `'Invalid SELECT statement. Unexpected token "PIVOT" at...'` I ran the compatibility and it's 90... though the SQL is within another program and sometimes has less functionality (I failed at doing this same thing with coalesce). I tried a real simple `PIVOT` and am having the same issue. – Jamie S Sep 04 '13 at 19:04
K, it may be a problem with the wrapping program or I may have a glitch in my query. Can you run it directly in SQL Studio? We need to cut out the middle man to find out if that's the problem. If it is, you'll need benisntfunny's solution. – Sep 04 '13 at 21:11
Unfortunately I don't have SQL Studio... I'm certain it's this software (again - the SQL is built into it and it's limited). Support said it won't accommodate PIVOT... any chance you have any more ideas?? – Jamie S Sep 09 '13 at 23:42

benisntfunny · Answer 2 · 2013-09-04T00:41:00.240

1

SELECT  id as ID,
        descr as DESCRIPTION,
        REPAIRID AS repairId,
        CASE WHEN "1" IS NOT NULL 
        THEN 'X'
        ELSE ''
        END AS Sunday,
        CASE WHEN "2" IS NOT NULL 
        THEN 'X'
        ELSE ''
        END AS Monday,
        CASE WHEN "3" IS NOT NULL 
        THEN 'X'
        ELSE ''
        END AS Tuesday,
        CASE WHEN "4" IS NOT NULL 
        THEN 'X'
        ELSE ''
        END AS Wednesday,
        CASE WHEN "5" IS NOT NULL
        THEN 'X'
        ELSE ''
        END AS Thursday,
        CASE WHEN "6" IS NOT NULL
        THEN 'X'
        ELSE ''
        END AS Friday,
        CASE WHEN "7" IS NOT NULL
        THEN 'X'
        ELSE ''
        END AS Saturday
FROM (
SELECT id "Junk",
       id, 
       descr,
       repairid,
       DATEPART(dw,someday) "Day"
--etc to Saturday
FROM myTestTable) tbl1
PIVOT 
(
    AVG(Junk)
    FOR Day IN ([1],[2],[3],[4],[5],[6],[7])
) as pvt

update edit: that will work.

edited Sep 04 '13 at 00:41

answered Sep 03 '13 at 23:37

benisntfunny

103
4

Thank you!! That works, but doesn't consolidate like RepairIDs to one line (eg: for service on multiple days per week). – Jamie S Sep 03 '13 at 23:46
Yeah I'm fixing that now though without looking further I bet the guy above me has done it right. – benisntfunny Sep 03 '13 at 23:47
My program doesn't like the PIVOT as it's an "unexpected token." – Jamie S Sep 04 '13 at 00:13
My guess is that his example is more complex (making assumption) than you're able to translate to your code. See my updated one. It will work. (Hopefully) :) – benisntfunny Sep 04 '13 at 00:40
I'm using SQL within another program and it can't do some things... I have a feeling it can't do PIVOT because I just tried a very simple PIVOT and I'm getting the same error message: 'Invalid SELECT statement. Unexpected token "PIVOT" at...' So - back to square one :( – Jamie S Sep 04 '13 at 18:55

score 0 · Answer 3 · answered Sep 03 '13 at 23:32

0

Just from a quick glance, your not including your days of the week in your select statement. Your joins won't return anything unless you explicitly tell them to.

answered Sep 03 '13 at 23:32

Bill Hatter

165
5

I had tried: Select Agreements.AgrmntID, LaborCodeTypes.Description, AgreementSchedules.RepairID, Sched.Sunday, Sched.Monday, Sched.Tuesday ...but to no avail. – Jamie S Sep 03 '13 at 23:35

Converting DatePart and grouping on one line

3 Answers3