Post reply

Newbie needs some T-SQL help, please

  • March 20, 2007 at 3:59 am

    #116531

    I have a view written that extracts part number and qty booked as scrap from our inventory tables.

    It gives me :-

    Partno, Yearbooked, monthbooked, Qty, scrap reason, supplier

    From this view I want to create another View that sums up the qty of each part number and places it into a column that's determined from the monthbooked relation of each record to the DATEPART(m, GETDATE()).

    So I end up with a qty for CurrentMonth, Month-1, Month-2, Month-3 etc. etc. against each part number record.

    I can get the CurrentMmonth column to work but don't understand how to create the previous month columns.

    My Goal is to have a rolling months qty values to report out through Crystal reports as a Graph based on the Qty Data.

    Any Help Appreciated

    Craig Lloyd

  • March 20, 2007 at 5:18 am

    #695879

    Best would be if you provided an example of the table with columns involved, some rows of testdata that would act as prescribed, and what the desired output from that data would look like.

    /Kenneth

  • March 20, 2007 at 6:41 am

    #695897

    How far back ya wanna go on the Month-x columns?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 20, 2007 at 7:26 am

    #695914

    Hi,

    thanks for the replies, I want to go back 12 months from current to effectively give a rolling 13 months we can report to our suppliers, performance over the last 12 months to give accumulative scores and PPM (parts per million).

    Here's some sample data from our ScrapRecords view :

    StockCodeDescriptionWarehouseTrnYearTrnMonthEntryDateTrnQtyTrnTypeReferenceSupplierGlCode
    64430543HP HOSE 507mm                 RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430543HP HOSE 507mm                 RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430543HP HOSE 507mm                 RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430554END FIT, BLOCK                RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430554END FIT, BLOCK                RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430555END FIT, BANJO                RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430556ORIFICE                       RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430561SPIRAL TUBE 170mm - press cut RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430561SPIRAL TUBE 170mm - press cut RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430561SPIRAL TUBE 170mm - press cut RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430561SPIRAL TUBE 170mm - press cut RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430563SOCKET, OUTER                 RM2007125/01/2007-2AHP3-OT   HCL-EUR510111
    64430563SOCKET, OUTER                 RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    64430563SOCKET, OUTER                 RM2007125/01/2007-2AHP3-OT   HCL-EUR510111
    64430563SOCKET, OUTER                 RM2007125/01/2007-1AHP3-OT   HCL-EUR510111
    74450655HOSE,HP (356mm)               RM2007125/01/2007-5ABH1-MS   HCL-EUR510111
    74450655HOSE,HP (356mm)               RM2007125/01/2007-3ABH1-CL   HCL-EUR510111
    74450656HOSE,HP (301mm)               RM2007125/01/2007-3ABH1-DC   HCL-EUR510111
    74450656HOSE,HP (301mm)               RM2007125/01/2007-4ABH1-DF   HCL-EUR510111
    74450657HOSE,HP (301mm) - PAINT MARKEDRM2007125/01/2007-5ABH1-DE   HCL-EUR510111
    74450657HOSE,HP (301mm) - PAINT MARKEDRM2007125/01/2007-1ABH1-OR   HCL-EUR510111
    74450659HOSE,HP (157mm)               RM2007125/01/2007-1ABH4-OT   HCL-EUR510111
    74450659HOSE,HP (157mm)               RM2007125/01/2007-1ABH4-CLA  HCL-EUR510111
    74450659HOSE,HP (157mm)               RM2007125/01/2007-1ABH4-OT   HCL-EUR510111
    74450659HOSE,HP (157mm)               RM2007125/01/2007-1ABH4-SF   HCL-EUR510111
    74450659HOSE,HP (157mm)               RM2007125/01/2007-2ABH4-OT   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-SL   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-OT   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-CL   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-OT   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-SF   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-2ABH4-OT   HCL-EUR510111
    74450662HOSE,HP (237.5mm)             RM2007125/01/2007-1ABH4-SL   HCL-EUR510111
    74471516CENTRE FITTING                RM2007125/01/2007-2ABH4-OT   HCL-EUR510111
    74471516CENTRE FITTING                RM2007125/01/2007-1ABH4-CL   HCL-EUR510111
    74471516CENTRE FITTING                RM2007125/01/2007-1ABH4-OT   HCL-EUR510111
    74471516CENTRE FITTING                RM2007125/01/2007-1ABH4-SF   HCL-EUR510111
    74471516CENTRE FITTING                RM2007125/01/2007-1ABH4-SL   HCL-EUR510111
    74471532CENTRE FITTING                RM2007125/01/2007-1ABH4-SL   HCL-EUR510111
    74471536FEMALE END FITTING            RM2007125/01/2007-3ABH1-DC   HCL-EUR510111
    74471536FEMALE END FITTING            RM2007125/01/2007-3ABH1-CL   HCL-EUR510111
    74471537MALE END FITTING              RM2007125/01/2007-5ABH1-MS   HCL-EUR510111
    74471538BANJO FITTING                 RM2007125/01/2007-5ABH1-DE   HCL-EUR510111
    74471538BANJO FITTING                 RM2007125/01/2007-1ABH1-OR   HCL-EUR510111
    74471538BANJO FITTING                 RM2007125/01/2007-4ABH1-DF   HCL-EUR510111

    So basically I wanna take the TrnQty column and depending on which month from the TrnMonth and the TrnYear column put it into a new View that splits and Sums the qtys per month based on a test against GETDATE().

    So I get

    <StockCode ><Description><QtyCurrentMonth><QtyMonth-1><QtyMonth-2><QtyMonth-3><etc>

    Hope you understand,

    Let me know if you need more info.

    Thanks,

    Craig Lloyd

  • March 20, 2007 at 7:35 am

    #695918

    Are we looking at trnyr and trnmnth or entrydate for the date comparison?

    select stockcode,description,

    sum(case when datepart(yyyy,getdate()) = trnyr and datepart(mm,getdate()) = trnmonth then trnqty else 0 end) as curmonth,

    sum(case when datepart(yyyy,dateadd(mm,-1,getdate())) = trnyr and datepart(mm,dateadd(mm,-1,getdate())) = trnmonth then trnqty else 0 end) as curmonthl1,

    sum(case when datepart(yyyy,dateadd(mm,-2,getdate())) = trnyr and datepart(mm,dateadd(mm,-2,getdate())) = trnmonth then trnqty else 0 end) as curmonthl2,

    and so on

  • March 20, 2007 at 7:56 am

    #695930

    Hi,

    we can use either the TrnMonth or DATEPART the Entry date as they are the same data source in the tables used to create the original view.

    I just thought it would be easier to use the TrnMonth,

    That query seems to have done what I want, fantastic

    You have made me a very happy guy

    Don't want to appear cheeky but how do I get the values to collate in the qty columns as integers ? they are currently showing .000 if the value is 0 and 10.000 if it's 10.

    Cheers,

    Woohooooooooooo

    Craig

     

     

  • March 20, 2007 at 8:14 am

    #695942

    what is the data type of the quantity field?  You can change that to int and your sums should inherit that or you can cast your sums as int

     

  • March 20, 2007 at 8:41 am

    #695956

    Hi,

    thanks, I used the Cast ((<expresion&gt AS Int) to sort it out

    Cheers,

    Craig

  • March 20, 2007 at 8:53 am

    #695966

    Just a thought.......

    Will the month-? take the date calculations past the year end ?

    What I mean is if the Date calculation is minus 3 months where the GETDATE() month is 2 will it go back to month 12 of 2006 ? to get the data ?

    If not how do I deal with the rolling year end ?

    Cheers,

    Now this is getting complicated 🙂

    Craig Lloyd

  • March 20, 2007 at 9:02 am

    #695971

    The dates will roll back properly because you are using date functions.

     

    you may also want to think about making the current date a parameter instead of getdate() so you can run the report historically.

  • March 20, 2007 at 8:38 pm

    #696079

    Be careful using "Views of Views"... the outer view will frequently cause the inner view to fully materialize it's rows especially when aggragates are used in the outer view... it won't take many rows to create a performance problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 21, 2007 at 2:50 am

    #696098

    That is true.

    It's pretty common that one view will perform ok, and the other view also, but when you start piggybacking views on top of eachother really bad performance may be the result.

    /Kenneth

  • March 21, 2007 at 3:26 am

    #696103

    mmm, I was unaware this could happen,

    would it be better to create and update a table where I have the first view ?

    If so how do I get it to update when the original tables update ? I'm assuming it's a stored procedure or something that would do this but I have very little experience with stored procedures, so I'm a bit out of my depth.

    The report that this view is going to supply is only going to be run once a month so can is there a better way of doing it altogether ?

    Cheers,

    Craig

  • March 21, 2007 at 5:43 am

    #696115

    If it's a once per month report as you say, Craig, then a view offers no particular advantage because the underlying data will have changed enough to force a "recompile" of the execution plan, anyway.

    I'd make a stored procedure where you can do things using more than one SELECT (divide & conquer) and maybe a temp table to store interim results to really simplify the code (more SELECTs with much simpler level of complexity and fewer joins each).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply