Get records from modified date last week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get records from modified date last week

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-12-30 : 11:36:59

Good morning,

I have a "weekly report" that gets generated by a script that I have included below. It's pretty straight forward to get the correct date span but I also need to exclude any records that were modified after 3 pm (15 hundred) of the current date.

Here is what I have so far:

SELECT
NCOS_Data.NC_Lead_Gen_Office,
NCOS_Data.NC_LeadDate,
NCOS_Data.NC_Prospect_Name,
NCOS_Data.NC_CreateDate,
NCOS_Data.NC_ModifyDate

FROM [NCOS].[dbo].[NCOS_Data]
where NCOS_Data.NC_OMP_Approval_Status = 'Approved'
and DATEDIFF(day, NCOS_Data.NC_ModifyDate, GETDATE()) <= 7
and CASE WHEN NCOS_Data.NC_ModifyDate = GETDATE()
THEN DATEPART(hh, NCOS_Data.NC_ModifyDate) < 15
-- 3 PM CUT OFF
END
ORDER BY NCOS_Data.NC_Lead_Gen_Office

The error is pointing at the '<' sign ?

THEN DATEPART(hh, NCOS_Data.NC_ModifyDate) < 15

Any help is greatly appreciated.

Bryan Holmstrom

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-30 : 14:58:57

You might try something like

SELECT 
    NCOS_Data.NC_Lead_Gen_Office,
    NCOS_Data.NC_LeadDate,
    NCOS_Data.NC_Prospect_Name,
    NCOS_Data.NC_CreateDate, 
    NCOS_Data.NC_ModifyDate
FROM [NCOS].[dbo].[NCOS_Data]
WHERE NCOS_Data.NC_OMP_Approval_Status = 'Approved'
    AND DATEDIFF(day, NCOS_Data.NC_ModifyDate, GETDATE()) <= 7
    AND 1 = 
-- 3 PM CUT OFF
        CASE WHEN NCOS_Data.NC_ModifyDate = GETDATE() AND DATEPART(hh, NCOS_Data.NC_ModifyDate) > 15 
            THEN 0 ELSE 1
        END 
ORDER BY NCOS_Data.NC_Lead_Gen_Office

as the way you have the case statement is created is not allowed.

djj

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-12-31 : 10:01:52

Fantastic djj55...Worked perfect...
Thanks again

Bryan Holmstrom

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-01-02 : 07:50:54

Glad I could help, I do not always interpret the request correctly, and I do things a bit differently sometimes. So I am glad this worked out.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources