Post reply

retrieve Yeserday Data

  • May 8, 2011 at 8:17 pm

    #237823

    Hi Friends, Please look at this query and advise me what I should be doing to get only those stores which weren't polled yesterday.

    I am trying with all the possibilities but its giving dupicate rows to all he rows and data is incorrect.

    Please guide me.

    SELECT distinct TOP (100) PERCENT FUTURA.dbo.V_ANSCHRIF.ANS_NUMMER AS STORE, FUTURA.dbo.V_ANSCHRIF.ANS_NAME1 AS STORE_NAME,

    FUTURA.dbo.V_ANSCHRIF.ANS_PLZ_ZUSATZ AS NUMBER_OF_POS_IN_STORE, POLLING.NUMBER_OF_POS_POLLED,

    POLLING.LAST_DATE_OF_SALES, CASE WHEN (LAST_DATE_OF_SALES) >= CONVERT(char(8), DATEADD(day, - 1, GETDATE()), 112)

    THEN 'POLLED' ELSE 'FAIL' END AS STATUS

    FROM FUTURA.dbo.V_ANSCHRIF LEFT OUTER JOIN

    (SELECT COUNT(DISTINCT KAS_KASSE) AS NUMBER_OF_POS_POLLED, KAS_FILIALE AS BRANCH, KAS_MANDANT AS MANDANT,

    KAS_VK_DATUM AS LAST_DATE_OF_SALES

    FROM FUTURA.dbo.V_KASSTRNS

    WHERE (KAS_SATZART = 19) AND (KAS_VK_DATUM >= CONVERT(char(8), DATEADD(day, - 1, GETDATE()), 112))

    GROUP BY KAS_MANDANT, KAS_FILIALE, KAS_VK_DATUM

    HAVING (KAS_VK_DATUM >= CONVERT(char(8), DATEADD(day, - 1, GETDATE()), 112))) AS POLLING ON

    FUTURA.dbo.V_ANSCHRIF.ANS_NUMMER = POLLING.BRANCH

    WHERE (FUTURA.dbo.V_ANSCHRIF.ANS_TYP = 2) AND (1 = 1) AND (FUTURA.dbo.V_ANSCHRIF.ANS_PLZ_ZUSATZ LIKE 'LIVE%')

    ORDER BY STORE

  • May 9, 2011 at 2:48 pm

    #1321998

    Please don't continue to post the same question repeatedly. The reason nobody responded to your first post is because you didn't provide any detailed information about your problem.

    Please direct all replies here

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 2 (of 2 total)

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