Date issue

Question

Date issue

kcheluvaraj 94771

SSC Rookie

Points: 34
More actions
May 8, 2011 at 4:56 pm

#237822

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.
If i use <> its giving huge set of rows like one store will be coming as failed for more than 100 times.
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
Thanks.

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

You need to post some ddl and sample data so that we can help you. Take a look at the link in my signature for answers on how to get help.

_______________________________________________________________

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/

Lowell SSC Guru Points: 323547 More actions · Answer 2

something like this should work:

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

--changed to use datetime datatype

WHEN (LAST_DATE_OF_SALES) >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)

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 >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -1))

GROUP BY

KAS_MANDANT,

KAS_FILIALE,

KAS_VK_DATUM

HAVING (KAS_VK_DATUM >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -1))

) 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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!