Get Hourly Created records count for a day from a SQL Table

SELECT STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ') AS HOUR, COUNT(*) AS COUNT
FROM tableName
WHERE Createddate BETWEEN CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AND CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())) + 1)
GROUP BY STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ')
ORDER BY 1

Comments

Post a Comment

Popular posts from this blog

How to Convert Word Document to PDF using C#

  Document wordDocument = null;             string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];             string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];             DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);             FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");             foreach (FileInfo wordFile in wordFiles)             {                 object paramSourceDocPath = (Object)wordFile.FullName;                 //(Object)wordFile.Name                 string paramExportFilePath = paramSourceDocPath.ToString().Replace(".doc", ".pdf"); //  string paramExportFilePath = DestinationPath + wordFile.Name.ToString().Replace(".doc", ".xps");                 if (!File.Exists(paramExportFilePath.ToString()))                 {                     Application wordApplication = new Application();                        object paramMissing = Type.M
Read more

Sql query to get counts by quarterly and half yearly

---- Quarterly  SELECT     CASE         WHEN MONTH(date_column) IN (1, 2, 3) THEN 'Q1'         WHEN MONTH(date_column) IN (4, 5, 6) THEN 'Q2'         WHEN MONTH(date_column) IN (7, 8, 9) THEN 'Q3'         WHEN MONTH(date_column) IN (10, 11, 12) THEN 'Q4'     END AS quarter,     CONCAT(YEAR(date_column),         CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN '-H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN '-H2'         END) AS half_year,     COUNT(*) AS count FROM your_table GROUP BY quarter, half_year ORDER BY quarter, half_year; --- Half Yearly  SELECT YEAR(date_column),    CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN 'H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN 'H2'         END AS half_year,     COUNT(*) AS count FROM your_table GROUP BY  half_year ORDER BY  half_year; Replace `date_column` with the column in your table that con
Read more

How to upload File to Secured FTP ( SFTP ) in C#

Upload File from Local Machine to SFTP Server in C# The following C# code will upload a file from local machine to SFTP server. string _ftpURL = "testsftp.com"; //Host URL or address of the SFTP server string _UserName = "admin"; //User Name of the SFTP server string _Password = "admin123"; //Password of the SFTP server int _Port = 22; //Port No of the SFTP server (if any) string _ftpDirectory = "Receipts"; //The directory in SFTP server where the files will be uploaded string LocalDirectory = "D:\\FilePuller"; //Local directory from where the files will be uploaded string FileName = "test.txt"; //File name, which one will be uploaded Sftp oSftp = new Sftp(_ftpURL, _UserName, _Password); oSftp.Connect(_Port); oSftp.Put(LocalDirectory + "/" + FileName, _ftpDirectory + "/" + FileName); oSftp.Close();   tamir.sharpssh.dll can be downloaded from below link. https://sourcef
Read more