Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding a table with date appended to it and check for the range of that date Expand / Collapse
Author
Message
Posted Wednesday, September 05, 2012 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 2:53 PM
Points: 19, Visits: 157
Hi All,

I need to create a process that looks for a table in database with date(YYYYMM) appeneded to it where MM can be any month of current quarter and check if there exists a table and dump that data into a table. I was able to get to the point of getting the last day and first day of current quarter to check for the range. But the problem is iam not sure how to search for a table with date appended for this specific range. Please help me out.

Thanks in advance
Post #1354345
Posted Wednesday, September 05, 2012 1:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:30 PM
Points: 389, Visits: 89
Hi Prasad,

In order to search for the table we can use sys.tables as below, also inorder to convert date to month you can use datepart function.
Hope this helps.

select * from sys.tables where name like '%YYYYMM%'


Thanks.
Post #1354359
Posted Wednesday, September 05, 2012 1:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Will your table suffix represent just year and month only YYYYMM or day as well YYYYMMDD?
What criteria do you want to apply? Month number and year? Date range? Anything else?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1354367
Posted Wednesday, September 05, 2012 2:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 2:53 PM
Points: 19, Visits: 157
Eugene Elutin (9/5/2012)
Will your table suffix represent just year and month only YYYYMM or day as well YYYYMMDD?
What criteria do you want to apply? Month number and year? Date range? Anything else?



It just uses YYYYMM not date....no other criteria...i just have to check with this date falls under current quarter...the only problem is it is aappended to a table/tables and have to pull all thosee tables out.
Post #1354371
Posted Wednesday, September 05, 2012 2:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
That will return all tables with suffix YYYYMM where YYYYMM represent every month of the current calendar quarter.

SELECT t.name 
FROM sys.tables t
JOIN (SELECT '%' + CAST(QFM + m AS VARCHAR) AS QM
FROM ( SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4))
+ RIGHT('0' + CAST(DATEPART(QUARTER,GETDATE()) * 3 AS VARCHAR(2)),2) AS QFM ) q
CROSS JOIN (SELECT 0 m UNION ALL SELECT 1 m UNION ALL SELECT 2 m) m
) qm3
ON t.name LIKE qm3.QM



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1354375
Posted Friday, September 07, 2012 7:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Another way to get the table names...

 SELECT st.Name
FROM sys.tables st
WHERE RIGHT(st.Name,6) IN
(
SELECT CONVERT(CHAR(6), DATEADD(mm,t.N,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)), 112)
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t (N)
)
;



--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356328
Posted Sunday, September 09, 2012 6:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
>> I need to create a process that looks for a table in database with date(YYYYMM) appended to it where MM can be any month of current quarter and check if there exists a table and dump that data into a table. <<

This makes no sense. Rows are appended to a tables, not dates. Why are you PHYSICALLY moving data from table to table? SQL programmers use VIEWs.

>> I was able to get to the point of getting the last day and first day of current quarter to check for the range. <<


A useful idiom is a report period calendar. It gives a name to a range of dates.

CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.

Since SQL is a database language, we prefer to do look ups and not calculations.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1356530
Posted Sunday, September 09, 2012 8:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
CELKO (9/9/2012)
>> I need to create a process that looks for a table in database with date(YYYYMM) appended to it where MM can be any month of current quarter and check if there exists a table and dump that data into a table. <<

This makes no sense. Rows are appended to a tables, not dates. Why are you PHYSICALLY moving data from table to table? SQL programmers use VIEWs.


He's not talking about appending rows to a table, Joe. He's talking about finding tables that have table names that are essentially identical with the only difference being that the names of the tables have had a year and month notation appended to them. This type of thing happens a lot when receiving data from 3rd party sources. If you don't have the Enterprise Edition of SQL Server, the tables are typically allowed to persist as they are named as a form of "poor man's partitioning" and are frequently assembled as a complete "entity" by using partitioned views.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356539
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse