Post reply

Need to write query with two conditions

  • August 11, 2006 at 3:03 am

    #654492

    Why wait until Monday?  Just put in Monday's date instead of GETDATE()!

    John

  • August 11, 2006 at 3:07 am

    #654494

    Yah done the same thing but out put is not correct

  • August 11, 2006 at 3:12 am

    #654495

    It look like it's not adding only friday and saturday data because total sum is bigger then original sum what i have for friday to sunday

  • August 11, 2006 at 3:48 am

    #654500

    I have tested the expression from John's solution, and it evaluates to 4 if date is Monday and 2 if day is other than Monday. This is precisely what you have in your query. Since the rest of the query is unchanged, it should give the same results as your original query... Are you sure you didn't introduce any typos? And does your originally posted query work well? /EDIT - See my next post/

    You can simplify the date concatenation:

    CREATE VIEW dbo.YS AS

    SELECT     Salesperson, SUM(InvoicedValueBase) AS YesterdaySales

    FROM         dbo.SalesF

    WHERE     CAST(CAST(19000000 + DateInvoiced  AS CHAR(8)) AS DATETIME) > GETDATE() + 2 * SIGN((@@datefirst + DATEPART(dw, GETDATE()) - 2) % 7 ) - 4

    GROUP BY ALL Salesperson

  • August 11, 2006 at 4:25 am

    #654505

    I had a good laugh now when I realized what's the problem... maybe if you try it on Monday, it will work fine :-))

    You have no upper date limit in your query, so if you try it on you table full of data with last Monday date, the sum includes everything from Friday to today.

    Nevertheless, John's solution works well, and the simplification from my previous post works, too.

  • August 11, 2006 at 6:10 am

    #654517

    Thanks Vladan

    I changed system date from 11th to 7th and double checked that which date getdate is giving me and it was monday means 7th and after that i posted my reply and also double checked my total...

    John's query is working well on yesterday data anly on monday showing me total sales value more then original means sum of friday and saturday sales...

    I am not a old user of SQL so i again check everything and try your query also might be doing something wrong again

  • August 11, 2006 at 6:37 am

    #654522

    Many Thanks Vladan & John

    After reading your post i made one view which was having data upto friday and again by changing system date to monday i tried it and it's working.

    You were right it was taking data from friday to today.........

    Thanks you very much, it was not possible to do without your help.

    Kind Regards,

    Vandy

Viewing 7 posts - 16 through 21 (of 21 total)

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