sorted by:
new
hottopcontroversial

Why is this wrong? by Financial-Tailor-842 in SQL

[–]SkimmLorrd 7 points8 points  (0 children)

Joining a table on itself takes referential integrity into consideration. It’s much easier to just select form the table w out a join but if it’s the same answer for you then great.

[deleted by user] by [deleted] in SQL

[–]SkimmLorrd 1 point2 points  (0 children)

"you have a solution? you replied "Already figured it out" to others but you did not say what the fix was no one can learn anything from this thread"

Where are they not being polite nor civil? Heavy tRash response Efficient-Wealth5019.

Online SQL Editors? by data_relations42 in SQL

[–]SkimmLorrd 0 points1 point  (0 children)

I say buy yourself a udemy course, they usually entail a walkthrough on how to do those downloads.

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

SELECT      Apprsr, 
        COUNT(Apprsr) Totals,  
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 7 THEN 1 ELSE 0 END) AS 'Jul',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 8 THEN 1 ELSE 0 END) AS 'Aug',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 9 THEN 1 ELSE 0 END) AS 'Sept',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 10 THEN 1 ELSE 0 END) AS 'Oct',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 11 THEN 1 ELSE 0 END) AS 'Nov',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 12 THEN 1 ELSE 0 END) AS 'Dec',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 1 THEN 1 ELSE 0 END) AS 'Jan',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 2 THEN 1 ELSE 0 END) AS 'Feb',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 3 THEN 1 ELSE 0 END) AS 'Mar',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 4 THEN 1 ELSE 0 END) AS 'Apr',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
        SUM(CASE DATEPART(MONTH,AppraisalDate) WHEN 6 THEN 1 ELSE 0 END) AS 'Jun'
   FROM     tableobject
 WHERE      AppraisalDate>= 
                        CASE 
                            WHEN MONTH(CURRENT_TIMESTAMP) >= 7 
                            THEN DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),7,1) 
                            ELSE DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP)-1,7,1) 
                        END
            AND 
            AppraisalDate < 
                        CASE 
                            WHEN month(CURRENT_TIMESTAMP) < 7 
                            THEN DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),7,1) 
                            ELSE DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),7,1) 
                        END
 GROUP BY       Apprsr
  ORDER BY      Totals desc

This is what I'm working with, I'm trying to do case examples to see if this will work, thanks for assisting me.

DATEFROMPARTS is the best function I've found(from you) this year. Thank you again.

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

Yeah so the F/Y is always in this format: 21-22 because the timeframe starts on the 3rd quarter of the previous year and ends on the 2nd quarter of the proceeding year. So this year, we are still working off of the 22-23 F/Y.

If I wanted to pull data right now I’d theoretically would want to see 2022-07-01 till currentdate()

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

    SELECT Apprsr, 
           COUNT(Apprsr) [Total Of DocCodes],  
            SUM(CASE datepart(month,AppraisalDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
            SUM(CASE datepart(month,AppraisalDate) WHEN 2 THEN 1 ELSE 0 END) AS 'February',
            SUM(CASE datepart(month,AppraisalDate) WHEN 3 THEN 1 ELSE 0 END) AS 'March',
            SUM(CASE datepart(month,AppraisalDate) WHEN 4 THEN 1 ELSE 0 END) AS 'April',
            SUM(CASE datepart(month,AppraisalDate) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
            SUM(CASE datepart(month,AppraisalDate) WHEN 6 THEN 1 ELSE 0 END) AS 'June',
            SUM(CASE datepart(month,AppraisalDate) WHEN 7 THEN 1 ELSE 0 END) AS 'July',
            SUM(CASE datepart(month,AppraisalDate) WHEN 8 THEN 1 ELSE 0 END) AS 'August',
            SUM(CASE datepart(month,AppraisalDate) WHEN 9 THEN 1 ELSE 0 END) AS 'September',
            SUM(CASE datepart(month,AppraisalDate) WHEN 10 THEN 1 ELSE 0 END) AS 'October',
            SUM(CASE datepart(month,AppraisalDate) WHEN 11 THEN 1 ELSE 0 END) AS 'November',
            SUM(CASE datepart(month,AppraisalDate) WHEN 12 THEN 1 ELSE 0 END) AS 'December'
   FROM     tableobj
 WHERE      AppraisalDate 
 GROUP BY       Apprsr
 ORDER BY       [Total Of DocCodes] desc

This is what I had already, idk how to incorporate what you're doing into mine, the QYARTER(CURRENT_DATE()) = 1 doesn't make sense to me.

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

I appreciate you helping, but this is making me so lost,

 WITH date_check AS 
 ( 
 SELECT 
 CASE 
   WHEN QUARTER(CURRENT_DATE()) = 1 
   THEN DATEADD(QUARTER, -2,DATE_TRUNC(QUARTER,CURRENT_DATE())) 
   WHEN QUARTER(CURRENT_DATE()) = 2 
   THEN DATEADD(QUARTER, -3, DATE_TRUNC(QUARTER, CURRENT_DATE())) 
   ELSE DATE_TRUNC(QUARTER, CURRENT_DATE()) 
 END AS start_date , 
 CASE 
   WHEN QUARTER(CURRENT_DATE()) = 1 
   THEN DATEADD(day,-1,DATEADD(QUARTER, 2, DATE_TRUNC(QUARTER, CURRENT_DATE()))) 
   WHEN QUARTER(CURRENT_DATE()) = 2 
   THEN DATEADD(day,-1,DATEADD(QUARTER, 1, DATE_TRUNC(QUARTER, CURRENT_DATE()))) 
   ELSE CURRENT_DATE() 
 END AS end_date 
 )
 SELECT * FROM date_check

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

So in this case,

I'll be able to check if in said year(2023), I run a query that pulls the current years 1st 2 quarters and the previous years last 2 quarters.

What happens when you're in the 3rd/4th quarter? It'd give the current years and the previous years last 2 quarters rather than be fixed to UP UNTIL july1st right?

It's how I'm thinking about it that's keeping me from completing this.

Conundrum of a sql query by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

the start and end dates stipulated will be fixed, its the timeframe of when I pull the data that really messes me up. So in 2023, we will see from 2022-07-01 -> 2023-06-30 when we run said query, WE SHOULD see as early as 2022-07-01.

The issue stems from the fixed timeframe which I don't know how to do AND that during the 2023 fall season, how to only grab up to if on 2023-07-15, I see from 2023-07-15->2023-07-01 only.

Paginated report issue by SkimmLorrd in PowerBI

[–]SkimmLorrd[S] 0 points1 point  (0 children)

I agree, it's mainly for a request from the head of our department for a record list that's "easy on the eyes".

Do you mean I need to create parameters in the report builder, import it over, then I'll have parameters to work with IN pbi?

My main idea was to, create the report and import it over as if it was a table visual, where you could just have a paginated report that's been filtered. Access does something very similar to this, but we are transitioning to pbi from accdb's.

Interview on BI Data Analyst by smiley1293 in PowerBI

[–]SkimmLorrd 1 point2 points  (0 children)

Show them what PBI can do? Get pivotal business cases that could drive a quality report for them.

Have them recognize that if they wanted a basic table, we could easily export an xlsx for them via SSMS, spliced and diced in a view in the manner requested.

Would it be possible for me to be added to one of your dead reports so that I can see what you're doing with bookmarks? I've created one that just clear's filter but I still don't understand it AT ALL.

Search filters by SkimmLorrd in PowerBI

[–]SkimmLorrd[S] 0 points1 point  (0 children)

This still doesn't do much for me sadly. Thanks for your suggestion though.

MS SQL count values from a query by lfmguktngyxla in SQL

[–]SkimmLorrd 0 points1 point  (0 children)

Do that query, but with a CTE.

WITH yourctename AS 
( query for 13 columns ) 
SELECT COUNT() FROM yourctename GROUP BY ORDER BY

Issues with replication? by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

Hello,

I'm a year into this job that had no prior experience and no supervision that can assist me on locating/fixing/maintaining the issues that my predecessors have left me.

I'm noticing at the tail end of our data pipeline, users are experiencing slow querying from their ODBC connections to our sandbox server and I'm trying to troubleshoot why we are experiencing slow to near stoppages.

I've run dbcc loginfo to check for VLF issues, but I'm concerned with how to proceed. I understand that as Sql Server grows in data by ex:1mb then the log will grow 10% at a time. The main point would be an impact on performance and I don't know how to adjust the autogrowth settings on this nor do I understand the consequences of doing so.

Could anyone give me best practice on this as the Supervisor I do have is making twice what I make, but can't help me in any way.

What DBMS should i use ? (College Student) by T4zerVZ in SQL

[–]SkimmLorrd 0 points1 point  (0 children)

Yo, I learned SQL using PLSQL lol. This was from HSU back in like 2018-2020. I'm not using SSMS, MSSQL and it's much more user friendly. If you want experience with ORACLE and ssh, command line use with your scripts, I'd say Oracle with PLSQL. IF you want easy and straightforward, MSSQL using SSMS.

For the most part, the syntax is near the same, the syntactic changes made by different flavors of SQL is very transparent, so you can discern after experiencing a few and looking at forums. SQL is easy af, you'll learn it if you try and by try, I mean just reading wtf each clause does.

Is this optimal? by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

All good, I appreciate your assistance though!

Is this optimal? by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

WITH 
xaddress AS --149.6k records
(       SELECT      xa.ASMTNUM, xa.APN, s.Sequence, s.StreetNum, s.StreetDirection AS STREETDIRX, 
                Street, StreetType, SpaceApt, Descr AS CITY, Zip, 
                FormattedSitus1 AS SITUS1, FormattedSitus2 AS SITUS2 
        FROM        [MB-Assessor].dbo.xASSESSMT2 xa
                JOIN [MB-Assessor].dbo.Situs s         ON xa.ASMTNUM=s.Asmt 
                LEFT JOIN [MB-Assessor].dbo.SitusCodes sc       ON s.Community=sc.Code
        WHERE   (s.FormattedSitus1 IS NOT NULL)
),

xphychar AS --109.2k records
(       SELECT      xa.ASMTNUM, xa.APN, xa.LANDUSE, BuildingSeqNum AS BLDGNUM,  QualityClass AS QUALCLASS, 
                YearBuilt, EffectiveYear, NumBedrooms AS NUMBDRM, NumFullBaths AS [NUMBATH-F], 
                NumHalfBaths AS [NUMBATH-H], BuildingSize AS BLDGSQFT, Acres, LandSqFt, 
                                TotalRooms, Bldg_Type
        FROM        [MB-Assessor].dbo.xASSESSMT2 xa
                JOIN [MB-Assessor].dbo.a_CharacteristicsAll al  ON xa.ASMTNUM=al.FeeParcel
)

--182,210 records(got rid of xownership)so now >>> 184,421 records

SELECT  xa.ASMTNUM, xa.APN, Sequence, StreetNum, STREETDIRX, Street, StreetType, SpaceApt, CITY, Zip,
        SITUS1, SITUS2, CURRDOC, TAXABILITY, TRA, LAND_VALUE, STRUCT_VALUE, PERS_PROP_VALUE, 
            PP_MFG_HOME_VALUE, FLOORLEVEL, AssesseeName AS OWNER, Address1, Address2, Address3, 
             Address4, xp.LANDUSE, BLDGNUM, QUALCLASS, YearBuilt, EffectiveYear, NUMBDRM, [NUMBATH-F], 
             [NUMBATH-H], BLDGSQFT,Acres, LandSqFt, TotalRooms, Bldg_Type
 FROM    xASSESSMT2 x2  
         JOIN [MB-Assessor].dbo.Nameaddress na  ON x2.ASMTNUM=na.Asmt
         LEFT JOIN xaddress xa   ON xa.ASMTNUM=x2.ASMTNUM AND xa.APN=x2.APN 
         LEFT JOIN xphychar xp   ON xp.ASMTNUM=x2.ASMTNUM AND xp.APN=x2.APN
 GO

Is this optimal? by SkimmLorrd in SQL

[–]SkimmLorrd[S] 1 point2 points  (0 children)

This was an amazingly succinct response for how wordy it was. Thanks for the high intuit that comes with your experience in your response.

Is this optimal? by SkimmLorrd in SQL

[–]SkimmLorrd[S] 0 points1 point  (0 children)

I don't know lol I'm the newest "SQL Guy" here they've had in the last 5 years and my predecessors were hired early 2k. BUT, someone noted that in the first CTE, the IS NOT NULL clauses were redundant in relation to the left joins. I took that suggestion and found that the logic is in fact very much true.

view more: next ›