logo
Tags down

shadow

Is there a way to split sql results by year?


By : SHIROMANI SHARMA
Date : September 14 2020, 09:00 PM
this will help I currently have the following SQL statement: , Just add the project year to the SELECT and GROUP BY clauses:
code :
SELECT
    [Manager].[Name], 
    YEAR([Project].[CurrentStatusDt]) PojectYear,
    COUNT([Project].[ProjectId]) AS TotalProjects
FROM [Project]
INNER JOIN [Manager] ON [Project].[ManagerId] = [Manager].[ManagerId]
WHERE [Project].[CurrentStatusId] = 5
GROUP BY [Manager].[Name], YEAR([Project].[CurrentStatusDt])
SELECT
    [Manager].[Name], 
    SUM(CASE 
        WHEN [Project].[CurrentStatusDt] < CAST('2019-01-01' AS DATE)
        THEN 1 
        ELSE 0 
    END) TotalProjects2018,
    SUM(CASE 
        WHEN [Project].[CurrentStatusDt] >= CAST('2019-01-01' AS DATE)
        THEN 1 
        ELSE 0 
    END) TotalProjects2019
FROM [Project]
INNER JOIN [Manager] ON [Project].[ManagerId] = [Manager].[ManagerId]
WHERE 
    [Project].[CurrentStatusId] = 5 
    AND [Project].[CurrentStatusDt] >= CAST('2018-01-01' AS DATE)
    AND [Project].[CurrentStatusDt] <  CAST('2020-01-01' AS DATE)
GROUP BY [Manager].[Name]


Share : facebook icon twitter icon

Problem with leap year in MS SQL giving different results for same year


By : funnytrees
Date : March 29 2020, 07:55 AM
Does that help Year() function returns value of INT datatype. Your function expects DATETIME value

How to Split Year month Day,if Year does not exist in the given string using c#?


By : Philipp
Date : March 29 2020, 07:55 AM
this one helps. Here the below code am working for Split the number from the given string and stores the correspond integer into combobox.That working Perfect.But i want to know ,If Year does not exist in the string,how to assign Year as Zero and the next integer for month strores in second combobox , You can do with Regex like this
code :
int combBox1, combBox2, combBox3;
var sample = "1Year(s)4month(s)2DaY(s)";

var yearString = Regex.Match(sample, @"\d+Year", RegexOptions.IgnoreCase).Value;
if (!string.IsNullOrEmpty(yearString))
    combBox1 = int.Parse(Regex.Match(yearString, @"\d+").Value);
var monthString = Regex.Match(sample, @"\d+Month", RegexOptions.IgnoreCase).Value;
if (!string.IsNullOrEmpty(monthString))
    combBox2 = int.Parse(Regex.Match(monthString, @"\d+").Value);
var dayStrings = Regex.Match(sample, @"\d+Day", RegexOptions.IgnoreCase).Value;
if (!string.IsNullOrEmpty(dayStrings))
    combBox3 = int.Parse(Regex.Match(dayStrings, @"\d+").Value);

Concatenate SQL Results by Year (Year, Make, Model)


By : Tomotaka Ogino
Date : March 29 2020, 07:55 AM
will be helpful for those in need I Have change it again. please test it and let me know if it works. Sorry for the bad SQL before. i forgot to initialize the variables at the first time.
code :
  SELECT
    sku,
    CASE
      WHEN MIN(YEAR) = MAX(YEAR) THEN MIN(YEAR)
      ELSE CONCAT(MIN(YEAR), '-', MAX(YEAR))
     END AS YEAR,
     make, model, submodel, notes
  FROM
    (SELECT @ldfnr:= IF((@old_make = tab.make
      AND @old_model = tab.model
      AND @old_submodel = tab.submodel
      AND @old_notes = tab.notes
      AND (@old_year = tab.`year`
      OR @old_year = tab.`year`-1)) , @ldfnr, @ldfnr+1) AS nr, tab.* ,
      @old_make := tab.make , @old_model := tab.model ,
      @old_submodel := tab.submodel , @old_notes := tab.notes ,
      @old_year := tab.`year`
    FROM TableName AS tab,
      (SELECT @ldfnr:=0, @old_model:='', @old_submodel:='', @old_notes:='', @old_year:='', @old_make:=''  ) AS tmp
    ORDER BY make, model, submodel, notes, `YEAR` ASC) AS mytab
  GROUP BY nr
  ORDER BY nr;

+------+-----------+-----------+-------+----------+------------------------------------------+
| sku  | YEAR      | make      | model | submodel | notes                                    |
+------+-----------+-----------+-------+----------+------------------------------------------+
|    1 | 1995-1997 | Chevrolet | Astro | Base     | Clear Lens; Chrome Housing; Pair; 1 pc.; |
|    1 | 2001      | Chevrolet | Astro | Base     | Clear Lens; Chrome Housing; Pair; 1 pc.; |
|    1 | 1995-1996 | Chevrolet | Astro | CL       | Clear Lens; Chrome Housing; Pair; 1 pc.; |
|    1 | 1995-1996 | Chevrolet | Astro | LS       | Clear Lens; Chrome Housing; Pair; 1 pc.; |
|    1 | 1997      | Chevrolet | Astro | LT       | Clear Lens; Chrome Housing; Pair; 1 pc.; |
+------+-----------+-----------+-------+----------+------------------------------------------+
5 rows in set (0.44 sec)

Subsetting by year gives different results using ymd() vs. year()


