logo
Tags down

shadow

can we use two different queries with CTE table in sql server


By : Z.Hawkes
Date : September 16 2020, 12:00 PM
should help you out You can't use a CTE in more than one statement, their scope is at statement level. What you could do though is make use of the OUTPUT clause to capture the rows updated and insert them into your log table:
code :
WITH cte AS
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY hours  ORDER BY hours) AS rno, 
            personnum,
            Date,
            hours  
    FROM    Datatable
    WHERE   personnum =  @personnum
    AND     Hours = @minhrs
)
UPDATE  cte
SET     hours = hours + 0.01
OUTPUT  deleted.personnum, deleted.Date, deleted.hours INTO logdata
WHERE   rno=1;
-- I have had to guess at types here, change them as necessary
DECLARE @tmpLog TABLE (PersonNum INT, Date DATE, Hours INT); 

WITH cte AS
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY hours  ORDER BY hours) AS rno, 
            personnum,
            DATE,
            hours  
    FROM    Datatable
    WHERE   personnum =  @personnum
    AND     Hours = @minhrs
)
UPDATE  cte
SET     hours = hours + 0.01
OUTPUT  deleted.personnum, deleted.DATE, deleted.hours INTO @tmpLog
WHERE   rno=1;


INSERT LogData (personnum, Date, hours)
SELECT  personnum, Date, hours
FROM    @tmpLog;


Share : facebook icon twitter icon

SQL server sub queries vs. table joins


By : user2442592
Date : March 29 2020, 07:55 AM
like below fixes the issue It would probably be best to check the execution plan for individual cases. This will clue you in on its internal operation. Try the query a few different ways and analyze the execution plans.

View all queries that ran on a particular table in Sql Server


By : user3234082
Date : March 29 2020, 07:55 AM
like below fixes the issue This is why databases need audit tables which can be created to show who took the action. You also would have need to set up in advance a way to record the queries themselves (you can use Profiler but it takes a significant performance hit to do so and very few dbas will allow you to use it on a production database.) if you really wanted that but usually knowing who took the action (or which applications as we have many that hit our database) and when is all we need to trace down a data issue. If you haven't set up for auditing in advance, it is hard to get the answer you want. Some third party log analyzer (which will be costly) might be able to recreate the data but it still wouldn't know who did it or what the query was. And why are you doing backups only every three days?
How do you know the rows are missing (ie they should still be there) and not correctly deleted by someone because they should not be there. Just becasue there is a data difference from three days ago doesn't indicate a problem. If these are values that should not be changed by users, they why do they have the rights to change them? Perhaps your security model as welll as you back process needs to be re-evaluated.

SQL Server - Combine 5 queries into one table


By : Chris
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Hello I have to create a report on the amount of Outbound Usages from Pharmacies There are 4 types of outbound types: SMS, Call, Email, and P , A simple cross tab will work for this:
code :
select PharmacyID,
    count(*) as total,
    count(case when NotificationMode = 'sms' then 1 else null end) as sms,
    count(case when NotificationMode = 'email' then 1 else null end) as email,
    count(case when NotificationMode = 'call' then 1 else null end) as call,
    count(case when NotificationMode = 'p' then 1 else null end) as p
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and (NotificationMode = 'sms' or NotificationMode = 'call' or NotificationMode = 'email' or NotificationMode = 'p')
group by PharmacyID
SELECT PharmacyID, [sms], [email], [call], [p]
FROM
    (SELECT PharmacyID, NotificationMode 
        from OutboundCallData) AS outboundData
    PIVOT
    (
       count(NotificationMode )
       FOR NotificationMode IN ([sms], [email], [call], [p])
    ) AS P

Sql Server create table queries


By : Frederikke Jensen
Date : March 29 2020, 07:55 AM
I wish this help you
1.Why should the primary key (COLUMN1) specified as a "PRIMARY KEY CLUSTERED"?

Added some columns to a SQL Server 2008 R2 table but it does not show up when a web page queries the table


By : Ikuti112
Date : March 29 2020, 07:55 AM
will help you The only thing I can think of is that the column you are querying is not being rendered perhaps you aren't mentioning your column in the query.
Related Posts Related Posts :
  • How can i export the result set of an SQL Server stored procedure from one server into a table on another server?
  • Best practice when using calculations and joins
  • Converting Query into SQL Server syntax
  • Order by case with NULLs not first nor last
  • SQL Server stored procedure with while condition containing table variable
  • Conditionally insert a row if it does not exist already
  • Stored Procedure calling variables from table
  • Dynamic Statement to Get Top "n" for Each Individual Column from Ms SQL Table
  • Group by a COUNT subquery in SQL Server
  • Is it possible to add column name from the other's column value
  • Need help in writing oracle query from XML Column
  • SQL query to return startdate and enddate
  • sql with case in where clause
  • SQL get max value with date smaller date
  • PostgreSQL: CAST() as money: specify currency
  • BI Data modeling - Traditional vs new approaches
  • What is the difference between these 2 sql queries?
  • Beginner: LEFT JOIN not doing what it should?
  • Convert list into comma separate values inside multiline sql query for python
  • Postgres delete a row if the foreign key's column is set to false
  • SQL Server storing multiple values in a variable
  • Why does a pg query stop using an index after a while?
  • How to get last lines from table, but need all information of line?
  • Connecting to Teradata via SAS (SQL Explicit Passthrough), for data pull, is it recommended to use execute statement?
  • Joining multiple tables in single join transformation in Data Flow (Azure Data Factory v2)
  • SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
  • Count function rather than count(*)
  • Postgresql - pick up field from object array to text array
  • Get the last entered rows of each distinct value of a column
  • Concatenate multiple rows to form one single row in SQL Server?
  • Create a new table by grouping the data in a table
  • How to retrieve different data from same sql query while we execute query simultaneously?
  • Week Start from Monday for a given date
  • UBER CRM case study Customer retention metrics in SQL
  • How do I get the performed operation and affected table from a database trigger in oracle?
  • Logic OR on a Bytea using GROUPBY
  • Avoid division by zero: 1 / 0 error in WITH clause
  • How to create temporary tables in SQL SERVER?
  • What is the meaning behind this SQL Statement? ISNULL(Status,0) & 128 = 0?
  • How to match user defined data type in SQL
  • postgres - Can a window function be used to solve this query?
  • IS ISNULL() specific for integers?
  • Generating MIN, AVG, MAX columns from a column. [SQL] Hive
  • Querying last non-null values of time-series table in Postgres
  • Indexes In Postgres
  • Count most occurring word in row SQL Server
  • How to find difference in minute up to two decimal between two column in Oracle where both column datatype is date?
  • SQL to Split Rows into Multiple Column
  • Sql Query / Calculate rank for values based on date and id
  • ER/Relational Table to Database
  • problems with join
  • MONTH invalid identifier in Oracle
  • Select distinct value from a table
  • DB2 showing 0 instead of decimal place
  • Add a derived attribute to existing table in SQL Developer
  • Get results from one table based on "filter" table efficiently
  • How do I copy data from one Azure database table to a different Azure database table and also convert data types?
  • How to run all postgres sql scripts from same folder using shell script
  • 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
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk