Requesting help for advanced SQL generating automatic counts based on dates

Question

Requesting help for advanced SQL generating automatic counts based on dates

cuseman03

SSC Enthusiast

Points: 194
More actions
December 17, 2019 at 8:53 pm

#3708611

Hi,
challenging query I have that I can only get part of and am looking for expertise.
I have a column called 'filedate' and the format is MMM-D-YYYY and then time. So for example May 3 2019 1:27PM. I have another column called 'scheduled' and this has values such as monthly, daily, weekly, etc. what I want to do is come up with logic to ensure the count of files matches the scheduled but without having to ever code in data start and end times. For example, if you had the date above and 'monthly' you would want the filedate count to be >=1 for the month of may. In other words I want to tell SQL, look at the associated filedate and ensure for that particular month the count of filedates is >=1.
I also want to do this for 'daily' and 'weekly'. the challenge here is while you could say count >=5 for weekly, SQL doesn't know when a week ends and begins. So let's say I have 3 file dates, sept 3, 4, and 5 and the frequency is daily. SQL doesn't know the count is 3 because it doesn't know the dates that week begins and ends. My solution is to add t hem up and say where = 'daily', filedate count is >=20 (business days in a month) based on the month referenced in the actual file date.
In this example the count is 4 and it only needs to be >=1 (for the month sept)
In this example it needs to be >= 20 or 21 (business days in a month) but it's actual value is 4, meaning 16-17 files were not provided based on the # of business days in a month
thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

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

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 1

You can CONVERT() your string to a datetime value, which will make your life a lot easier, then you can group by day.
```
DECLARE @myDate varchar(50);
SET @myDate = 'May 3 2019 1:27PM';
SELECT @myDate,CONVERT(datetime,@myDate)?
```
SQL Server identifies the start of a week with the DATEFIRST value, normally it's 7 (Sunday) but you can change it using a SET DATEFIRST = <whatever> command first.
You can get a weeknum value for the year, so you can group by week that way
```
set datefirst 7;
select datepart(week, '2017-02-01');?
```
Hope that helps

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

drew.allen SSC Guru Points: 76944 More actions · Answer 2

Data should be stored in fields of the appropriate data type. Dates should be stored in date fields, not character fields. (There is an exception for intermediary tables that are used for parsing/validating incoming data.)

SQL is aware of the beginnings of weeks, but you can have more control over it by using a calendar table which can encode holidays and other non-working days that are exceptions to the general rules. You should Google calendar tables.

If you need more information, please post CONSUMABLE data and expected results. Consumable data consists of a script to create and populate a table (preferably a temp table) and the expected results should be done the same way.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 3

so the thing is I have thousands of dates so I can't hard code them as a variable. essentially what I need is for SQL to recognize the date, associate that to the frequency, and then perform the count. I'm not even sure this is possible but I am only an intermediate user.

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 4

unfortunately I can't post the results of the query per company guidelines. I think the calendar in a perfect world would be good, using it as a lookup table but this is just beyond my SQL toolset.

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 5

I just did that to show you how it works, you would just put your column there, but without the ddl and sample data I used a variable. If you can mock up even five samples, and fake the structure you want, we can try to give something workable

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 6

so attached are some rows from the query output. You can see that while for the table referenced there are records for 'daily', there are far fewer than there should be b/c daily indicates every business day which would total either 20 or 21 in a month. I want to be able to write some code that would have an output showing this:

Nov - 5 of 21

Oct 5 of 21

Sep 3 of 21

21 being business days in a month

so for 'daily' you should ideally see 20 or 21 records for that month each having a unique filedate.

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 7

cuseman03

SSC Enthusiast

Points: 194

December 17, 2019 at 11:08 pm

#3708655

sorry but I have no code at this point

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 8

Right, so do a count() of the files, group by month(convert(date time,dateColumn)),

or if you want the week, group by datepart(week, convert(datetime, dateColumn))

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 9

sorry but I am stuck here.

when I try to declare the column 'filedate' in the variable it doesn't recognize it

DECLARE @myDate varchar(50)

SET @myDate = filedate

SELECT @myDate,CONVERT(datetime,@myDate)

select COUNT(filedate)

from TableUpdateStats

group by datepart(week, convert(datetime))

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

You can't store a whole column in a scalar variable. Since you haven't provided table DDL and sample data, this is not tested:

SELECT
DATEPART(week, CONVERT(datetime,filedate))
,COUNT(*)
FROM TableUpdateStats
GROUP BY DATEPART(week, CONVERT(datetime,filedate));

John

cuseman03 SSC Enthusiast Points: 194 More actions · Answer 11

the data above is sample, it's from SQL just pasted into excel. using your code the error I get is

Msg 241, Level 16, State 1, Line 2

Conversion failed when converting date and/or time from character string.

DDL

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

What you have provided doesn't help me to test the code I wrote. CREATE TABLE and INSERT statements are easier for us to work with and hence will get you better answers quicker.

If your filedate column fails a conversion to datetime then you need to check the data in it to make sure that it's convertible. You may need to massage the data into another format before converting, or check that your regional settings are such that the current format is recognised.

John