By : Mike Morales
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Looking over the problem some more it looks like: ymd("2010-12-31") is 12:00 AM on the 31st and not 12 PM.
There are 2 options which I see a possible solutions. Use the next day in the filter or covert all of your date/times to just dates with GMT.
code :
require(lubridate)
library(data.table)

teaSet <- fread("teaSet.csv", na.strings=c("NA","N/A", ""))
teaSet$opened <- ymd_hms(teaSet$opened, tz = "")
teaSet$year <- as.factor(teaSet$year) 

ymd2010 <- teaSet[opened >= ymd("2010-01-01") & opened < ymd("2011-1-1"),]
print(dim(ymd2010))

#a second possible option - not as clean as the prior one
teaSet$opened <- ymd_hms(teaSet$opened, tz = "GMT")
ymd2010_2 <-teaSet[as.Date(opened) >= ymd("2010-01-01") & as.Date(opened) <= ymd("2010-12-31")]
print(dim(ymd2010_2))

year2010 <- teaSet[year(opened)==2010,]

print( dim(year2010 ))
summary(teaSet$year)

"ratchet" function: replace previous year's results only if next year's results are greater


By : Ahmed Hamdy
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I need to loop through years in a df and replace values in another column (percentpp) only if the next year's percentpp is larger. Once a cell has reached a high value, it cannot go back down - only up, like a ratchet. , You can use cummax.
With base R:
code :
df <- df[order(df$year), ]
df$percentpp <- ave(df$percentpp, df$cell_id, FUN = cummax)
library(dplyr)

df <- df %>%
  group_by(cell_id) %>%
  arrange(year) %>%
  mutate(percentpp = cummax(percentpp)) %>%
  ungroup
df <- read.table(text = "
  cell_id   year    percentpp
1   40      2011    3
2   41      2011    1
3   42      2011    0
4   43      2011    0
5   40      2012    1
6   41      2012    5
7   42      2012    1   
8   43      2012    5
9   40      2013    2
10  41      2013    2
11  42      2013    2
12  43      2013    0
13  40      2014    2   
14  41      2014    3   
15  42      2014    3   
16  43      2014    3 
")
Related Posts Related Posts :
  • Error when trying to insert values into a table and not sure why
  • can anybody help me to find the number of times the zero will occur from 1-1000
  • Surrogate vs Natural Primary Keys, *SPECIFICALLY* in a Data Warehouse. Is this debated?
  • Oracle SQL SAVEPOINT problems
  • How to concatenate aggregated rows unless one column equals a certain value in SQL?
  • How to get current Year in PLSQL stored procedure
  • SQL - Return UserIds that only reviewed an item as "poor"
  • Rollback date specific changes for user
  • format date in oracle SQL
  • Select distinct column in sql group by
  • SQL ORDER BY one of two column values with value used from first column if not null and other column if first column is
  • How do I make and access regex capture groups in Django without RawSQL?
  • Representing a binary tree in an SQL table using foreign keys
  • Regex or conversion for 'YYYY-MMM-DD'
  • How to join all different rows with the same ID but a different date and return it as a unique row with new columns like
  • Alternative to multiple SQL self-joins to transform a table
  • regexp replace to replace the second special character from the last in a string
  • Fix sql injection on Rails model scope
  • How to keep updating a table which is created from multiple tables with different update times
  • How to replace string in Oracle?
  • How to shorten SQL LIKE statement
  • Don't display row the condition is accomplished SQL
  • SQL Server : create a column based on the first occurrence of a value in another column
  • SQL Server: Replicate data from multiple tables into one
  • Case statement combining three tables
  • Issue with running Spark SQL query - column not found
  • There is a reserved word in my SQL statement
  • Populating data from legacy DB using Entity Framework Code First
  • How to remove all constraints on a table with SMO objects (SQL Server)?
  • Infinite loop with recursive SQL query
  • How to display day of the week instead of dates at line chart in Power BI?
  • Running total. CTE keeps running after the result has been fetched
  • Oracle 11 SQL : Split 1 row into x rows and insert a new column
  • Why is this 'WHEN THEN' SQL query not returning the expected result?
  • Filter rows within an analytical function - Oracle
  • Use analytical functions on a small data set but with all the data in the table
  • how to use bcp in client system not remote
  • How do I use an IN Clause in Sql Server Parameter
  • Display only records which are not duplicated
  • How to update 0 or null values with sequence number?
  • Linking two tables based on timestamp interval
  • Get previous month data in Azure Cosmos DB
  • Changing URLs in a SQL table
  • Rounding number up or down, based on 0.5 and closest to round number
  • snowflake Need to run sql insert in loop based on column value
  • Update column based on array search in postgresql
  • Getting SQL result where common column value result count is more than 1
  • How to convert a number into a format which will be separated by comma's by position in sql
  • Create Phone type column from the source of phone numbers
  • How to convert a column of array values
  • How to count the number of records in an sql database
  • fetch data using query builder by checking multiple data from same column in laravel
  • PostgresSQL - how to design an effective table
  • How to store emoji characters in DB2 database?
  • How to use PIVOT in SQL Server to convert a set of values into a column?
  • What is a clean way to copy a SQL table with more than 1000 rows?
  • Query to find tables without unique keys
  • scheduled job's job action exceeds 4000 characters in oracle
  • How can I improve performance of queries with window-functions, that seem to ignore indexes?
  • SQL CASE with one condition and multiple results
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk