Results 1 to 13 of 13

Thread: SQL Views

  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: SQL Views

    My First time building "Views" in SQL...... I'm trying to figure out how to return a 1 instead of a two when I Count the number of records that return inn my view.

    here's what I have;

    SELECT TOP 100 PERCENT dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate,
    COUNT(dbo.tbl_ProcTimesheet.TransAmt) AS Shifts, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.YCode,
    dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink
    FROM dbo.tbl_ProcTimesheet INNER JOIN
    dbo.tbl_SysVarRestEeShiftPayCodes ON dbo.tbl_ProcTimesheet.Code = dbo.tbl_SysVarRestEeShiftPayCodes.PayCode INNER JOIN
    dbo.tbl_SysVarRestEeShiftRules ON dbo.tbl_ProcTimesheet.YCode = dbo.tbl_SysVarRestEeShiftRules.YCode AND
    dbo.tbl_ProcTimesheet.ZCode = dbo.tbl_SysVarRestEeShiftRules.ZCode
    WHERE (dbo.tbl_ProcTimesheet.Sequence <> N'0') AND (dbo.tbl_ProcTimesheet.Week < 3)
    GROUP BY dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.TransAmt,
    dbo.tbl_SysVarRestEeShiftRules.DailyHours, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.XCode,
    dbo.tbl_ProcTimesheet.YCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink, dbo.tbl_ProcTimesheet.Sequence,
    dbo.tbl_ProcTimesheet.Week
    HAVING ('IIf' > '1,1,0') AND ('if' > '1,1') AND (dbo.tbl_ProcTimesheet.erNum LIKE N'5648 ') AND (SUM(dbo.tbl_ProcTimesheet.TransAmt)
    >= dbo.tbl_SysVarRestEeShiftRules.DailyHours) AND (dbo.tbl_ProcTimesheet.PayDate = CONVERT(DATETIME, '2005-09-06 00:00:00', 102)) AND
    (COUNT(dbo.tbl_ProcTimesheet.TransAmt) > 0)
    ORDER BY dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.[Employee Number]

    When it counts shifts, I only want to return a maximum of i, as in either you had a shoft that day, or not. I gave up trying to figure out the "IF" string in SQL, and for the life of me I can not figure this out.

    Does anyone know a good site with info on how to construct an SQL View?

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    My First time building "Views" in SQL......
    Good Luck for the future methinks U'l need it.

    I'm trying to figure out how to return a 1 instead of a two when I Count the number of records that return inn my view.
    Confusion now sets in

    I gave up trying to figure out the "IF" string in SQL
    IF is a Keyword and not SQL and returns a boolean value, maybe you were looking for the CASE Expression

    And WTF is this
    HAVING ('IIf' > '1,1,0') AND ('if' > '1,1') AND .........
    Does this view actually run ?

    When it counts shifts, I only want to return a maximum of i, as in either you had a shoft that day, or not
    Please Clarify (and i don't mean the shaft bit)

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jan 2005
    Posts
    40

    Reply...

    Sorry for the confusion, I'm converting my access db to SQL - far more complex than I had thought.

    In a nutshell, I have a table that sets the rules which determin whether or not an employee has earned a "shift". For example if an employee works less than 4 hours, they have not worked a shift, and therefore do not earn certain benefits.

    My problem is that sometimes an employee has numerous pay records on a single day, so I am trying to consolidate that by summing the hours to test them against the minimum.

    Here is my Access query that I am trying to convert, maybe it will help you understand what I am up to;

    SELECT tbl_ProcTimesheet.idCalendar, tbl_ProcTimesheet.erNum, tbl_ProcTimesheet.PayDate, IIf([TransAmt]>=[DailyHours],1,0) AS Shifts, tbl_ProcTimesheet.[Employee Number], tbl_ProcTimesheet.XCode, tbl_ProcTimesheet.YCode, tbl_ProcTimesheet.ZCode, tbl_ProcTimesheet.eeLink
    FROM (tbl_ProcTimesheet INNER JOIN tbl_SysVarRestEeShiftPayCodes ON tbl_ProcTimesheet.Code = tbl_SysVarRestEeShiftPayCodes.PayCode) INNER JOIN tbl_SysVarRestEeShiftRules ON (tbl_ProcTimesheet.ZCode = tbl_SysVarRestEeShiftRules.ZCode) AND (tbl_ProcTimesheet.YCode = tbl_SysVarRestEeShiftRules.YCode)
    WHERE (((tbl_ProcTimesheet.Sequence)<>"0"))
    GROUP BY tbl_ProcTimesheet.idCalendar, tbl_ProcTimesheet.erNum, tbl_ProcTimesheet.PayDate, IIf([TransAmt]>=[DailyHours],1,0), tbl_ProcTimesheet.[Employee Number], tbl_ProcTimesheet.XCode, tbl_ProcTimesheet.YCode, tbl_ProcTimesheet.ZCode, tbl_ProcTimesheet.eeLink
    HAVING (((tbl_ProcTimesheet.erNum)="5648 ") AND ((IIf([TransAmt]>=[DailyHours],1,0))>0))
    ORDER BY tbl_ProcTimesheet.PayDate, tbl_ProcTimesheet.eeLink;


    All these queries that I have to convert work great in Access, but as I sad converting is a tad more complex than I had envisioned

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Warning: This is a logically slippery slope... Be careful just how far you decide to go down it!

    I'd suggest using:
    Code:
    SELECT
       t.idCalendar, t.erNum, t.PayDate
    ,  CASE WHEN 4 < Sum(DailyHours) THEN 0 ELSE 1 END AS Shifts, t.[Employee Number]
    ,  t.YCode, t.XCode, t.ZCode, t.eeLink
       FROM dbo.tbl_ProcTimesheet AS t
       INNER JOIN dbo.tbl_SysVarRestEeShiftPayCodes AS pc
          ON t.Code = pc.PayCode
       INNER JOIN dbo.tbl_SysVarRestEeShiftRules AS sr
          ON t.YCode = sr.YCode
          AND t.ZCode = sr.ZCode
       WHERE  t.erNum LIKE N'5648 '
          AND t.Sequence <> N'0'
          AND t.PayDate = '2005-09-06'
          AND (t.Week < 3)
       GROUP BY t.idCalendar, t.erNum, t.PayDate
    ,    t.TransAmt, sr.DailyHours, t.[Employee Number]
    ,    t.XCode, t.YCode, t.ZCode
    ,    t.eeLink, t.Sequence, t.Week
       HAVING SUM(t.TransAmt) >= sr.DailyHours
       ORDER BY t.PayDate, t.[Employee Number]
    -PatP
    Last edited by Pat Phelan; 09-26-05 at 23:21.

  5. #5
    Join Date
    Jan 2005
    Posts
    40

    MOre Replies....

    I'm afraid I'm well on my way down that slope, lol

    THis is my revised code;

    SELECT TOP 100 PERCENT dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate,
    dbo.tbl_ProcTimesheet.[Employee Number], COUNT(dbo.tbl_ProcTimesheet.[Employee Number]) AS Shifts, dbo.tbl_ProcTimesheet.YCode,
    dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink
    FROM dbo.tbl_ProcTimesheet INNER JOIN
    dbo.tbl_SysVarRestEeShiftPayCodes ON dbo.tbl_ProcTimesheet.Code = dbo.tbl_SysVarRestEeShiftPayCodes.PayCode INNER JOIN
    dbo.tbl_SysVarRestEeShiftRules ON dbo.tbl_ProcTimesheet.YCode = dbo.tbl_SysVarRestEeShiftRules.YCode AND
    dbo.tbl_ProcTimesheet.ZCode = dbo.tbl_SysVarRestEeShiftRules.ZCode
    WHERE (dbo.tbl_ProcTimesheet.Sequence <> N'0') AND (dbo.tbl_ProcTimesheet.Week < 3)
    GROUP BY dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_SysVarRestEeShiftRules.DailyHours,
    dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.YCode, dbo.tbl_ProcTimesheet.ZCode,
    dbo.tbl_ProcTimesheet.eeLink, dbo.tbl_ProcTimesheet.Sequence, dbo.tbl_ProcTimesheet.Week
    HAVING (dbo.tbl_ProcTimesheet.erNum LIKE N'5648 ') AND (SUM(dbo.tbl_ProcTimesheet.TransAmt) >= dbo.tbl_SysVarRestEeShiftRules.DailyHours) AND
    (COUNT(dbo.tbl_ProcTimesheet.[Employee Number]) > 0)
    ORDER BY dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.[Employee Number]


    I'm going to try yours now, and see hwo it goes...... THANKS by the way

  6. #6
    Join Date
    Jan 2005
    Posts
    40

    Didn't Work :((

    I'm afraid to say that your suggestion returned no records whatsoever......

    I'm starting to see the error in prematurely heading down the SQL path...... What fools we are when we bote off more than we can chew, lol

    Any thoughts on the code I sent? I'm going to tinker with the Case stuff...... I think I understand how Case works, ....... I hope

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Might I suggest following Brett's suggestion on how to get an answer fast? He's distilled the ideas offered by lots of bright folks over a relatively long period of time into one neat, simple post.

    I'd bet if you take the approach he suggests, you'll have a solid working answer in 12 hours or less.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your access query returns this value in the SELECT clause:
    Code:
    IIf([TransAmt]>=[DailyHours],1,0) AS Shifts
    ...but you have this criteria in the HAVING clause:
    Code:
    IIf([TransAmt]>=[DailyHours],1,0)>0
    ...which filters out any cases where the SELECT clause would return 0.
    Doesn't make much sense to me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2005
    Posts
    78
    Have you tried pretty much leaving your original query as it is but replacing

    IIf([TransAmt]>=[DailyHours],1,0)

    with

    CASE WHEN TransAmt >= DailyHours THEN 1 ELSE 0 END

    ?

    The rest of the query looks pretty standard to me at first glance. I make no comment on whether it is a good query or not.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Larry Dumoulin
    Sorry for the confusion, I'm converting my access db to SQL - far more complex than I had thought.

    Not to be a doomsayer, but that's an understatement.

    I can't count the number of home grown, or worse, consultant made "solutions" using Access that then need to be upgraded.

    Almost in every case, Access was employed to support the business model, by "developers" who have little experience in such.

    Complicated and convaluted solutions are made, and then work arounds are forced because a lack of understanding with the original data model.

    How does this help you?

    It doesn't.

    In every case, for every Access/Excel, whatever, I have done a full blown rewrite of the system with a a proper logical model. This gets converted into physical, and the original data is staged as is. A migration plan and, most usually, a data sanitation effort is performed, which is incorporated into migration.

    Long story short, push to re-write the app.

    This is just a small series of things that cause a upgrade to SQL Server to be painful.

    Do you have a nested "Queries" in Access?

    I had one that was 13 levels deep, with three branches....never mind, that's another story
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2005
    Posts
    1
    Why do you use "Select Top 100 Percent"?

    Thanks,

    dB

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Views are not allowed to have ORDER BY statements, and will throw an error if you include one. BUT, there is a loophole: the TOP clause is pointless without an ORDER BY clause, so SQL makes an exception and allows ORDER BY when TOP is specified. Hence, TOP 100% to return all the records and allow the result set to be ordered.

    Goofy, yes...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Brett Kaiser

    Long story short, push to re-write the app.
    Absolutely. The "query designer" in Access is only good for simple queries. For complex queries, it does a poor job and creates almost unreadable SQL. Most DB programmers could rewrite that query from scratch faster than they could fix it, and it would be a better query.

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •