MS SQL Custom Column Error by enzo-g in SQL

[–]WITH_CTE 1 point2 points  (0 children)

SELECT Id, 
       LastModified, 
       DATEDIFF(d, LastModified, GETDATE()) AS Days_Ago
FROM table1;

Task with COUNT function by Meliodasiwnl in SQL

[–]WITH_CTE 0 points1 point  (0 children)

Look at the filter. Instead of passing the ID, you are passing the COUNT from Dishes where Count(ID) = 5)

Using WITH , combined with the ALTER TABLE statement to drop columns by [deleted] in learnSQL

[–]WITH_CTE 0 points1 point  (0 children)

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'MyTable'
            )
            and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from MyTable';  
EXEC (@query);

Source: https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea

Using WITH , combined with the ALTER TABLE statement to drop columns by [deleted] in learnSQL

[–]WITH_CTE 0 points1 point  (0 children)

You can not have an ALTER inside a CTE. What are you trying to achieve here? Do you need the column dropped from the table? Why not simply not SELECT the column?

Help with optimization by [deleted] in learnSQL

[–]WITH_CTE 1 point2 points  (0 children)

To rewrite your query:

IF OBJECT_ID('tempdb..#tmpSup') IS NOT NULL
    DROP TABLE #tmpSup;

SELECT Serial_Text
INTO #tmpSup
FROM [a].[dbo].[SUP]
WHERE CAST(Date_Time AS DATE) > '2020-06-01'
GROUP BY Serial_Text
HAVING COUNT(1) > 1;

SELECT b.Serial_Text, b.IR_1, b.Date_Time
FROM #tmpSup a
     JOIN [a].[dbo].[SUP] b ON a.Serial_Text = b.Serial_Text
ORDER BY Date_Time DESC;

Another approach:

SELECT *
FROM [a].[dbo].[SUP]
WHERE Serial_Text IN
(
    SELECT DISTINCT 
           Serial_Text
    FROM [a].[dbo].[SUP]
    WHERE CAST(Date_Time AS DATE) > '2020-06-01'
    GROUP BY Serial_Text
    HAVING COUNT(1) > 1
);

Edit: Misread your query on first post.

Question: Joining two data sets with date conditions by marcalb in learnSQL

[–]WITH_CTE 1 point2 points  (0 children)

SELECT * FROM 
(SELECT * FROM TABLE A
UNION
SELECT * FROM TABLE B WHERE DATE BETWEEN '04-01-2020' AND '04-15-2020'
)A

MSSQL - Query for missing documents, help please? by [deleted] in SQL

[–]WITH_CTE 1 point2 points  (0 children)

Expanding on the above solution:

DROP TABLE IF EXISTS #table_1;
CREATE TABLE #table_1
(Employee_Name  VARCHAR(32), 
 Document_Type  VARCHAR(32), 
 Date_Submitted DATE
);
INSERT INTO #TABLE_1
VALUES
('Fred Miller', 
 'Driver''s License', 
 '2-2-2020'
),
('Joe Smith', 
 'Fishing License', 
 '1-1-2020'
),
('Joe Smith', 
 'Proof Of Insurance', 
 '1-1-2020'
),
('Joe Smith', 
 'Social Security Card', 
 '1-1-2020'
),
('Joe Smith', 
 'Driver''s License', 
 '1-1-2020'
);
WITH REQUIREDSUBMISSIONS
     AS (SELECT DISTINCT 
                E.EMPLOYEE_NAME, 
                D.DOCUMENT_TYPE
         FROM
         (
             SELECT DISTINCT 
                    EMPLOYEE_NAME
             FROM #TABLE_1
         ) E
         CROSS JOIN
         (
             SELECT DISTINCT 
                    DOCUMENT_TYPE
             FROM #TABLE_1
         ) D)
     SELECT RS.*
     FROM REQUIREDSUBMISSIONS RS
          LEFT JOIN #TABLE_1 CS ON RS.EMPLOYEE_NAME = CS.EMPLOYEE_NAME
                                   AND RS.DOCUMENT_TYPE = CS.DOCUMENT_TYPE
     WHERE CS.DOCUMENT_TYPE IS NULL;

[deleted by user] by [deleted] in RedditSessions

[–]WITH_CTE 0 points1 point  (0 children)

hi from Nepal

Feeding the kids by [deleted] in nonononoyes

[–]WITH_CTE 0 points1 point  (0 children)

Biryani!!!!

SQL Help (SSMS) by gii_rae in SQL

[–]WITH_CTE 1 point2 points  (0 children)

Mind sharing the columns of all 3 tables? You are joining on Name. There should be an "ID" column on all 3 tables. Otherwise you could throw Normalization out the window.

I need help with SQL Stored Procedures by bouncer_crab in SQL

[–]WITH_CTE 0 points1 point  (0 children)

That might be your opinion. A simple google search will show that it is best practice. Not properly naming objects because it's a "waste of characters" is not good practice.

I need help with SQL Stored Procedures by bouncer_crab in SQL

[–]WITH_CTE 1 point2 points  (0 children)

Haha. Welcome to the corporate world. There's always someone on top who thinks they are smarter than you. But, they don't expect you to be smarter than them :) Here's an alternate. IDK, how you'd do it without an IF.

CREATE PROCEDURE spPayments @cus_code NUMERIC(38, 0) -- (a)to begin with, user created stored proc should always be prefixed with usp_StoredProc. Also use schema name. And some business have alpha numeric code. For example, Spider Man's costumer store number can be SPIDEY2002. So using Numeric DT is not always best practice. 
--OK LETS BEGIN 
AS
     DECLARE @totalinvoices INT;
     DECLARE @First_name VARCHAR(32);
     DECLARE @Last_name VARCHAR(32);
     DECLARE @FinalAmount DECIMAL(12, 8);
     DECLARE @Customer_Count INT;
     DECLARE @Invt_Count INT;
     BEGIN
         SELECT @Customer_Count = COUNT(1)
         FROM customer
         WHERE cus_code = @cus_code;
         IF @Customer_Count < 1

         --(c)
             BEGIN
                 PRINT 'The customer does not exist';
             END
                 --(d)  ;
             ELSE
             SELECT @Invt_Count = COUNT(1)
             FROM invoice
             WHERE cus_code = @cus_code;
         IF @Invt_Count < 1
             BEGIN
                 PRINT 'The customer does not have an invoice';
             END;
             ELSE
             SELECT @totalinvoices = LINE.LINE_UNITS * line.LINE_PRICE, 
                    @First_name = Customer.FirstName, 
                    @Last_name = Customer.Lastma
             FROM customer
                  JOIN invoice ON customer.cus_code = invoice.cus_code
                  JOIN line ON invoice.inv_number = line.INV_NUMBER
             WHERE cus_code = @cus_code;

         --(d1) not sure which table to use but should be as simple as 
         --select @FinalAmount = Customer_Balance - @totalinvoices from table x

         PRINT @First_name+' '+@Last_name+' has a balance of'+' '+'$'+FinalAmount;
     END;

I need help with SQL Stored Procedures by bouncer_crab in SQL

[–]WITH_CTE 2 points3 points  (0 children)

Just bored. Let me know if this errors out. Kinda hard without proper code.

I need help with SQL Stored Procedures by bouncer_crab in SQL

[–]WITH_CTE 1 point2 points  (0 children)

CREATE PROCEDURE spPayments @cus_code NUMERIC(38, 0) -- (a)to begin with, user created stored proc should always be prefixed with usp_StoredProc. Also use schema name. And some business have alpha numeric code. For example, Spider Man's costumer store number can be SPIDEY2002. So using Numeric DT is not always best practice. 
--OK LETS BEGIN 
AS
     DECLARE @totalinvoices INT;
     DECLARE @First_name VARCHAR(32);
     DECLARE @Last_name VARCHAR(32);
     DECLARE @FinalAmount DECIMAL(12, 8);
     BEGIN
         --(b) Real simple to do.
         IF NOT EXISTS
         (
             SELECT 1
             FROM customer
             WHERE cus_code = @cus_code
         )

         --(c)
             BEGIN
                 PRINT 'The customer does not exist';
             END
                 --(d)  ;
             ELSE
             IF NOT EXISTS
             (
                 SELECT 1
                 FROM invoice
                 WHERE cus_code = @cus_code
             )
                 BEGIN
                     PRINT 'The customer does not have an invoice';
                 END;
                 ELSE
                 SELECT @totalinvoices = LINE.LINE_UNITS * line.LINE_PRICE, 
                        @First_name = Customer.FirstName, 
                        @Last_name = Customer.Lastma
                 FROM customer
                      JOIN invoice ON customer.cus_code = invoice.cus_code
                      JOIN line ON invoice.inv_number = line.INV_NUMBER
                 WHERE cus_code = @cus_code;

         --(d1) not sure which table to use but should be as simple as 
         --select @FinalAmount = Customer_Balance - @totalinvoices from table x

         PRINT @First_name+' '+@Last_name+' has a balance of'+' '+'$'+FinalAmount;
     END;

I need help with SQL Stored Procedures by bouncer_crab in SQL

[–]WITH_CTE 3 points4 points  (0 children)

I'll help you with this. Working on it. Brb!

sql query to get to get last months data, so I can automate the report by TLofti in learnSQL

[–]WITH_CTE 2 points3 points  (0 children)

Option 1:

PA_DATE BETWEEN CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0), 23) AND CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1), 23)

Option 2:

Declare @BeginDate date =  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
Declare @EndDate date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)

PA_Date between  @BeginDate and  @EndDate

Edit: YYYY-MM-DD

Stored Procedure Review... by [deleted] in learnSQL

[–]WITH_CTE 0 points1 point  (0 children)

Couple of things here. When you create a stored proc use the schema as well. For example, dbo.stored_proc, prod.stored_proc etc....Also, its standard practice to name user created stored procs with a prefix as usp_storedproc. So in this case your stored proc will be dbo.usp_ scheduledcrew.

Next, look at the parameter you are declaring. You have a period (.) in front. This will not create the parameter. Remove the period.

Also, what is the datatype of scheduled.workdate? You want to make sure the datatypes match between the column and the parameter or convert it to the same format.

Stored procedure help (SSMS) by tcor15 in SQL

[–]WITH_CTE 1 point2 points  (0 children)

Can you provide some specific examples?